PostGreSQL - Conexões idle

Uns dois meses atrás uma aplicação que tinha desenvolvido para um cliente usando JDBC e hospedada na Amazon, estava dando erro de conexão.

Pesquisando vi que existe o comando:

select * FROM pg_stat_activity

que informa os states das conexões.

E este para fechar as conexões (use com cuidado):

select pg_terminate_backend(pg_stat_activity.pid)

from pg_stat_activity

where pg_stat_activity.datname = 'database' AND pid <> pg_backend_pid();

Pesquisando ainda, encontrei vários links informando para usar o método close nos objetos: Connection, ResultSet e PreparedStatement. Revisei o código adicionando o método close sempre que necessário o que resolveu o meu problema.

Segue então aí a dica de sempre usar close quando estiver usando JDBC.

Nota: Prefira PreparedStatement em vez de Statement para evitar SQL Injection.

LIMIT do PostgreSQL != ROWNUM do Oracle

Numa aplicação de um cliente, Flex+Java+JDBC+PostgreSQL, estou ajudando a converter para usar Oracle.

Numa olhada rápida trocamos o comando LIMIT do PostgreSQL nas instruções sql para ROWNUM (no where) por acharmos serem equivalentes.

Na maioria das alterações funcionou corretamente pois tínhamos LIMIT 1, então trocando para WHERE ROWNUM = 1 se obteve o resultado esperado comparando o retorno da aplicação usando PostgreSQL e Oracle.

Mas em outros casos o resultado não foi o esperado, tipo LIMIT 10 trocando para ROWNUM = 10, retornava 1 registro ou nenhum enquanto que o PostgreSQL retornava até 10 registros.

Olhando a documentação dessas instruções se percebeu que não são equivalentes, o LIMIT "limita" a quantidade de registros retornados até o número informado, podendo retornar menos, enquanto que o ROWNUM é uma pseudo coluna que o Oracle "cria" para cada linha retornada, nesse caso ROWNUM = 10 irá retornar somente a linha 10 ou nenhuma caso o restante do WHERE retornasse menos de 10 linhas.

A solução foi fácil, ROWNUM <= 10. 

Veja também:

LIMIT and OFFSET

ROWNUM Pseudocolumn

Auditoria em PostgreSQL

Uns anos atrás na Sadig onde trabalhava precisei alterar o produto BI para fazer com que as chamadas a banco de dados que já "conversavam" com dbf, SQL Server e Oracle para também rodarem em PostgreSQL, gostei muito desse banco, e cada vez tenho aprendido mais sobre ele.

Agora num cliente, a Lexsis, precisamos implementar o recurso de auditoria no PostgreSQL. Se estivéssemos usando Hibernate poderíamos usar o Envers o qual usando a anotação @Audited na classe com a anotação @Entity cria uma nova tabela com atributos adicionais no banco para gravar as alterações na tabela da entity. 

Outra opção é ter uma única tabela e gravar o DTO serializado num campo dessa tabela, mas não seria fácil de identificar só utilizando a console do banco.

Usamos JDBC puro, então, uma opção que foi definida em reunião foi criar um schema para auditoria com uma cópia de cada tabela com campos adicionais específicos para auditoria da aplicação usando triggers. 

Bem, mas para cada tabela se precisa criar a trigger / function e um script para criar a tabela de auditoria, o que na primeira vez se torna trabalhoso e repetitivo pelo número de tabelas existentes em cada aplicação que irá usar a auditoria, porque a trigger é vinculada a uma única tabela e a uma function. Usar uma única function é uma ideia mas não cheguei a um código que contemplasse o meu cenário, mas não perdi muito tempo pois sempre achei interessante ser o mais independente do banco, caso seja necessário migrar para outro.

A ideia de ter uma tabela de espelho num schema separado é interessante por ser mais fácil a visualização, não necessitando de uma interface apropriada. Usando este formato e pensando em otimizar o processo me foquei em dois pontos: como otimizar a criação das tabelas espelhos e como não precisar das triggers.

Referente a criação das tabelas, dando uma olhada no create table do PostGreSQL vi que existe as cláusulas INHERITS e LIKE, o primeiro permite herdar a estrutura de uma ou mais tabelas (multi-herança), onde qualquer alteração nas estruturas das tabelas herdadas como alteração de um campo ou inclusão afeta as tabelas filhas o que resolve a situação de ter os mesmos campos de auditoria em todas as tabelas, enquanto que o LIKE permite pegar a estrutura de outra tabela, mas diferente do INHERITS, alterações na tabela de origem não se propaga para a tabela de destino. O LIKE não copia os valores default da tabela de origem (EXCLUDING DEFAULTS) só o faz com a adição de INCLUDING DEFAULTS.

Referente a segunda opção, para ficar independente de banco de dados utilizei introspection / reflection e anotações. Em cada DTO / entity marquei com uma anotação para identificar a tabela (equivalente o @Entity do Hibernate) do banco e outra anotação para identificar o campo id (primary key - PK) na tabela (equivalente o @Id do Hibernate). Com isso passo o DTO para uma classe com mais alguns parâmetros da aplicação que via introspection / reflection monta um insert...select para popular a tabela de auditoria, ficando assim independente de banco de dados e se for necessário migrar para outro banco só se altera uma classe. 

O único cuidado que se tem que ter com o insert...select é que a ordem dos campos da cláusula select tem que fechar com a ordem dos campos da tabela do insert, pois não vincula o campo / valor da tabela do select com o campo na tabela de destino, podendo dar erro de tipo inválido ou gravar um valor de um campo em outro por estarem na mesma ordem e terem o mesmo tipo de dados.

Com esta metodologia, usando introspection / reflection e anotações, consegui montar um início de um ORM, passando o DTO consigo fazer a exclusão, agora o próximo passo é fazer o insert, update passando o DTO e a listagem lendo os valores da tabela e populando os DTOs necessários.