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

Removendo registros duplicados

Olá, pessoal

Muitas vezes temos tabelas sem chaves primárias (nada recomendável) e com registros duplicados.
Uma pergunta que normalmente surge é: Como fazer para deletar estes registros? Uma solução possível é:

DELETE FROM tabela WHERE tabela.ctid NOT IN (SELECT max(tabela.ctid) FROM tabela GROUP BY tabela.codigo) RETURNING codigo;

Observação 1: O parâmetro RETURNING está disponível a partir da versão 8.2.

Observação 2: Uma boa prática é executar comandos do tipo UPDATE/DELETE dentro de um bloco de transação [1].

[1] http://www.postgresql.org/docs/8.3/interactive/mvcc-intro.html

Fiquem a vontade para comentários.

[]s

Visualizando meta-comandos do psql

Olá, pessoal

Quando por exemplo é realizado no psql uma instrução como:

\dt – mostrar as tabelas

\dn – mostrar os esquemas

Isso na realidade é um meta-comando. Por trás deste meta-comando existe uma consulta ao catalogo (pg_catalog) do PostgreSQL. Para visualizar qual consulta é realizada é necessário habilitar o parâmetro ECHO_HIDDEN.

Exemplo:

\set ECHO_HIDDEN ON

Desta forma todo o meta-comando executado apresentará no psql a consulta que é executada no catalogo do PostgreSQL.

Lembrando que essa alteração é valida somente enquanto a sessão estiver ativa. Para tornar padrão essa modificação é necessário coloca-la arquivo .psqlrc. Este arquivo está localizado no diretório home do usuário (/home/usuario/.psqlrc). Caso o arquivo não exista o mesmo pode ser criado.

Fiquem a vontade para comentários.

[]s

Listando tabelas e seus relacionamentos

Olá, pessoal

Segue abaixo uma forma de descobrir os relacionamentos existentes entre as tabelas de um banco de dados.

O resultado do sql abaixo é: esquema da tabela (PK), tabela (PK), esquema da tabela (FK) e tabela (FK).

PK – Primary Key – Chave primária.

FK – Foreign Key – Chave estrangeira.

SELECT sch1.nspname,
tab1.relname,
sch2.nspname,
tab2.relname
FROM pg_namespace sch1 JOIN pg_class tab1 ON sch1.oid=tab1.relnamespace
JOIN pg_constraint ON tab1.oid=pg_constraint.confrelid
JOIN pg_class tab2 ON pg_constraint.conrelid=tab2.oid
JOIN pg_namespace sch2 ON tab2.relnamespace=sch2.oid
ORDER BY sch1.nspname,
tab1.relname,
sch2.nspname,
tab2.relname;

Fiquem a vontade para comentários.

[]s

PostgreSQL – Desabilitando o autocommit

Olá, pessoal

Como todos nós sabemos o PostgreSQL é autocommit, isto é, ao executar qualquer comando SQL automaticamente este comando é efetivado no banco, porém é possível desabilitar este comportamento.

Para alterar o comportamento padrão faz-se: (Lembrando que esta alteração é realizada na seção e via ambiente psql).

\set AUTOCOMMIT OFF

Verificando a modificação:

\echo :AUTOCOMMIT

OFF

Com a modificação do comportamento padrão é necessário executar o comando COMMIT ou o comando END para que a operação SQL seja efetivada no banco. Caso venha a ocorrer algum erro, a seguinte mensagem é apresentada: current transaction is aborted, commands ignored until end of transaction block e a transação automaticamente é abortada.

Se for desejável que a modificação se torne permanente é necessário realizar esta alteração no arquivo .psqlrc. Este arquivo fica localizado no diretório do usuário (/home/usuario/.psqlrc).

Fiquem a vontade para comentários

[]s