Arquivos do Blog

PGCon 2009 – Inscrições abertas

Estão abertas as inscrições para o PGCon 2009.

Não perca tempo e faça já sua inscrição e aproveite o maior de todos os eventos sobre PostgreSQL. Clique aqui e confira:

Usando o DBLink para interligar dois bancos PostgreSQL

Pessoal,

Hoje vou apresentar como é possível trocar informações entre bancos de dados PostgreSQL. Apresentarei como isso é possível através do uso do DBLink.

O primeiro passo é instalar o DBLink no banco. Aqui vou considerar que a instalação do PostgreSQL foi realizada de forma compilada e para isso será necessário fazer uso dos arquivos da instalação. Dentro do diretório existe um diretório denominado contrib e dentro deste diretório existe um subdiretório chamado dblink. Para instalar o dblink é necessário realizar a compilação e posteriormente adiciona-lo ao banco desejado.

Para compilar é necessário executar o seguinte comando:

make

make install

Após a execução dos comandos acima será gerado um arquivo chamado dblink.sql. Este arquivo deve ser carregado (importado) no banco desejado. Para demonstrar o seu uso trabalharei com os bancos: banco01 e banco02.

O banco banco01 possui um tabela chamada tabela01 e o banco02 possui uma tabela chamada tabela02. Cada tabela possui um atributo código do tipo inteiro e ambas as tabelas contém 10 registros.

Carregando o arquivo dblink.sql no banco01.

psql banco01 -f dblink.sql

Com o dblink carregado no banco01, o próximo passo é realizar a conexão entre o banco01 e o banco02.

No exemplo, será considerado que estando conectado no banco01 será requisitada uma conexão com o banco02 para ai sim possibilitar a troca de informações entre os dois bancos de dados.

Então vamos a prática:

banco01=# SELECT dblink_connect(‘conexao’,’host=localhost port=9999 user=postgres dbname=banco02′);
dblink_connect
—————-
OK
(1 row)

Alguns parâmetros são informados. O primeiro é um nome para a conexão, e o restante parâmetros normais de uma conexão: hostname, porta, usuário, senha (opcional) e o nome do banco. Como para este exemplo a autenticação esta usando o método trust, o parâmetro password foi omitido.

Com a conexão OK agora é só realizar uma operação qualquer.

Por exemplo, um join entre a tabela01 que pertence ao banco01 e a tabela02 que pertence ao banco02.

banco01=# SELECT tab01.codigo,tab02.codigo FROM tabela01 tab01 INNER JOIN (SELECT * FROM dblink(‘conexao’,’SELECT codigo FROM tabela02′) AS resultado(codigo integer)) tab02 ON tab01.codigo=tab02.codigo;

Um outro exemplo pode ser feito com uma operação de escrita (INSERT, UPDATE OU DELETE).

A partir do banco01 fazendo uma chamada de inserção na tabela02 que está no banco02.

banco01=# SELECT dblink_exec(‘conexao’,’INSERT INTO tabela02 VALUES (generate_series(11,20))’);
dblink_exec
————-
INSERT 0 10
(1 row)

Conferindo:

banco01=# SELECT * FROM dblink(‘conexao’,’SELECT codigo FROM tabela02′) AS resultado(codigo integer);
codigo
——–
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)

Para operações de UPDATE e DELETE o procedimento transcorre da mesma maneira do que do comando INSERT.

Por fim, para encerrar a conexão é necessário executar a seguinte função:

banco01=# SELECT dblink_disconnect(‘conexao’);
dblink_disconnect
——————-
OK
(1 row)

Espero que a dica seja útil. Até uma próxima oportunidade 🙂

Abraços

PGCon 2009 – Grade oficial do evento

Maior evento sobre PostgreSQL da América Latina acontece em Campinas.

A Unicamp já se prepara para receber a “3ª Conferência Brasileira de PostgreSQL” ou simplesmente PGCon Brasil 2009.

Nos dias 23 e 24 de outubro, centenas de estudantes e profissionais de TI participarão do maior evento latino-americano sobre o mais poderoso sistema gerenciador de banco de dados de código livre do mundo, o PostgreSQL.

A programação completa da Conferência já foi confirmada. O evento contará com palestras, tutoriais e os já consagrados Hacker Talks e Lightning Talks. Estarão presentes desenvolvedores nacionais do PostgreSQL como Euler Taveira e Francisco Figueiredo Jr, internacionais como Bruce Momjian, Magnus Hagander além de profissionais reconhecidos no Brasil como Fernando Ike, Roberto Mello, Leandro Dutra entre outros.

Na programação, estarão temas como as últimas novidades da versão 8.4 do PostgreSQL, técnicas de monitoramento, segurança, ajustes de desempenho e muito mais.

Mais informações sobre o evento, podem ser obtidas no site oficial em: http://pgcon.postgresql.org.br/2009/index.php

Abraços

Descobrindo a definição de chave primária (primary key) e chave estrangeira (foreign key)

Olá, pessoal

Na última semana surgiu uma pergunta na lista pgbr-geral sobre como descobrir a definição das restrições (constraints) de chave primária (primary key) e chave estrangeira (foreign key). Então aproveito para escrever um pouquinho sobre o assunto.

Essa informação não é encontrada diretamente nas tabelas e visões do catálogo do PostgreSQL, mas sim através de uma função chamada pg_get_constraintdef(oid), onde o parâmetro oid é o oid da constraint e que pode ser encontrado na tabela pg_constraint. Por exemplo, existe uma visão chamada pg_indexes que mostra as definições dos índices, porém se pararmos para analisar esta consulta veremos que para apresentar a definição dos índices é utilizada uma função chamada pg_get_indexdef(oid) semelhante a função usada para extrair a definição das restrições (constraints).

Abaixo segue a consulta para identificar o nome da tabela e suas definições de chave primária (primary key) e chave estrangeira (foreing key). Para encontrar essa informação é necessário o uso das tabelas de sistema pg_class e pg_constraint. Coloquei também a tabela pg_namespace que é onde são armazenados os esquemas existentes do PostgreSQL.

SELECT pg_class.relname AS nome_da_tabela,

pg_get_constraintdef(pg_constraint.oid) AS definicao_da_restricao

FROM pg_namespace JOIN pg_class ON pg_namespace.oid=pg_class.relnamespace

JOIN pg_constraint ON pg_class.oid=pg_constraint.conrelid

WHERE pg_namespace.nspname=’public’

AND pg_class.relkind=’r’

ORDER BY pg_class.relname;

O resultado desta consulta apresenta o nome da tabela e a sua respectiva definição de chave primária (primary key) e chave estrangeira (foreign key). Caso a tabela não possua chave primária e/ou chave estrangeira a mesma não será retornada por esta consulta. Neste caso seria necessário substituir o INNER JOIN por um LEFT OUTER JOIN. A palavra OUTER no LEFT OUTER JOIN é opcional.

E a sua implementação ficaria assim:

SELECT pg_class.relname AS nome_da_tabela,

pg_get_constraintdef(pg_constraint.oid) AS definicao_da_restricao

FROM pg_namespace JOIN pg_class ON pg_namespace.oid=pg_class.relnamespace

LEFT OUTER JOIN pg_constraint ON pg_class.oid=pg_constraint.conrelid

WHERE pg_namespace.nspname=’public’

AND pg_class.relkind=’r’

ORDER BY pg_class.relname;

Na cláusula WHERE existem duas restrições: pg_namespace.nspname=’public’ restringe em qual esquema serão procurados os objetos, neste caso apenas no esquema public. Se for necessário procurar em mais de um esquema pode-se substituir a condição pg_namespace.nspname=’public’ por pg_namespace.nspname IN (‘public’, ‘outro_esquema’). A condição pg_class.relkind=’r’ restringe que só serão pesquisados os objetos do tipo tabela. Esta restrição é importante pois a tabela pg_class armazena outros objetos como: índices, seqüências, visões, tipos compostos e tabelas toast. Esta condição é necessária devido ao join com a tabela pg_namespace. Se essa junção for retirada não é necessária esta restrição.

Se for de interesse é possível mostrar o nome da restrição (constraint) através do atributo conname que está armazenado na tabela pg_constraint.

Era isso. Fiquem a vontade para comentários.

Abraços


Monitorando o tamanho do banco de dados

Olá, pessoal

Apresento aqui uma maneira de descobrir o tamanho de cada banco de dados de um servidor PostgreSQL. Para esse calcúlo são usadas as funções pg_database_size e pg_size_pretty.

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC,
pg_database.datname;

Fiquem a vontade para comentários.

[]s

Identificando índices duplicados

Olá, pessoal

O assunto de hoje é sobre a identificação de índices duplicados. Um problema que tenho visto em alguns clientes que tenho trabalhado é a quantidade de índices duplicados que existem nas tabelas, então posto aqui uma maneira de indentificar estes índices. Junto a isso apresento uma maneira interessante do uso da claúsula ORDER BY.

SELECT pg_stat_user_indexes.schemaname AS nome_do_esquema,
pg_stat_user_indexes.relname AS nome_da_tabela,
pg_attribute.attname AS nome_do_atributo,
pg_stat_user_indexes.indexrelname AS nome_do_indice,
CASE pg_index.indisprimary
WHEN ‘t’ THEN ‘Sim’
ELSE ‘Nao’
END AS indice_na_chave_primaria
FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid)
JOIN
(SELECT pg_index.indrelid,
pg_index.indkey,
count(*)
FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid)
GROUP BY pg_index.indrelid,
pg_index.indkey
HAVING count(*)>1) ind_dup ON pg_index.indrelid=ind_dup.indrelid
AND pg_index.indkey=ind_dup.indkey
JOIN pg_attribute ON pg_attribute.attrelid=ind_dup.indrelid
AND pg_attribute.attnum=SOME(pg_index.indkey)
ORDER BY pg_stat_user_indexes.schemaname,
pg_stat_user_indexes.relname,
pg_index.indisprimary=’t’ DESC;

O SQL acima apresenta como resultado o nome do esquema que a tabela pertence, o nome da tabela, o atributo indexado, o nome do índice e se o índice está associado a uma chave primária.

Uma questão interessante que acabei descobrindo é poder fazer uma comparação na claúsula ORDER BY. No exemplo acima a ordenação é pelo nome do esquema, nome da tabela e pelo atributo pg_index.indisprimary=’t’ que ordena primeiramente pelo índice que está associado a uma chave primária.

Eu sabia que era possível usar operadores na claúsula ORDER BY mas imaginava que só era possível usar a seguinte estrutura:

ORDER BY atributo USING <; para ordenação crescente e  ORDER BY atributo USING >; para ordenação decrescente.

Era isso. Fiquem a vontade para comentários.

[]s

Comentários em objetos

Olá, pessoal

No PostgreSQL temos um recurso muito bacana e pelo que tenho visto muito pouco utilizado que é o comentário em objetos. A idéia é bem simples. É ser um comentário (descrição) do objeto. Um comentário pode ser adicionado em colunas de tabelas, tabelas, seqüências, esquemas, tablespaces, rules, funções, gatilhos (triggers) dentro outros.

Por exemplo: Supondo a seguinte estrutura de tabela:

postgres=# \d funcionarios
Table “public.funcionarios”
Column       |          Type          | Modifiers
——————–+————————+———–
codigo_funcionario | integer                | not null
nome               | character varying(50)  | not null
salario            | numeric(10,2)          | not null
auxiliar1          | character varying(100) |
Indexes:
“funcionarios_pkey” PRIMARY KEY, btree (codigo_funcionario)

Os campos codigo_funcionario, nome e salário são de fácil interpretação e o campo auxiliar1 o que ele representa na tabela? Ele pode ser por exemplo, um campo para informações adicionais do funcionário ou derrepente armazenar uma outra informação. Se por exemplo, o campo auxiliar1 tivesse um comentário seria muita mais rápido e fácil descobrir qual o objetivo deste campo e que informações ele armazena.

Na teoria é bonito mas como funciona na prática o comentário sobre objetos e qual comando eu devo utilizar para adicionar um comentário? E depois, como eu visualizo o comentário?

Para adicionar um comentário sobre um objeto faz-se uso do comando COMMENT ON.

Sintaxe completa:

COMMENT ON
{
TABLE object_name |
COLUMN table_name.column_name |
AGGREGATE agg_name (agg_type [, …] ) |
CAST (sourcetype AS targettype) |
CONSTRAINT constraint_name ON table_name |
CONVERSION object_name |
DATABASE object_name |
DOMAIN object_name |
FUNCTION func_name ( [ [ argmode ] [ argname ] argtype [, …] ] ) |
INDEX object_name |
LARGE OBJECT large_object_oid |
OPERATOR op (leftoperand_type, rightoperand_type) |
OPERATOR CLASS object_name USING index_method |
OPERATOR FAMILY object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
ROLE object_name |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
TABLESPACE object_name |
TEXT SEARCH CONFIGURATION object_name |
TEXT SEARCH DICTIONARY object_name |
TEXT SEARCH PARSER object_name |
TEXT SEARCH TEMPLATE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
} IS ‘text’

Exemplo: Adicionando um comentário a tabela de funcionários.

postgres=# COMMENT ON TABLE funcionarios IS ‘Tabela de Funcionários’;
COMMENT

Pronto. Adicionamos um comentário a tabela de funcionários.

Para verificar o comentário sobre uma tabela utiliza-se o meta-comando \dd seguido do nome da tabela.

postgres=# \dd funcionarios
Object descriptions
Schema |     Name     | Object |      Description
——–+————–+——–+————————
public | funcionarios | table  | Tabela de Funcionários
(1 row)

Pronto. Ai está a tabela e o seu respectivo comentário.

Exemplo: Adicionando comentário nas colunas da tabela.

postgres=# COMMENT ON COLUMN funcionarios.codigo_funcionario IS ‘Código do funcionário.’;
COMMENT
postgres=# COMMENT ON COLUMN funcionarios.nome IS ‘Nome do funcionário.’;
COMMENT
postgres=# COMMENT ON COLUMN funcionarios.salario IS ‘Salário do funcionário.’;
COMMENT
postgres=# COMMENT ON COLUMN funcionarios.auxiliar1 IS ‘Informações adicionais do funcionário.’; COMMENT

Feito. Os comentários sobre as colunas da tabela estão prontos. Para visualizar faz-se uso do meta-comando \d+ e o nome da tabela.

postgres=# \d+ funcionarios
Table “public.funcionarios”
Column       |          Type          | Modifiers |              Description
——————–+————————+———–+—————————————-
codigo_funcionario | integer                | not null  | Código do funcionário.
nome               | character varying(50)  | not null  | Nome do funcionário.
salario            | numeric(10,2)          | not null  | Salário do funcionário.
auxiliar1          | character varying(100) |           | Informações adicionais do funcionário.
Indexes:
“funcionarios_pkey” PRIMARY KEY, btree (codigo_funcionario)
Has OIDs: no

Rápido e fácil e tem-se um comentário sobre as colunas da tabela o que facilita a interpretação do que estas colunas estão representando.

Fiquem a vontade para comentários.

[]s

Removendo roles no PostgreSQL

Olá, pessoal

Vou falar um pouco sobre a remoção de roles no PostgreSQL. Para remover um role conteiner ou login usamos o comando DROP ROLE.

Sintaxe completa: DROP ROLE [ IF EXISTS ] name [, …].

Observação: A opção IF EXISTS está disponível a partir da versão 8.2 e é opcional.

Quando queremos remover uma role ou uma lista de roles podemos fazer:

DROP ROLE IF EXISTS role1;

ou

DROP ROLE role1;

DROP ROLE IF EXISTS role1,role2,role3;

ou

DROP ROLE role1,role2,role3;

O IF EXISTS verifica se a role existe. Se sim, remove a role. Caso contrário apresenta uma mensagem informando que a role não existe. Sem essa verificação seria apresentada uma mensagem de erro.

Vamos a prática:

foo=# DROP ROLE jotacomm;
DROP ROLE

Porém, em alguns casos pode ser gerado o seguinte erro:

DROP ROLE foo;

ERROR:  role “foo” cannot be dropped because some objects depend on it
DETAIL:  owner of table tabela2
owner of table tabela1

O erro apresentado acima indica que a role não pode ser excluída pois existem objetos que ela é dona (OWNER).

E como proceder nesta situação? Uma solução seria atribuir a propriedade do objeto para outra role. Por exemplo:

ALTER TABLE tabela1 OWNER TO bar;

ALTER TABLE tabela2 OWNER TO bar;

Depois da alteração é possível remover a role. No entanto, essa solução não é a mais adequada pelo fato de que se a role possuir vários objetos como: tabelas, seqüências, funções, esquemas dentre outros objetos este processo deve ser realizado para todos estes objetos.

Então podemos utilizar os comandos DROP OWNED ou REASSIGN OWNED. O DROP OWNED remove todos os objetos de propriedade de uma ou mais roles enquanto que o comando REASSIGN OWNED transfere a propriedade dos objetos de uma ou mais roles para uma outra role.

Os comandos DROP OWNED e REASSIGN OWNED estão disponíveis a partir da versão 8.2.

Sintaxe completa:

DROP OWNED BY name [, …] [ CASCADE | RESTRICT ]

REASSIGN OWNED BY old_role [, …] TO new_role

Dessa forma o processo fica mais fácil e rápido. Tá, mas como funciona isso na prática?

Exemplo do uso do DROP OWNED.

foo=# DROP OWNED BY foo;
DROP OWNED

Como a execução do comando DROP OWNED todos os objetos da role foo foram deletados.

Então agora eu posso usar o DROP ROLE para remover a role.

foo=# DROP ROLE foo;
DROP ROLE

Exemplo do uso do REASSIGN OWNED.

foo=# REASSIGN OWNED BY bar TO postgres;
REASSIGN OWNED

Como a execução do comando REASSIGN OWNED todos os objetos de propriedade da role bar foram transferidos para a role postgres.

Então agora eu posso usar o DROP ROLE para remover a role.

foo=# DROP ROLE bar;
DROP ROLE

Fiquem a vontade para comentários.

[]s

Removendo arquivos com exceção do mais recente

Olá, pessoal

Quando trabalhamos com os arquivos de log do PostgreSQL, por exemplo, dependendo de como está a configuração (arquivo postgresql.conf) pode ser que sejam gerados vários arquivos de log. Se temos um hd não tão grande isso pode ser um problema.

Descrevo aqui uma forma de remover os arquivos mais antigos e deixar apenas o último arquivo pois o PostgreSQL está escrevendo nele. Uma boa prática poderia ser antes de remover estes arquivos gerar uma cópia em um outro computador ou a gravação dos mesmos em um cd ou dvd.

O comando abaixo faz isso: Apaga os arquivos deixando apenas o arquivo mais recente.

ls -t | grep -v `ls -t | head -n 1` | xargs rm -v

Um exemplo na prática:

O diretório de logs possui os seguintes arquivos:

postgresql-2008-10-24_231829.log

postgresql-2008-10-25_151120.log

postgresql-2008-10-25_151359.log

Executando:

ls -t | grep -v `ls -t | head -n 1` | xargs rm -v

removido `postgresql-2008-10-25_151120.log’
removido `postgresql-2008-10-24_231829.log’

Restou apenas o arquivo postgresql-2008-10-25_151359.log que é o arquivo mais atual.

Fiquem a vontade para comentários.

[]s

Instalando o PostgreSQL no Windows Vista

Olá, pessoal

Para instalar o PostgreSQL no Windows Vista é necessário desabilitar UAC (User Account Control – Controle de Contas de Usuário (Painel de Controle – Contas de Usuário – Ativar/Desativar controle de contas de usuário)).

Depois de realizada a alteração você precisa reiniciar o Windows.

Pronto. Agora você já pode instalar o seu PostgreSQL. Depois de instalado você pode ativar novamente o UAC.

Fiquem a vontade para comentários.

[]s