Meu Blog de PostgreSQL!!!

Criação de Crosstabs no PostgreSQL

2011-11-25T13:56:00.002-03:00

Você já criou uma tabela cruzada, ou crosstab, utilizando SQL? Você sabia que era possível? Possivelmente você nunca precisou, mas criar crosstabs é um recurso à disposição que pode ser bastante útil!

Tabelas cruzadas apresentam mais de uma dimensão de forma integrada aos usuários, sendo uma forma importante para a melhor visualização de dados consolidados. Geralmente o cruzamento de dados neste tipo de tabela é feito na camada de apresentação das aplicações ou em componentes geradores de relatórios, mas isso não impede que você também possa implementar crosstabs via banco de dados.

Vamos exemplificar a criação de crosstabs no postgresql utilizando as tabelas abaixo:

* Tabela Loja - Basicamente apresenta a descrição da loja

-- Tabela Loja
CREATE TABLE loja (codigo integer, nomeloja varchar(20), observacao varchar(250));
INSERT INTO loja VALUES (1, 'Loja 1', 'Matriz');
INSERT INTO loja VALUES (2, 'Loja 2', 'Filial 1');
INSERT INTO loja VALUES (3, 'Loja 3', 'Filial 2');
INSERT INTO loja VALUES (4, 'Loja 4', 'Filial 3');

* Tabela Vendas - Apresenta as vendas realizadas para cada mês em cada loja.

-- Tabela Vendas

CREATE TABLE vendas (seqvenda integer, codloja integer, mesvenda integer, valor integer);
INSERT INTO vendas VALUES (1, 1, 1, 100);
INSERT INTO vendas VALUES (2, 1, 2, 100);
INSERT INTO vendas VALUES (3, 1, 3, 100);
INSERT INTO vendas VALUES (4, 1, 1, 100);
INSERT INTO vendas VALUES (5, 2, 2, 100);
INSERT INTO vendas VALUES (6, 2, 3, 100);
INSERT INTO vendas VALUES (7, 2, 1, 100);
INSERT INTO vendas VALUES (8, 2, 2, 100);
INSERT INTO vendas VALUES (9, 4, 3, 100);
INSERT INTO vendas VALUES (10, 4, 1, 100);
INSERT INTO vendas VALUES (11, 4, 2, 100);
INSERT INTO vendas VALUES (12, 4, 3, 100);

Vamos fazer consultas para mostrar as vendas com base em duas dimensões: a loja que fez a vendas e o período (mês) das vendas.

Em primeiro lugar, vamos colocar como colunas as lojas da tabela pai (loja), e como linhas as vendas da tabela filha, agrupando os dados por mês. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for da loja a ser apresentada na coluna.

-- CROSSTAB
-- COLUNAS - LOJAS
-- LINHAS - Vendas em cada Mês
SELECT
(CASE vendas.mesvenda WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEV' WHEN 3 THEN 'MAR' ELSE 'ERRO' END) AS MES,
SUM (CASE vendas.codloja WHEN 1 THEN vendas.valor ELSE 0 END) AS MATRIZ,
SUM (CASE vendas.codloja WHEN 2 THEN vendas.valor ELSE 0 END) AS FILIAL_1,
SUM (CASE vendas.codloja WHEN 3 THEN vendas.valor ELSE 0 END) AS FILIAL_2,
SUM (CASE vendas.codloja WHEN 4 THEN vendas.valor ELSE 0 END) AS FILIAL_3
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY vendas.mesvenda
ORDER BY vendas.mesvenda;

O resultado da consulta mostra inclusive a filial 2, que esteve fechada durante o período:

mes | matriz | filial_1 | filial_2 | filial_3
-----+--------+----------+----------+----------
JAN |    200 |      100 |        0 |      100
FEV |    100 |      200 |        0 |      100
MAR |    100 |      100 |        0 |      200
(3 registros)

Agora vamos fazer uma inversão: vamos colocar como colunas as lojas da tabela filha, vendas, e como linhas as informações da tabela pai, loja, com os dados agrupados por loja. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for do mês a ser apresentado na coluna.

-- CROSSTAB 2
-- COLUNAS - Vendas em cada Mês
-- LINHAS - LOJAS
SELECT
(CASE loja.codigo WHEN 1 THEN 'MATRIZ' WHEN 2 THEN 'FILIAL 1' WHEN 3 THEN 'FILIAL 2' WHEN 4 THEN 'FILIAL 3' ELSE 'ERRO' END) AS LOJA,
SUM (CASE vendas.mesvenda WHEN 1 THEN vendas.valor ELSE 0 END) AS JAN,
SUM (CASE vendas.mesvenda WHEN 2 THEN vendas.valor ELSE 0 END) AS FEV,
SUM (CASE vendas.mesvenda WHEN 3 THEN vendas.valor ELSE 0 END) AS MAR
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY loja.codigo
ORDER BY LOJA;

Resultado da consulta:

   loja   | jan | fev | mar
----------+-----+-----+-----
FILIAL 1 | 100 | 200 | 100
FILIAL 3 | 100 | 100 | 200
MATRIZ   | 200 | 100 | 100
(3 registros)

O exemplo acima pode ser incrementado e melhorado de várias formas (por exemplo, as tabelas não têm índices). Se você tem alguma sugestão ou forma alternativa de fazer crosstabs ou de melhorar os exemplos abaixo, não deixe de postar um comentário neste post!

Obs.: Existe um módulo do postgresql já bastante estável, chamado tablefunc que apresenta funções para a criação de tabelas cruzadas. É uma forma de criar crosstabs com menos trabalho, mas também com menos diversão e portabilidade! Mais informações sobre o tablefunc aqui.

A Função initcap()

2011-11-25T09:22:00.000-03:00

A função initcap() é ao mesmo tempo simples e útil, e muitos desenvolvedores não a conhecem. Basicamente, a initcap() recebe como parâmetro uma string e a retorna de volta, colocando a primeira letra de cada palavra maiúscula, e as demais em letras minúsculas.

É bem prático para formatar strings de nomes de pessoas e lugares utilizando o próprio banco de dados.

Exemplo 1:

teste=# SELECT initcap('ALFA BETA GAMA');
    initcap
----------------
Alfa Beta Gama
(1 registro)

Exemplo 2:

teste=# SELECT initcap('alfa beta gama');
    initcap
----------------
Alfa Beta Gama
(1 registro)

A função initcap não altera caracteres numéricos, mas atua em qualquer "palavra" começada por caracteres, ainda que contenha números.

Exemplo 3:

teste=# SELECT initcap('123 123');
initcap
---------
123 123
(1 registro)

teste=# SELECT initcap('a123a A123A');
   initcap
-------------
A123a A123a
(1 registro)

Esta função pode ser empregada na criação de índices(!), embora eu não veja nisso qualquer utilidade para a maioria das aplicações de banco de dados. Bom, se você conhece algum uso criativo desta função, não deixe de registrar nos comentários!

Exemplo 4:

teste=# CREATE TABLE simples(codi integer, nome varchar(50));
CREATE TABLE
teste=# CREATE UNIQUE INDEX indexnome ON simples((initcap(nome)));
CREATE INDEX

Outras funções similares mas mais conhecidas, são UPPER() e LOWER(), que passam a string passada como parâmetro para maiúsculas e minúsculas, respectivamente.

201 Posts! Obrigado!

2011-10-17T10:08:00.000-03:00

Em 2007 comecei a estudar mais sobre o Postgres, e criei este blog.

Estamos em 2011 e este é o post número 201.

Ainda há muito a estudar, muito mesmo!

Ainda há muito para postar, muitas ideias.

O tempo diminuiu. Bastante.

E nem ensino mais banco de dados.

Mas cada acesso, cada comentário feito, atestam que valeu a pena.

E continua valendo.

Agradeço a Deus!

Obrigado aos amigos da comunidade, muitos dos quais admiro sem conhecer pessoalmente.

Obrigado a quem acessa este site.

Obrigado aos brasileiros.

Obrigado aos paulistas, aos paranaenses e ao povo do Rio Grande do Sul pelos acessos! A todos os estados e ao meu Ceará!

E também aos americanos, portugueses e holandeses (!) que acessam.

E a todos os demais!

Obrigado por cada comentário, ainda que anônimo!

Este site é sobre uma ferramenta técnica.

Nunca vai chegar a um milhão de acessos.

Mas esse não era o objetivo.

Tampouco vender consultorias.

Espero ter tempo para continuar estudando e compartilhando!

Obrigado!

Unlogged Tables: Funcionalidade para Aumento de Desempenho!

2011-10-11T16:46:00.000-03:00

Todos sempre buscamos melhorar o desempenho das operações de banco de dados. E um dos recursos de performance ainda pouco utilizados da versão 9.1 do postgres são as chamadas unlogged tables.

O que são Unlogged Tables?

Unlogged Tables são tabelas que não apresentam suporte a recuperação pós-falha. Não apresentam portanto log de transações (write-ahead-log - WAL). Essa característica possibilita um grande ganho de desempenho em todas as operações realizadas. O ganho de desempenho obtido se deve ao sacrifício da possibilidade de recuperar os dados em caso de falha de sistema.

Uma unlogged table tem seus dados automaticamente perdidos em caso de falha, pois é truncada automaticamente, o que gera um ganho no tempo de recuperação do banco de dados.

Os dados de uma unlogged table não sofrem replicação dentro do postgresql.

Em unlogged tables não há necessidade de se manter o log e sincronizá-lo com o banco de dados, fator importante para o de ganho de desempenho.

Em que situações é recomendado utilizar este tipo de tabela?

Em situações em que a durabilidade dos dados não seja realmente importante:
- Para parâmetros de aplicações web;
- Cache de dados em geral;
- Tabelas de status de aplicações, entre outras possibilidades.

Acredito que apenas uma pequena parte de sistemas de bancos de dados possa ser armazenada em tabelas unlogged.

As operações de inserção, alteração, alteração e consulta a dados de uma "tabela sem log" são diferentes de uma tabela "normal"?
A forma de fazer e os comandos utilizados permanecem os mesmos. No entanto, internamente, não há write-ahead-log (WAL), o que faz com que os dados da tabela seja perdidos em caso de quedas de sistema. A velocidade das operações tende a ser bem maior.

Como criar Unlogged Tables?

A criação de tabelas sem log é bastante simples. Basta colocar a cláusula "UNLOGGED" no comando de criação da tabela.

teste=> CREATE TABLE LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=> CREATE UNLOGGED TABLE NAO_LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=>
É permitido indexar este tipo de tabela?

Não existem restrições à indexação, exceto para índices GIST em que este recurso não está implementado. É possível inclusive reindexar, se for o caso! Os índices de uma unlogged table também são "unlogged", isto é, são truncados em caso de falha do sistema.

teste=> CREATE INDEX UNLOGT ON NAO_LOGADA(cod);
CREATE INDEX
teste=>
teste=> insert into NAO_LOGADA values (1, 'Teste 1');
INSERT 0 1
teste=> insert into NAO_LOGADA values (2, 'Teste 2');
INSERT 0 1
teste=> insert into NAO_LOGADA values (3, 'Teste 3');
INSERT 0 1
teste=> REINDEX TABLE NAO_LOGADA;
REINDEX
De quanto é o ganho esperado em desempenho?

DEPENDE da operações realizada. Veja o link abaixo e depois faça seus próprios testes:
http://pgsnaga.blogspot.com/2011/10/pgbench-on-unlogged-tables.html

Considerações Finais

Unlogged Tables são um recurso válido para ganho de performance em certos casos específicos. No entanto, a definição de que tabelas devem ser unlogged pode gerar erros graves e impossibilitar a recuperação de dados relevantes. Esta decisão deve ser sempre bastante embasada e levar em conta as necessidades de todos os usuários do banco.

PostgreSQL 9.1 Lançado Oficialmente!

2011-09-12T10:58:00.000-03:00

A muito aguardada versão 9.1 do PostgreSQL está disponível para download. O destaque são as mudanças relacionadas a performance e replicação, mas existem melhorias em praticamente todos os campos. É baixar, instalar e usar!

Abaixo o texto do anúncio oficial:

------------------------------------------------------------------------------------

The PostgreSQL Global Development Group announces the release of
PostgreSQL 9.1. This latest version of the leading open source database
offers innovative technology, unmatched extensibility, and new features
such as synchronous replication, K-Nearest Neighbor indexing, and
foreign data wrappers.

"PostgreSQL 9.1 provides some of the most advanced enterprise
capabilities of any open source database, and is backed by a vibrant and
innovative community with proven customer success. PostgreSQL is well
positioned for building and running applications in the cloud," said
Charles Fan, Sr. VP R&D, VMware.

Responding to Users

Version 9.1 delivers several features which users have been requesting
for years, removing roadblocks to deploying new or ported applications
on PostgreSQL. These include:

* Synchronous Replication: enable high-availability
with consistency across multiple servers
* Per-Column Collations: support linguistically-correct
sorting per database, table or column.
* Unlogged Tables: greatly improves performance for ephemeral data

"Heroku runs the largest PostgreSQL database-as-a-service in the world,"
said James Lindenbaum, Heroku co-founder. "The release of synchronous
data replication with 9.1 provides our customers with innovative new
ways of protecting mission-critical data, and validates PostgreSQL as
one of the fastest-moving datastores available."

Advancing the State of the Art

Our community of contributors innovates with cutting-edge features.
Version 9.1 includes several which are new to the database industry,
such as:

* K-Nearest-Neighbor Indexing: index on "distance" for
faster location and text-search queries
* Serializable Snapshot Isolation: keeps concurrent transactions
consistent without blocking, using "true serializability"
* Writeable Common Table Expressions: execute complex multi-stage
data updates in a single query
* Security-Enhanced Postgres: deploy military-grade security
and Mandatory Access Control

"OpenERP has always relied on the enterprise-class features of
PostgreSQL to provide a fast, reliable and scalable foundation for the
Business Applications supporting our customers' operations every day.
Data integrity in highly concurrent and transactional contexts is a
critical topic for us, and we're very enthusiastic about the new
Serializable Snapshot Isolation of PostgreSQL 9.1!" said Olivier Dony,
OpenERP Community Manager.

Extending the Database Engine

PostgreSQL's extensibility enables users to add new functionality to a
running production database, and use them for tasks no other database
system can perform. Version 9.1 adds new extensibility tools, including:

* Foreign Data Wrappers: attach and query other databases
from PostgreSQL
* Extensions: easily create, load, and manage new database features

In PostgreSQL's 25th year of database development, our community
continues to advance database technology with every annual release.
Download version 9.1 now and experience the most advanced open source
database system in the world.

More information on PostgreSQL 9.1:
* Release notes
http://www.postgresql.org/docs/9.1/static/release-9-1
* Presskit
http://www.postgresql.org/about/press/presskit91
* Guide to 9.0:
http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1

Download 9.1 now:
* Main download page:
http://www.postgresql.org/download
* Source code:
http://www.postgresql.org/ftp/source/v9.1.0
* One-click installer, including Windows installer:
http://www.enterprisedb.com/products/pgdownload.do

PgBr 2011 - Inscrições Abertas! Último dia para Submissão de Trabalhos!

2011-09-09T15:16:00.000-03:00

A PgBr 2011, o maior evento de Postgres do país, está com inscrições abertas. O valor da inscrição aumenta à medida em que se aproxima a data do evento: 3 e 4 de novembro, em São Paulo. A inscrição vip dá direito a almoço com os palestrantes. Se inscreva agora!

Hoje, 09 de setembro, é o último dia para Submissão de Trabalhos!

Serpro Ultrapassa os 200 Bancos de Dados PostgreSQL!

2011-09-09T14:37:00.001-03:00

Ao atingir o volume de 220 bancos de dados, o PostgreSQL passa a ocupar a
posição de destaque como tecnologia livre de bancos de dados no Serpro - Serviço Federal de Processamento de Dados. O MySQl apresenta 200 bancos de dados implementados.

Confira esta informação na página 19 da edição 207 da versão online de revista TEMA. Outro artigo com destaque para o Postgres é o do encarte TEMATEC "Expresso em Nuvem", que mostra uma implementação de grande porte com Postgres e PgPool.

A Função clock_timestamp()

2011-07-21T10:24:00.000-03:00

Você já ouviu da função clock_timestamp? Ela retorna a data e hora com bastante precisão junto com a time zone do servidor, da mesma forma que as funções now() e current_timestamp. Então para quê implementar esta nova função? O interessante da clock_timestamp é que a mesma retorna o timestamp do término da transação, enquanto now e current timestamp retornam a data/hora do início da transação corrente.

É uma função que pode ser importante para aqueles que necessitam de alta precisão ao lidar com variáveis temporais.

Exemplos:

1 - Sintaxe básica

teste=# SELECT clock_timestamp();
        clock_timestamp
-------------------------------
2011-07-21 09:34:12.645251-03
(1 registro)

2 - Extração de parte do valor retornado.

teste=# SELECT SUBSTRING(CAST(now() AS VARCHAR) FROM 1 FOR 10);
substring
------------
2011-07-21
(1 registro)

3 - Lado a lado o resultado de clock_timestamp() e now() na mesma transação. Observe que os valores são diferentes e indicam o timestamp de início e de término da transação.

teste=# SELECT SUBSTRING(CAST(now() AS VARCHAR) FROM 1 FOR 10);
substring
------------
2011-07-21
(1 registro)

teste=# SELECT now() || '   ' || clock_timestamp();
                           ?column?
---------------------------------------------------------------
2011-07-21 10:21:46.592655-03   2011-07-21 10:21:46.592828-03
(1 registro)

4 - Diferença entre clock_timestamp() e now(), mostrando o tempo decorrido entre o início e o término da transação.

teste=# SELECT clock_timestamp() - now();
    ?column?
-----------------
00:00:00.000099
(1 registro)

Link: tudo sobre data e hora no PostgreSQL!

2011-07-15T17:41:00.001-03:00

O link abaixo explica bem como lidar com data, horas e timestamps com o PostgreSQL:

http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Internas/Data_e_Hora

Já neste outro link, temos a documentação de funções e operadores para lidar com datas e horas:

http://www.postgresql.org/docs/8.2/static/functions-datetime.html

Baixe os RPMs de Instalação da versão 9.1 Beta2!

2011-07-01T07:48:00.000-03:00

Os instaladores para linux da versão 9.1 Beta 2 estão disponíveis para quem quiser testar. Esse é mais um indício de que a versão 9.1 logo será oficialmente lançada.

Baixe os RPMs aqui e teste à vontade!

JMeter: Brincando de Testar seu Servidor PostgreSQL!

2011-06-28T11:42:00.001-03:00

Testes de performance de servidores de bancos de dados são importantes para se avaliar ambientes de produção antes da implantação de projetos que envolvam muitos usuários e grande tráfego de informações. Uma ferramenta boa e estável para este tipo de teste é o Jakarta Jmeter.

Através do JMeter é possível configurar conexões ao servidor, criar requisições JDBC (comandos, chamadas de funções, etc.), submetê-las ao servidor e analisar o resultado da execução. A ferramenta permite ainda criar múltiplas threads simulando um grande número de usuários simultâneos, e executar para cada thread as requisições mais de uma vez, gerando uma grande carga de acessos que testa os limites de carga aceitos pelos bancos de dados.

Adicionalmente, é possível utilizar ouvintes (listeners) para apresentar os resultados da execução dos comandos na forma de tabelas, árvores e gráficos diversos.

O JMeter é compatível com qualquer banco que aceite conexão JDBC, incluindo o postgres e pode testar ainda outros tipos de requisição como ftp e http. Baixe o jmeter agora!

Vamos mostrar as principais etapas de utilização da ferramenta utilizando um teste feito no postgresql.

1. Instalação

Baixe a ferramenta, descompacte os arquivos em uma pasta. Observe que existem vários subdiretórios. As pastas mais relevantes são a LIB e a BIN.

Copie o arquivo do driver jdbc do postgresql (e o dos demais bancos com os quais trabalhar) para a pasta LIB do JMeter.

2. Execução

No windows pode ser utilizado o arquivo jmeter.bat, na pasta BIN da ferramenta (jakarta-jmeter-2.4/bin no meu caso). No Linux existe o arquivo jmeter.sh.

Existe a opção de rodar diretamente do arquivo .JAR, digitando:

java -jar ApacheJMeter.jar

A tela inicial apresentada é bastante simples, com uma barra de menu e uma árvore onde são apresentados hierarquicamente os comandos dos planos de testes.

A árvore apresenta duas grandes divisões: plano de testes e área de trabalho. A área de trabalho pode ser utilizada para colocar comandos que não serão executados, enquanto que o plano de testes é a parte da treeview que apresenta os comandos do plano que serão executados, sendo que comandos de teste podem ser arrastados livremente entre área de trabalho e plano de testes.

3. Número de Usuários Simulados no Teste

O primeiro passo é definir o número de usuários que seu teste deseja simular. Se o seu teste tiver muitos usuários, pode ser caracterizado como teste de carga. Caso tenha apenas um, executando uma vez cada comando, pode ser entendido como um teste funcional. Cada plano de testes pode agregar dezenas de requisições (testes) de banco de dados (JDBC), FTP, HTTP, entre outras possibilidades.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Threads (Users)\ Grupos de Usuários". A tela mostrada permite que se defina quantos usuários virtuais serão simulados, o tempo de inicialização de cada usuário e o número de vezes que cada usuário simulado executará os próximos comandos do plano de testes. Caso se deseje 100 usuários, executando 10 vezes cada teste, uma execução do plano de testes testará 10000 execuções do plano.

4. Configurando o JDBC

Para fazer testes de banco de dados, devemos configurar a conexão JDBC.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Elemento de Configuração\ Configuração da Conexão JDBC".

A tela mostrada permite que se defina os parâmetros de conexão com o servidor, tais como limite de conexões, tempo máximo de conexão, intervalo para timeout de conexão, entre outros. A figura abaixo mostra os principais parâmetros utilizados.

5. Criação do Teste de Banco

Para criar os testes de banco de dados em si, basta se definir o SQL a ser submetido. O comando será executando tantas vezes forem definidas na seção "Grupo de Usuários" do plano de testes.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Testador\ Requisição JDBC".

A tela mostrada permite que se forneça o comando SQL a ser testado. Não utilize ponto e vírgula ";", pois pode gerar erro de execução.

6. Adicionando Ouvintes (Listeners)

Antes de executar os testes, deve ser definido de que forma o resultado da execução será apresentado. Os ouvintes monitoram os testes e apresentam o resumo das execuções de várias formas.

Um mesmo teste pode ser visualizado de mais de uma forma, o que facilita o entendimento, seja como árvore, tabela, gráfico ou geração de arquivo.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Ouvinte\ Árvore de Resultados". Acrescente outros ouvintes como "Relatório Agregado" e "Ver Resultados em Tabela". As figuras abaixo mostra o resultado de um teste visto por mais de um ouvinte.

7. Executando Testes

Utilize a barra de menu para executar os testes:
- "Executar\ Iniciar" - Executa os testes do plano de trabalho atual
- "Executar\ Limpar Tudo" - Limpa os resultados de testes anteriores para nova rodada de testes
- "Arquivo\ Salvar" - Salva o Plano de Testes

Independentemente de termos boas ferramentas como o JMeter, temos sempre de testar as nossas aplicações, e não podemos descuidar dos bancos de dados. O JMeter tem potencial para automatizar boa parte dos testes feitos com bancos de dados sem grande esforço, o que não elimina a necessidade de bons testadores e de cuidado na hora de se realizar e interpretar os resultados apresentados.

Teste o JMeter e me diga o que achou dele! Não se esqueça que a qualidade do seu teste é consequência de um bom plano de testes!

Atualize seus Drivers JDBC3 e JDBC4 para PostgreSQL

2011-06-15T08:29:00.000-03:00

Esse link é importante para quem trabalha com JDBC. O site postgresql.org apresenta uma farta oferta de opções de drivers JDBC para o Postgres, além da documentação e detalhes do desenvolvimento.

São disponibilizados drivers para as versões em produção e para as em desenvolvimento do postgres. Confira e atualize seu ambiente!

Conecte o PostgreSQL com o LibreOffice via JDBC!

2011-06-13T10:19:00.000-03:00

A interface visual do LibreOffice (BrOffice) é similar à do ACCESS, e muito superior à da interface do psql. Um grande sonho seria poder visualizar e operar o seu banco do postgresql por meio desta interface.

Já falamos aqui da integração BASE-POSTGRESQL via SDBC. No entanto um artigo da revista brasileira do BrOffice mostra como se conectar via JDBC. Acesse aqui o artigo "BASE E POSTGRESQL: Acomodando um elefante no escritório" de Leonardo Cezar.

O texto é bastante didático e bem ilustrado, facilitando a replicação dos procedimentos, e as próximas edições prometem a continuação da matéria detalhando a operacionalização do trabalho com o postgres dentro do LibreOffice Base. É imperdível para quem está procurando uma nova ferramenta visual.

Qual é a versão do seu Postgres?

2011-06-13T09:54:00.000-03:00

Você sabe qual é a versão do seu servidor Postgres? Sabe mesmo? E do cliente (estava pensando que é sempre a mesma?)? Ele é 32 ou 64 bits? Para muitos desenvolvedores a resposta é não, e em vários casos nem se sabe como recuperar estas informações.

A melhor maneira de se saber a versão de um servidor banco de dados é simplesmente consultando-a. E no caso do postgresql a função que retorna estas informações é a VERSION().

Exemplo:
- Consulta padrão:
SELECT version();

Resultado:
PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
Para recuperar informações de versionamento do cliente do banco deve se utilizar o utilitário psql:

Exemplo:
psql --version

Resultado:
psql (PostgreSQL) 9.0.4
contém suporte a edição em linha de comando

Agora você pode visualizar facilmente a versão atual do seu postgres. Então é bom passar mais alguma informação sobre como interpretar o número de versionamento.

O postgres é versionado em um sistema de numeração com três números, no formato "A.B.C". A versão de produção atual, seguindo este formato, é a 9.0.4. A sistemática de numeração de versão do postgres pode ser conferida aqui.

- A - Número de versão principal. Quando este número muda significa que aconteceram alterações radicais na estrutura e funcionamento do banco. A versão atual é 9, e não há planejamento para uma versão 10 no momento.

- B - Número de versão secundário. Quando este número muda significa que aconteceram alterações na estrutura e funcionamento do banco que demandam. Os números A e B devem ser considerados em conjunto, e não apenas o primeiro número de versão, pois indicam uma versão em especial. O B da versão atual é 0, mas existe uma versão beta do postgres 9.1 e planejamento da versão 9.2.

- C - Número de atualizações aplicadas à versão "A.B". Na versão 8.4, por exemplo, já foram aplicadas 10 atualizações. O valor inicial de C é 0 e atualmente estamos na versão 9.0.4, indicando que a versão 9.0 já sofreu 4 atualizações. É importante acompanhar os informes de atualização especialmente quando solucionam questões chave de segurança, performance e bugs que afetam de alguma forma o desenvolvimento dos sistemas e a disponibilidade dos servidores de banco.

Que tal conferir agora a versão que está no seu sistema?

Utilize o PSQL como Gerador de Relatórios!

2011-06-08T09:44:00.000-03:00

Uma boa maneira de utilizar o utilitário psql é como meio para executar scripts que recuperem informações e as armazenem em arquivos. Desta forma, podemos gerar relatórios de alta relevância e complexidade a um custo mínimo.

A sintaxe abaixo, lê um script de um arquivo de entrada com o programa e o executa no PSQL:

psql -U usuario -d banco_de_dados -f arquivo_de_entrada

Mas como gerar relatórios de forma fácil com o psql? Simplesmente as opções são infinitas, pois podemos mesclar comandos SELECT, funções como a current_timestamp e comandos do psql. A solução depende da sua criatividade.

Abaixo coloco um script simples que recupera informações sobre os objetos do banco. A opção "\o" especifica um arquivo de saída do relatório. Para se executar o script, o mesmo foi gravado no arquivo "entrada.txt" e foi executado no prompt do psql através da chamada "psql -U postgres -d teste -f entrada.txt". O script foi testado no Postgresql 9.0.

\o saida.txt
\echo Cabecalho
SELECT 'Inicio: ' || current_timestamp as Inicio;
\echo Relatorio no Arquivo Saida.txt
SELECT '#####################################################################' AS Titulo UNION ALL
SELECT '### Relatorio de Banco de Dados 1.0 #################################' AS Titulo UNION ALL
SELECT '### Claudio Leopoldino #################################' AS Titulo UNION ALL
SELECT '### Script para livre distribuição e utilização #####################' AS Titulo UNION ALL
SELECT '### http://postgresqlbr.blogspot.com/ ###############################' AS Titulo UNION ALL
SELECT '#####################################################################' AS Titulo;
\echo Lista de Bancos de Dados
\qecho '#########################################################################################'
\qecho '### Bancos de Dados #####################################################################'
\qecho '#########################################################################################'
\l
\echo Lista de Bancos de Dados com Detalhe
\l+
\echo Lista de Usuarios
\qecho '#########################################################################################'
\qecho '### Lista de Usuarios ###################################################################'
\qecho '#########################################################################################'
\du
\echo Lista de Tabelas
\qecho '#########################################################################################'
\qecho '### TABELAS #############################################################################'
\qecho '#########################################################################################'
\dt
\echo Lista de Tabelas com Detalhe
\dt+
\echo Lista de Tabelas de Sistema
\dtS
\echo Lista de Tabelas de Sistema com Detalhe
\dtS+
\echo Lista de Indices
\qecho '#########################################################################################'
\qecho '### INDICES #############################################################################'
\qecho '#########################################################################################'
\di
\echo Lista de Indices com Detalhe
\di+
\echo Lista de Sequencias
\qecho '#########################################################################################'
\qecho '### SEQUENCIAS###########################################################################'
\qecho '#########################################################################################'
\ds
\echo Lista de Visoes
\qecho '#########################################################################################'
\qecho '### VISOES ##############################################################################'
\qecho '#########################################################################################'
\dv
\echo Lista de Visoes com Detalhe
\dv+
\echo Lista de Visoes de Sistema
\dvS
\echo Lista de Visoes de Sistema com Detalhe
\dvS+
\echo Lista de Privilegios de Acesso
\qecho '#########################################################################################'
\qecho '### PRIVILEGIOS DE ACESSO ###############################################################'
\qecho '#########################################################################################'
\dp
\echo Lista de Large Objects
\qecho '#########################################################################################'
\qecho '### LARGE OBJECTS #######################################################################'
\qecho '#########################################################################################'
\dl
\echo Lista de Funcoes
\qecho '#########################################################################################'
\qecho '### FUNCOES #############################################################################'
\qecho '#########################################################################################'
\df
\echo Lista de Operadores
\qecho '#########################################################################################'
\qecho '### OPERADORES ##########################################################################'
\qecho '#########################################################################################'
\do
\echo Lista de Tipos de Dados
\qecho '#########################################################################################'
\qecho '### TIPOS DE DADOS ######################################################################'
\qecho '#########################################################################################'
\dT
\echo Rodape
SELECT 'Final: ' || current_timestamp as Final; \q

Agora você pode executá-lo e incrementá-lo para extrair e formatar toda informação que você desejar. Pode ainda criar novos e melhores scripts! Explore opções para layouts mais agradáveis, consultas mais específicas e o que mais a sua necessidade exigir e a sua criatividade for capaz de propor!

Como melhorar este relatório? Te convido a compartilhar com a comunidade nos comentários deste post. Sua contribuição é sempre bem vinda!

Booktown e outros Bancos de Dados de Exemplo para PostgreSQL

2011-05-31T09:10:00.000-03:00

Exemplos de bancos de dados são úteis para o ensino e a aprendizagem sobre as ferramentas e sobre situações práticas que ocorrem no cotidiano dos profissionais da área. Já falei previamente sobre o pagila, mas é sempre bom ter mais opções.

O Booktown é um script que cria o banco de dados de uma livraria completa, incluindo alguns registros e possibilitando um bom banco de dados para testes e ensino de banco de dados. Originalmente, a base booktown foi utilizada nos exemplos do livro Practical PostgreSQL.

Baixe o script aqui.

Automatize o seu Backup do PostgreSQL via Python!

2011-05-30T10:54:00.000-03:00

Vi esse script no site do David Goodwin e achei interessante. Mas ao executá-lo posteriormente tive outra boa surpresa: ele executa "de primeira", sem a necessidade de se fazer qualquer correção. A idéia é através de um programa em python chamar os utilitários de backup do postgres para realizar o backup, e o código deste script pode ser alterado para automatizar outras tarefas de modo bastante simples.

As etapas de utilização são simples:

- Instale o python e o postgres. Esta etapa eu não precisei fazer, porque já o tinha instalado aqui. Teste a versão digitando: "python --version". A versão da minha máquina é a 2.6.5 e do postgres é 9.0.
- Crie um arquivo de script com o código. Use a extensão ".py" como padrão para não misturar seus códigos python os de com outras aplicações.
- Edite os campos "usuário", "senha", "caminho do pg_dump", a "lista de bancos que devem sofrer backup" e salve o arquivo. Destaquei abaixo estes parâmetros em vermelho
- Execute o script. Utilizei "python -v py_backup.py". A opção -v significa VERBOSE, isto é, gera uma descrição de tudo o que está sendo feito durante o backup. Existem outras boas opções do pg_dump e do python, mas isso fica como pesquisa paras os interessados.

Abaixo coloco o código do script, mas acesse também o site original:

#!/usr/bin/python

from time import gmtime, strftime
import subprocess
import os
database_list = [ 'database1', 'database2', 'etc' ]
USER = "postgres"
PASS = "postgres-password"
BACKUP_DIR = "e:\\postgresql_backups\\"
# dump using PostgreSQL's custom format, with maximum compression. (-F c, -Z 9)
dumper = """ "c:\\program files\\postgresql\\8.1\\bin\\pg_dump" -U %s -Z 9 -f %s -F c %s  """                  
os.putenv('PGPASSWORD', PASS)
for database_name in database_list :
        print strftime( "%Y-%m-%d-%H-%M-%S" , gmtime()) + ":dump started for %s"%database_name 
        time = str (strftime("%Y-%m-%d-%H-%M"))
        file_name = database_name + '_' + time + ".sql.pgdump"
        #Run the pg_dump command to the right directory
        command = dumper % (USER,  BACKUP_DIR + file_name, database_name)
        subprocess.call(command,shell = True)
        print strftime( "%Y-%m-%d-%H-%M-%S" , gmtime()) + ":finished"

Tente utilizar o python para chamar outros utilitários do postgres. Se quiser fazer um backup de todas as bases de dados, pode utilizar por exemplo o PG_DUMPALL.

Tem sugestões de melhoria para este script? Poste aqui no nosso fórum!

A Revista Oficial do PostgreSQL!

2011-05-23T08:32:00.000-03:00

É apenas um teste, mas pode se concretizar. O primeiro número da revista oficial do PostgreSQL está no ar e você pode baixar gratuitamente. Acesse e confira! Caso a recepção seja boa, a revista ganhará novas edições.

O primeiro número me agradou bastante, explorando vários itens sobre as futuras versões do Postgres, questões de performance e dicas específicas para sistemas operacionais, o que nem sempre é fácil de se encontrar.

Você pode colaborar adquirindo a versão impressa ou submetendo matérias para os editores. Confira!

Participe: Edital para Instrutor de PostgreSQL - UNESCO/ FUNAI

2011-05-19T09:45:00.000-03:00

Aos instrutores de banco de dados, segue a notícia de edital para instrutor de PostgreSQL. A indicação foi do amigo Bruno Rebello. Participem e divulguem entre os possíveis candidatos! Os editais estão nos sites da UNESCO e da FUNAI.

Avisem sempre que tiverem vagas de seleções e concursos relacionados como PostgreSQL para divulgação neste espaço!

_______________________________________________________________

EDITAL 002/2011 - A UNESCO e FUNAI, por meio do Projeto 914BRA4008 – “Impactos do Desenvolvimento e Salvaguarda de Comunidades Indígenas”, seleciona na modalidade “PRODUTO”, 01 profissional com Graduação na área de Tecnologia da Informação e/ou pós-graduação de, no mínimo, 360 horas, mestrado ou doutorado em área de Tecnologia da Informação fornecido por instituição reconhecida pelo Ministério da Educação para Capacitação de técnicos/servidores da FUNAI, para operação e administração do banco de dados PostgreSQL, banco de dados utilizado para armazenar toda a estrutura de dados projetada no desenvolvimento do Sistema Indigenista de Informações - sistema esse que monitora a implantação dos Planos de Salvaguarda da FUNAI.

Os interessados deverão enviar o CV do dia 16/maio/2011 até às 23h 59 do dia 23/maio/2011 para o endereço unescobra4008@gmail.com, no formato Word, Open Office ou PDF , indicando o número do edital e o nome do perfil em que se candidata. Serão desconsiderados os CVs remetidos após a data limite indicada neste edital. Para mais informações, consultar o edital completo que será publicado nos sites www.funai.gov.br e www.unesco.org.br

Acesso ao Edital: 914BRA4008 Edital 002/2011, Anexo II (modelo padrão currículo).

Em atenção às disposições do Decreto nº 5.151, de 22 de julho de 2004 é vedada a contratação, a qualquer título, de servidores ativos da Administração Pública Federal, Estadual, do Distrito Federal ou Municipal, direta ou indireta, bem como de empregados de suas subsidiárias e controladas, no âmbito dos projetos de cooperação técnica internacional.

Estas contratações serão efetuadas mediante processo seletivo simplificado (análise de currículo e entrevista), a ser realizado com no mínimo 03 (três) candidatos, por vaga, com currículos válidos e maior pontuação, sendo exigida, destes profissionais, a comprovação da habilitação profissional e da capacidade técnica ou científica compatível com os trabalhos a serem executados.

De acordo com a Portaria nº 717, de 09/12/2006, é vedada a contratação de consultor que já esteja cumprindo contrato de consultoria por produto vinculado a projeto de cooperação técnica internacional.

Data de Publicação do Edital: 16/05/2011

Squirrel Client: Suas Conexões em um Único Lugar!

2011-05-16T15:02:00.000-03:00

Conexões são muito importantes e temos de gerenciá-las. Quando temos conexões para vários bancos de dados distintos em múltiplos servidores e ambientes de desenvolvimento, produção e homologação, esta gestão pode se tornar complicada. O Squirrel Client é uma boa ferramenta que permite a gestão das conexões, a visualização e a manipulação de dados do PostgreSQL e da maioria dos bancos de dados livres e proprietários, unificando em um ponto esta função. (eles só não têm um logotipo decente, então ilustrei com esse que encontrei na internet)

A instalação é bem fácil e se desejar baixar e executar direto do arquivo JAR ou do script .SH sem precisar instalar a ferramenta, não há segredos (é o que eu faço no cotidiano). No windows pode ser acionado pelo arquivo .BAT.

Para utilizar o Squirrel Client, basta baixar a ferramenta e o driver do banco de dados que se deseja acessar. A lista de bancos suportados pela ferramenta é muito vasta, e as configurações a serem feitas não são sofisticadas:

Primeiro passo: Configurar driver

No caso do PostgreSQL, o driver pode ser baixado facilmente. Neste exemplo foi utilizado um driver jdbc, copiado para a pasta lib do squirrel client.

Selecione a aba "Drivers" à esquerda da tela. Informe a string de conexão, dê uma descrição para o driver e confirme.

Segundo passo: Configurar conexão

Uma vez que um driver pode ser utilizado em várias conexões, deve-se configurar a conexão que utiliza o driver.

Acione a aba"Aliases" e clique no ícone "+" para criar uma nova conexão. Informe o nome, o driver que você criou, a string de conexão, usuário e senha, conforme a figura e confirme.

Para conectar, clique no primeiro ícone da aba "Aliases".

Visualizando o banco de dados

A conexão faz com que sejam mostradas duas abas: "Objects" e "SQL". A aba "Objects" permite a visualização do banco de dados através de uma treeview e de várias abas internas que são dinamicamente preenchidas à medida em que um objeto do banco de dados é selecionado.

A interface é bem detalhada e são apresentadas informações detalhadas a respeito dos metadados que não são comumente mostradas em outras ferramentas similares como permissões de acesso de tabelas e colunas.

Observe que as informações só serão 100% confiáveis se a coleta de estatísticas do banco de dados estiver atualizada.

Trabalhando com o banco de dados

A aba SQL permite a realização de consultas e a criação, salvamento e recuperação de scripts sql.

Funções como auto correct/ abreviations e bookmarks podem aumentar a produtividade do usuário.

A execução simultânea de várias conexões é muito útil para evitar a abertura de vários visualizadores em ferramentas distintas.

É possível criar um ou mais diagramas utilizando o botão direito do mouse sobre as tabelas e selecionando a opção "add to a graph", um bônus muito interessante, pois mostra os relacionamentos e permite imprimir de várias formas!

Limitações

Por ser uma ferramenta de acesso a dados, não apresenta recursos de design mais avançados para a opção de elaboração de diagramas, que gera diagramas através do esquema SQL, mas não gera esquema SQL com base no diagrama. No entanto isso não chega a ser um limitador, apenas não é o propósito da ferramenta.

O suporte a Hibernate não foi testado neste post, mas parece ser uma das features mais interessantes para os usuários deste framework.

A única restrição que encontrei foi o fato do sistema não apresentar o plano de execução das consultas que utilizei no teste (não testei outros bancos além do Postgresql). Possivelmente as novas versões supram esta necessidade futuramente.

O squirrel client se mostra adequado para manter e centralizar conexões a múltiplos bancos de dados, visualizar seus metadados e realizar operações no banco que demandem SQL. Veja os screenshots, baixe, instale e teste!

Ordene Registros Fisicamente com o Comando CLUSTER!

2011-05-13T18:20:00.000-03:00

O comando CLUSTER permite a ordenação física dos dados de uma tabela com base em um índice. Esta pode ser uma boa opção quando um grande número de leituras sequenciais for realizado com base em um ou mais campos. É um comando já antigo, mas pouco conhecido e utilizado.

A operação de ordenação é feita uma vez no momento da execução do comando, e caso se deseje que os dados continuem ordenados fisicamente, deve-se reexecutar o comando após alterações nos registros, o que pode tomar muito tempo de processamento.

Um problema com este comando é a utilização de arquivos temporários que pode exigir espaço em disco extra, além de acessos a disco que tornem a operação mais demorada..

A sintaxe é relativamente simples:

CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

A cláusula VERBOSE faz com que sejam mostradas informações do andamento da clusterização.

A sintaxe com nome da tabela e nome do índice é a mais segura, pois se sabe exatamente que tabela e que índice são utilizados.

A redação com o nome da tabela e sem o nome do índice assume que será utilizado para a ordenação o último índice utilizado.

A utilização de CLUSTER sem indicação de tabela ou índice reordena todas as tabelas que já tenham sido clusterizadas, o que pode ser arriscado, pois estas classificações de dados podem tomar um tempo substancial.

Por ser uma operação muito custosa, durante a clusterização a tabela fica indisponível para leituras e escritas, o que faz com que esse comando seja um candidato para momentos do dia de menor quantidade de acessos.

Abaixo temos um exemplo de clusterização, com criação de tabela, índices e dados e execução dos comandos de clusterização:

/*
Criação da tabela e inserção nos dados
*/

begin transaction;

drop table TAB_CLUSTER;
/*
drop index TAB_CLUSTER_COD;
drop index TAB_CLUSTER_VALOR;
*/

CREATE TABLE TAB_CLUSTER (
COD INTEGER, DESCRICAO VARCHAR(50), DATAHORA TIMESTAMP, VALOR INTEGER);

create index TAB_CLUSTER_COD on TAB_CLUSTER(COD);
create index TAB_CLUSTER_VALOR on TAB_CLUSTER(VALOR);

insert into TAB_CLUSTER values (round(CAST (random()*500 AS NUMERIC),0), 'Teste do comando Cluster', current_timestamp, round(CAST (random()*500 AS NUMERIC),0));
insert into TAB_CLUSTER values (round(CAST (random()*500 AS NUMERIC),0), 'Teste do comando Cluster', current_timestamp, round(CAST (random()*500 AS NUMERIC),0));
insert into TAB_CLUSTER values (round(CAST (random()*500 AS NUMERIC),0), 'Teste do comando Cluster', current_timestamp, round(CAST (random()*500 AS NUMERIC),0));
insert into TAB_CLUSTER values (round(CAST (random()*500 AS NUMERIC),0), 'Teste do comando Cluster', current_timestamp, round(CAST (random()*500 AS NUMERIC),0));
insert into TAB_CLUSTER values (round(CAST (random()*500 AS NUMERIC),0), 'Teste do comando Cluster', current_timestamp, round(CAST (random()*500 AS NUMERIC),0));

commit transaction;

cluster tab_cluster using tab_cluster_cod;

cluster tab_cluster using tab_cluster_valor;

Alternativas ao comando CLUSTER envolvem o uso de CREATE TABLE AS (sintaxe recomendada por compatível com Pl/ PgSQL) ou SELECT INTO. Ambos os comandos leem um conjunto der registros, ordenam e gravam em um local determinado:

- CREATE TABLE tab_cluster_teste1 AS SELECT * FROM tab_cluster ORDER BY cod;
- SELECT * INTO tab_cluster3 FROM tab_cluster ORDER BY cod;

PostgreSQL no Ubuntu: Primeiros Passos Após a Instalação

2011-05-12T15:54:00.000-03:00

Este post é uma continuação do de instalação do postgresql 9.0 no Ubuntu. Desta vez o referencial veio do site stuartellis.eu.

Uma vez instalado o banco, o serviço está ativo.

Por padrão existe um banco de dados postgres e um usuário postgres. É necessário configurar o acesso para poder cadastrar usuários.

Entre no terminal e siga os seguintes passos:

- sudo -u postgres psql - Abre o utilitário PSQL utilizando o usuário logado na máquina
- ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'senhaforte'; - Dentro do psql, define a senha do role postgres
- \q - Encerra a sessão do psql

Agora altere os arquivos postgresql.conf e pg_hba.conf para habilitar o acesso:

- cd /etc/postgresql/9.0/main
- sudo gedit postgresql.conf - Alterar parâmetro listen_addresses para '*' ou para a lista de endereços desejada
- sudo gedit pg_hba.conf - Alterar configuração de segurança
- sudo postgresql service restart - Reiniciar o serviço atualiza os parâmetros alterados

Agora você pode entrar no psql como usuário postgresql, executar scripts, etc:

- sudo -u postgres psql -d postgres

Minha Instalação do PostgreSQL 9.0

2011-05-12T14:20:00.000-03:00

A minha instalação de postgresql 9.0 no Ubuntu Linux, que já está defasada em relação à nova versão de desenvolvimento (9.1 - anúncio, download), eu fiz com base no link do sítio do Vinicius.

Utilizei estes comandos no terminal:
- sudo apt-get update - atualização das listas de pacotes do apt-get
- sudo apt-get install postgresql-9.0 - instala o pacote do postgresql, versão 9.0
- sudo apt-get install pgadmin3 - instala o pgadmin
- sudo apt-get autoremove - removendo pacotes não mais necessários
- service postgresql status - teste se a instalação está funcionando. A resposta foi: "Running clusters: 9.0/main". O servidor está no ar!

Agora é executar, estudar e fazer testes!

Claro que existem dezenas de sítios com tutoriais e boas dicas sobre como fazer a instalação, e este post não visa aprofundar o tema. Mas é sempre bom lembrar: que versão está na sua máquina?

Uma Leitura Diferente: Teses e Dissertações Sobre PostgreSQL

2010-12-29T16:01:00.001-03:00

Está sem idéias para o seu projeto de monografia? Está procurando alguma leitura mais aprofundada sobre banco de dados? Para encontrar a solução para estas questões, a Biblioteca de Teses e Dissertações do IBICT (Instituto Brasileiro de Informação em Ciência e Tecnologia) é uma boa opção.

Sua base de dados possui um acervo de pesquisas que engloba áreas como administração, medicina, psicologia e ciência da computação. Fiz uma busca e encontrei 16 documentos relacionados sobre o Postgres e o número tende a crescer.

Nos textos referentes ao Postgres a qualidade dos textos é relativamente boa e a ferramenta é extendida pela implementação de complementos, explorada como ferramenta de desenvolvimento e comparada com outras ferramentas. Os textos apresentam sugestões de novas pesquisas nas suas considerações finais. RECOMENDO!

PgDay 2011 em Cuba: Chamada de Trabalhos

2010-12-22T17:12:00.001-03:00

Uma boa oportunidade para conhecer Cuba é a terceira edição do seu PgDay. A chamada de trabalhos está aberta até o dia 10 de janeiro. O evento está marcado para os dias 1 a 5 de fevereiro de 2011. Mais informações aqui.

Existem muitas curiosidades sobre a comunidade cubana de Postgres que tornam este evento atraente e peculiar. Por ser um país socialista, as decisões são tomadas por consenso em comitês. A necessidade de adotar tecnologias mais acessíveis fez com que o Postgres nos últimos anos se tornasse um dos bancos de dados mais adotados no país, e o google insights mostra que a terra de Fidel atualmente é o país que mais faz buscas sobre o Postgres no mundo todo (o Brasil está em décimo lugar no mesmo levantamento).

O PgDay cubano é mais formal que o da maioria dos países e tem forte presença estatal. A chamada de trabalhos é bem detalhada, pedindo artigos escritos com rigor para registro nos anais do evento. Os melhores textos para uma edição especial da "Revista Cubana de Ciencias Informáticas".

Os interessados devem se apressar, pois o prazo é bem curto!

Tenha os Manuais de Referência do PostgreSQL!

2010-12-16T09:47:00.000-03:00

É sempre bom poder consultar fontes robustas de conhecimento para melhor conduzir este projeto. As "Bíblias do Oracle" ou as grandes compilações de APIs do Windows são algumas das publicações que muito me ajudaram. No caso do Postgres, os manuais disponibilizados na web sao a referência mais atual e a mais referenciada. No entanto, para muitos de nós a pesquisa em livros é mais prazeroza, e instituições de ensino e empresas ainda utilizam muito material em papel para pesquisas.

A gráfica britânica "Network Theory LTS" é uma empresa especializada em editoração e impressão de manuais de software livre como Python e CVS e está disponibilizando a documentação do Postgres 9.0.1 na forma de quatro livros. Os preços são relativamente baixos e parte do valor arrecadado vai para a comunidade de desenvolvimento do PostgreSQL (1 dolar por livro).

Abaixo coloco as referências:

- The PostgreSQL 9.0 Reference Manual - Volume 1A - The SQL Language
by The PostgreSQL Global Development Group
ISBN 978-1-906966-04-1, Price $19.95 (£14.95)

-The PostgreSQL 9.0 Reference Manual - Volume 1B - SQL Command Reference
by The PostgreSQL Global Development Group
ISBN 978-1-906966-05-8, Price $19.95 (£14.95)

- The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
ISBN 978-1-906966-06-5, Price $19.95 (£14.95)

- The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
ISBN 978-1-906966-07-2, Price $14.95 (£9.95)

Acesse o site aqui e não se esqueça de avaliar também o custo do frete do material antes de fazer qualquer compra!

Postgresql 9.1 em pleno desenvolvimento!

2010-11-08T09:01:00.001-03:00

O Postgresql 9.1 em pleno desenvolvimento e procurando mais testadores. A versão 9.1 está atualmente em sua segunda versão alfa, com evoluções da versão 9.0. As novas mudanças envolvem features de segurança, sql e recursos de administração e performance.

Conheça as funcionalidade aqui: http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html

Participe dos testes clicando aqui!

Catálogo do Postgres: uma imagem vale por mil palavras!

2010-10-06T15:54:00.000-03:00

Compreender os metadados de um SGBD é a forma mais direta de saber como encontrar informações sobre o estado dos bancos de dados de um servidor. No caso do PostgreSQL, as várias tabelas e visões do sistema nem sempre são conhecidas pelo desenvolvedor, o que pode gerar problemas.

A imagem abaixo sintetiza o catálogo do Postgres em sua versão 8.3, apresentando uma visão geral das relações entre as tabelas de metadados. O diagrama não está compĺeto, por terem sido omitidos alguns dos atributos das relações e faltam visões de sistema como PG_TABLES e PG_LOCKS, mas me parece uma iniciativa pioneira para abrir estas informações para um público maior.

A imagem foi encontrada neste site peruano. Caso alguém conheça mais sobre a origem desta imagem e seu(s) autor(es) ou saiba de outra que englobe mais informação ou versões mais novas do Postgres, não deixe de me avisar!!!

A Cláusula CASE

2010-10-04T15:28:00.000-03:00

O uso de expressões condicionais dentro de SQL permite a geração de resultados mais claros sem a necessidade de se programar dentro do código das aplicações as condições a serem verificadas.

A cláusula CASE permite que os valores retornados pelas consultas possam ser modificados caso sejam compatíveis com determinadas expressões de forma relativamente simples:

CASE WHEN condition THEN result

[WHEN ...]

[ELSE result]
END

A cláusula CASE aceita uma condição consiste em uma expressão simples e retorna o valor que consta após a cláusula THEN, caso a condição seja satisfeita. Podem ser utilizados opcionalmente mais de um WHEN no comando e a cláusula ELSE que é disparada quando nenhuma das condições do case seja satisfeita.

Abaixo, os comandos de criação de duas tabelas e a inserção de dados que serão utilizados para os exemplos de sintaxe:

CREATE TABLE VALORES (val INTEGER);
INSERT INTO VALORES VALUES (1);
INSERT INTO VALORES VALUES (2);

CREATE TABLE SIGNOS (val CHAR(1));
INSERT INTO SIGNOS VALUES ('A');
INSERT INTO SIGNOS VALUES ('T');
INSERT INTO SIGNOS VALUES ('G');
INSERT INTO SIGNOS VALUES ('X');

Sintaxe 1: Caso o valor numérico corresponda à condição, retorna uma string explicativa.

SELECT val,
CASE
    WHEN val=1 THEN 'Primeiro'
    WHEN val=2 THEN 'Segundo'
    ELSE 'Não Classificado'
END
FROM VALORES;

val |   case
-----+----------
   1 | Primeiro
   2 | Segundo
(2 registros)

Sintaxe 2: Testa se o valor numérico é par ou ímpar e retorna uma string explicativa.

SELECT val,
CASE
    WHEN val%2=1 THEN 'Ímpar'
    WHEN val%2=0 THEN 'Par'
END
FROM VALORES;

val | case
-----+-------
   1 | Ímpar
   2 | Par
(2 registros)

Sintaxe 3: O valor reTesta se o valor numérico é par ou ímpar e retorna uma string explicativa.

SELECT val,
CASE
    WHEN val='A' THEN 'Primeiro'
    WHEN val='T' THEN 'Segundo'
    WHEN val='G' THEN 'Terceiro'
    ELSE 'Não Classificado'
END
FROM SIGNOS;

val |       case
-----+------------------
A   | Primeiro
T   | Segundo
G   | Terceiro
X   | Não Classificado
(4 registros)

Nota: para condições mais complexas e que tomem muito tempo de processamento, pode ser criada uma função armazenada, que realiza as verificações de um case.

Habemus Postgresql 9.0!!!

2010-09-20T11:02:00.001-03:00

Lançada oficialmente a versão 9.0 do Postgresql, com novas funcionalidade e recursos para escalabilidade a muito tempo esperados! Atualize sua versão aqui!

Boa oportunidade para quem deseja estudar banco de dados e para quem já trabalha com a ferramenta de fazer uma atualização no servidor!

Abaixo, coloco o texto do lançamento, toscamente traduzido do inglês:

O PostgreSQL 9.0 chegou! O Grupo de Desenvolvimento Global do ostgreSQLanuncia a disponibilidade do nosso lançamento mais aguardado.
PostgreSQL 9.0 inclui replicação binária "built-in" e mais de uma dúziaoutras características importantes voltadas a todos os desenvolvedores, de web hackers a administradores de banco de dados.
A versão 9.0 inclui funcionalidades mais importantes do que qualquer liberação antes dela, incluindo:* Hot standby* Streaming Replication* Atualizações In-Place* Versões para Windows 64-bit * Fácil gerenciamento de permissões em massa* Blocos anônimos de código e nomeação de chamadas de parâmetros para procedimentos armazenados
* Novas "Window Functions" e ordenação de agregados
... e muito mais alterações. Para mais detalhes sobre as mais de 200 adições emelhorias nesta versão, desenvolvida por mais de uma centena de colaboradores, consulte as notas de lançamento.

PostgreSQL Beta 4 Lançado!

2010-08-30T10:07:00.000-03:00

Liberada nova versão de teste do Postgresql, a beta 4.

Vamos continuar testando e reportando problemas!

Notas de lançamento da versão aqui! Download aqui!

Coloque o PostgreSQL no seu Navegador!

2010-08-30T09:35:00.001-03:00

Esse é uma dica para quem gosta de personalizar o seu computador com temas e papéis de parede.

O Personas é um complemento do navegador Firefox que permite a fácil instalação de temas. Também é possível criar a sua própria skin para o firefox, de uma forma relativamente fácil, embora demore alguns dias para a mesma passar pelo processo de aprovação. É uma oportunidade para exercer toda a sua criatividade e personalizar sua máquina!

Existem temas sobre filmes, personagens de anime, artistas e sobre softwares e é muito fácil escolher e aplicar novas opções sem afetar o desempenho do navegador. O linux, por exemplo, tem dezenas de opções!

Até a semana passada, não havia nenhum sobre o Postgres e criei o primeiro, penando muito no uso do Gimp: "PostgreSQL Simple", que aparece na imagem que ilustra este post. Espero que apareçam em breve outras opções mais criativas :)

Problemas:
- Preferível para o Firefor 3.6.* em diante, não estando disponível para outros navegadores como IE.

Boa Apostila de Introdução ao Postgres

2010-07-14T09:47:00.005-03:00

Encontrei uma apostila de postgres bem simples de 2005, ainda referente à versão 7.*, mas ao mesmo tempo bastante concisa e prática para quem está aprendendo ou ensinando. Baixe o pdf aqui.

Não consegui maiores informações sobre a autora, Vanessa Rocha Solgate.

Você sabe de alguma outra apostila que possa ser referenciada?

Atualização: Fecharam o acesso à apostila. :(

Tentativas de acesso geram a mensagem: "You don't have permission to access /gs/treinamento/graduacao/apostilaPostgre.pdf on this server.".

Colocarei em breve mais links para outros materiais que não sejam privados à medida em que for descobrindo.

PostgreSQL Feature Matrix!

2010-07-07T12:35:00.002-03:00

Você sabe a partir de que versão o Postgres implementa a cláusula RETURNING ou cursores atualizáveis? Essas informações são importantes para a compatibilidade entre as várias versões do SGBD, assim como para acompanhar a evolução da ferramenta a cada ciclo de desenvolvimento, e estão disponíveis na PostgreSQL Feature Matrix!

Atualmente a matriz de funcionalidades oficial do postgres apresenta as mudanças originadas nas versões 7.48 até 9.0, beta 2.

PostgreSQL 9.0: Quais são as novidades? A visão das "funcionalidades da semana" - Parte 2!

2010-05-31T07:41:00.005-03:00

O PostgreSQL está em sua quarta versão alfa, e começam a aparecer indicações das novas funcionalidades e alterações que foram introduzidas. O informe semanal "PostgreSQL Weekly News", organizado por David Fetter apresenta uma seção chamada "Feature of the Week", ou funcionalidade da semana, descrevendo uma alteração em desenvolvimento. Os informes originais podem ser consultados aqui.

Abaixo listo as últimas funcionalidades citadas, agregando algum comentário quando pertinente:

31/05/2010 - Esta semana não teve "funcionalidade da semana". Nas colunas anteriores a coluna era escrita por David Fetter e Devrim GUNDUZ, mas o segundo não participou da coluna desta semana. Pode ser que a feature of the week volte, ou não...

23/05/2010 - Objetos grandes (lo_ *) agora possuem controles de acesso, como os demais objetos do banco de dados. É mais um fator positivo para a utilização de LOBs em geral.

16/05/2010 - O utilitário pg_ctl agora tem uma opçao initdb. Esta opção inicializa um servidor postgres. Mais informações aqui.

09/05/2010 - Você pode criar triggers por coluna, ou seja, que são disparads somente na modificação das colunas especificadas. Recurso utilíssimo!!!

A sintaxe, como descrita no SQL: 2008, é: CREATE TRIGGER trigger_name (BEFORE | AFTER) UPDATE OF col1 [,] ... ON col2 coln tablename FOR EACH ROW trigger_function PROCESSO EXECUTE ();

02/05/2010 - Agora você pode chamar funções com parâmetros nomeados, por exemplo, parameter_bar foo ('valor' AS parameter_foo, o valor 'outro' AS).

Recurso muito prático se você lembra o nome dos os parâmetros, mas não a ordem em que estão na declaração da função.

25/04/2010 - Funções podem agora ter valores padrão para parâmetros. Ótima e simples idéia!!!

18/04/2010 - GUCs agora são reguláveis por função e por banco de dados.

Você sabe o que é GUC? Global User Configuration (GUC) são configurações de servidor do arquivo postgresql.conf. Poder definir parâmetros de servidor para cada função é ter mais flexibilidade. Mas a forma como isso será implementado não parece clara ainda.

11/04/2010 - "You can now GRANT and REVOKE on objects schema-wide in a single command".

Mais recursos para configurar permissões de acesso, desta vez afetando todo o esquema do banco com apenas um comando. Faltam mais detalhes.

04/04/2010 - Existe agora um comando chamado ALTER DEFAULT PRIVILEGES, o qual permite o ajuste de privilégios que serão aplicados a objetos a serem criados no futuro.

É uma opção boa para préconfigurar os objetos do banco de dados utilzando grant e revoke para tabelas, sequências e funções.

01/04/2010 - "Distributed map-reduce functions in Erlang". Desconsiderem todo anúncio da equipe do Postgres feito no primeiro de abril!!!

Participe do PgDay de Brasília 2010!

2010-05-28T11:29:00.003-03:00

Faltam poucos dias para o PgDay em Brasília! Informe-se e se inscreva aqui.

Erros de "Permission Denied na Criação de Tablespaces"

2010-04-20T08:33:00.002-03:00

Em muitos casos os problemas que mais incomodam são os mais simples. No caso do Postgresql, um problema bem comum mas que causa estresse é a falta de permissão para a criação de tablespaces.

Tablespaces permitem um maior controle sobre a localização física dos dados, metadados, índices e estatísticas do banco. Informações sobre criação de tables paces podem ser obtidas aqui. O postgresql utiliza o sistema de arquivos fornecido pelo sistema operacional, o que muitas vezes gera problemas de autorização de acesso que impedem a sua criação, o clássico erro "permission denied".

Neste post vamos mostrar como definir corretamente as permissões para criação de tablespaces em ambiente windows.

Passo 1: Criar pasta do tablespace.
Crie uma pasta sem arquivos dentro para a definição do novo tablespace.

Passo 2: Abrir tela de configuração
Entre no Windows Explorer e acione o menu "Ferramentas/ Opções de Pasta...".
O sistema apresentará uma tela com as opções de pasta.
Desmarque a opção "Usar Compartilhamento Simples de Arquivo (Recomendado)" e confirme a operação. Isso habilitará a aba de segurança das pastas, que serrá utilizada para definir as permissões para a criação do tablespace.

Passo 3: Configuração da segurança da pasta
Na pasta criada para o tablespace, clique com o botão direito e acione o menu “Propriedades”.

Em seguida, clique na aba segurança.

Na tela apresentada, clique em “Adicionar”. Aparecerá uma tela como a que aparece abaixo. Defina o local (no botão "Locais") e, no nome do objeto, coloque o usuário do banco que vai ter acesso ao tablespace. Confirme com o botão “OK”.

Na aba de segurança aparecerá o novo usuário. Marque a opção de “Controle Total”, o que fará com que o banco de dados possa efetivamente acessar o tablespace.

Passo 4: Criação do tablespace.

Utilize para isso o comando CREATE TABLESPACE.

Este post teve contribuição de Jeferson Gallina.

PostgreSQL 9.0: Quais são as novidades? A visão das "funcionalidades da semana" - Parte 1!!!

2010-03-08T09:34:00.013-03:00

Abaixo listo as últimas funcionalidades citadas, agregando algum comentário quando pertinente:

28/03/2010 - Implementação do conceito de blocos anônimos de código (anonymous code blocks) através do comando DO nas linguagens PL/pgsql, PL/Perl, e PL/LOLCODE.

Estes blocos compreendem comandos que podem ser executados em um bloco de código sem um nome, utilizando as poderosas linguagens procedurais do PostgreSQL. Desta forma podem ser executados blocos de código sem que se precise criar funções dentro do banco. Parece uma ótima forma de se criar scripts.

O exemplo abaixo eu peguei aqui:

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT table_schema, table_name FROM information_schema.tables
           WHERE table_type = 'VIEW' AND table_schema = 'public'
  LOOP
      EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
  END LOOP;
END$$;

21/03/2010 - Campos do tipo Hstore não tem mais o limite de 64kB para o limite de chaves e suportam árvore B e classes de operadores hash, permitindo GROUP BY, DISTINCT, etc.

Obs: Você sabe por acaso o que é HSTORE? Eu também não sabia, mas é um módulo que implementa um tipo de dados chamado HSTORE que agrega ao mesmo tempo uma chave e um valor, ambos strings. Este novo tipo de dado é útil para uma série de cenários. Mais informações podem ser obtidas aqui.

14/03/2010 - Em PL/pgsql, o comando MOVE agora funciona de forma compreensível com Cursores.

Obs: Não deu para entender o problema que havia antes, mas este comando reposiciona o cursor sem recuperar dados (NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, ou BACKWARD).

07/03/2010 - A saída do EXPLAIN pode ser formatada como XML, JSON, YAML e o mecanismo de análise está muito mais simples. O formato tradicional de texto ainda é o formato padrão.

Obs: Os novos formatos permitem a definição de relatórios e ferramentas de análise dos resultados do comando Explain, facilitando a otimização de consultas.

28/02/2010 - A suíte pgbench agora é multi-threaded, o que lhe permite tirar partido de múltiplos núcleos de CPU.

Obs: O pgbench é um utilitário que faz o benchmark de desempenho do servidor. Pode ser utilizada para testes de estresse, carga e performance. O uso de múltiplas threads faz com que os resultados sejam mais confiáveis e obtidos em menor tempo.

21/02/2010 - Agora você pode controlar o comportamento de valores distintos para cada coluna com ALTER TABLE ALTER COLUMN ... ... SET (parâmetro = valor ,...) onde parâmetro pode ser uma das n_distinct e n_distinct_inherited. Valores positivos são assumidos como sendo o número de valores distintos aceitos, 0 diz que o planejador da consulta deve utilizar os resultados do comando ANALYZE, e os números negativos (que devem estar entre -1 e 0) fazem com que o planejador estime o número de valores distintos como o número de linhas multiplicado pelo valor absoluto do número.

Obs: Funcionalidade de performance, para consultas específicas.

14/02/2010 - Violações de restrição de unicidade de valor agora geram mensagens de erro mais detalhadas.

Obs: Agora fica mais fácil encontrar a causa de exceções de valores repetidos e como resolvê-las.

31/01/2010 - A checagem de constraints de não repetição de valores pode agora ser adiada até a hora do commit.

Obs: Funcionalidade de performance, para consultas específicas.

24/01/2010 - A sintaxe DROP IF EXISTS agora trabalha em colunas e restrições.

Obs: Sintaxe interessante para os desenvolvedores de ferramentas de banco de dados.

17/01/2010 - O Vacuum full foi alterado para gerar novos arquivos para tabelas e índices por padrão. Esta implementação é baseada no antigo comando CLUSTER e mais eficiente e efetiva. A funcionalidade antiga do VACUUM FULL ainda pode ser acessada através do comando VACUUM FULL INPLACE, mas será incompatível com o Hot Standby.

Obs: O Vacuum Full Implace só parece ser vantajoso para sistemas com pouco espaço em disco. A nova Implementação promete ganho de tempo nas manutenções de banco.

10/01/2010 - Agora você pode armazenar em log o estado de consultas SQL, erros, etc, usando "%e" na sua log_line_prefix.

03/01/2010 - No psql o uso de "\d" agora mostra quantas tabelas herdadas uma tabela-mãe tem, e "\d+" lista os nomes das tabelas herdadas.

Obs: Alteração de pouco impacto para quem não utiliza recursos objeto-relacionais.

27/12/2009 - Hot Standby. Após 1,5 anos de desenvolvimento, você pode finalmente executar consultas somente leitura PITR contra os escravos. Graças ao Simon Riggs, Heikki Linnakangas, qualquer muitos outros esforços incessantes.

Obs: O melhor uso de hardware promete ganho de desempenho e redução de custos para grandes sistemas escaláveis e resistentes a falhas. Esta é uma das funcionalidaes que merecerá bastante atenção dos DBAs.

20/12/2009 - Cláusulas WHEN sobre Triggers. Na versão 8.5 Alpha3 você será capaz de criar triggers com uma cláusula WHEN para que as mesmas executem apenas se os valores ou condições específicas ocorrerem. Graças Itagaki Takahiro e a equipe da NTT.

Obs: Esta alteração permite triggers mais específicas com menos validações internas, possivelmente impactando positivamente seu desempenho.

13/12/2009 - Constraints de exclusão (por Jeff Davis) permitem que você especifique como únicos "dados" que abranjam um intervalo, como uma área geométrica, um período de tempo, ou um array.

Obs: Função importante para quem trabalha com lógicas de negócios no banco de dados.

Abaixo, a redação oficial da coluna:

07/03/2010 - EXPLAIN output can be formatted as XML, JSON, and YAML, making machine parsing much simpler. The traditional text format is still the default format.

28/02/2010 - The pgbench suite is now multi-threaded, allowing you to take advantage of multiple CPU cores.

21/02/2010 - You can now control the behavior of distinct values per column using ALTER TABLE...ALTER COLUMN...SET (parameter=value,...) where parameter can be one of n_distinct and n_distinct_inherited. Positive numbers are assumed to be the number of distinct values, 0 tells the planner to use the results from ANALYZE, and negative numbers (which should be between -1 and 0, cause the planner to estimate the number of distinct values as the estimated number of rows multiplied by the absolute value of the number.

14/02/2010 - Uniqueness violations now raise more detailed error messages.

31/01/2010 - Uniqueness constraints can now be deferred until commit time.

24/01/2010 - The DROP IF EXISTS syntax now works on columns and constraints.

17/01/2010 - VACUUM FULL has been changed to now generate all-new files for the vacuumed table and indexes. This is based on the old CLUSTER command, and is both more efficient and more effective. The old functionality of VACUUM FULL can still be accessed via VACUUM FULL INPLACE, but will be incompatible with Hot Standby.

10/01/2010 - You can now log SQL state for queries, errors, etc., using %e in your log_line_prefix.

03/01/2010 - In psql, \d now shows how many inherited tables a parent has, and \d+ lists them.

27/12/2009 - Hot Standby. After 1.5 years of development, you can at last run read-only queries against PITR slaves. Thanks to Simon Riggs, Heikki Linnakangas, any many others for unceasing efforts.

20/12/2009 - WHEN clauses on Triggers. In 8.5Alpha3, you will be able to create triggers with a WHEN clause so that they will only execute if specific values or conditions occur. Thanks Itagaki Takahiro and the NTT team.

13/12/2009 - Exclusion Constraints (by Jeff Davis) allow you to specify as "unique" data which covers a range, such as a geometric area, a period of time, or an array.

O que acharam destas novas funcionalidades?

Elas impactarão o seu trabalho atual?

Acompanhe o Status do Servidor no PgAdmin III

2010-03-04T09:20:00.006-03:00

Acompanhar os acessos ao banco de dados é uma necessidade para quem lida com aplicações de maior porte. O PgAdmin III apresenta um bom painel que premite acompanhar o servidor, mostrando as conexões, bloqueios, transações correntes e o arquivo de log de auditoria.

O recurso pode ser utilizado através do menu "Ferramentas/ Status do Servidor". Abaixo, uma visualização da tela apresentada.

DreamCoder for PostgreSQL: Ferramenta Visual

2010-03-01T15:04:00.008-03:00

Uma das maiores queixas dos profissionais de banco de dados é a falta de ferramentas visuais robustas.

As opções abertas contribuem para reduzir este problema, mas ainda apresentam deficiências e lacunas de importantes funcionalidades que restringem sua utilização e difusão. Ao mesmo tempo, versões freeware de softwares comerciais podem ser úteis em virtude de suas funcionalidades, apesar de não terem seu código disponibilizado.

O DreamCoder for PostgreSQL é uma opção freeware que promete facilitar o trabalho com este banco de dados uma rápida análise da ferramenta mostrou pontos fortes e deficiências importantes. O software pode ser baixado aqui.

Abaixo, uma tela com a interface da ferramenta.

O editor de plpgsql apresenta bons recursos de formatação que promovem a produtividade.
Vide imagem abaixo.

Pontos Fortes:

- Versão Freeware (a Enterprise pode ser baixada com )
- Atualização Recente (02 de 2010)
- Bom editor de Pl/ PgSQL
- Bons recursos visuais para visualização e navegação nos objetos do banco, mostrando propriedades e dependências.
- Configuração de autosubstituição de caracteres.

Pontos Fracos:

- Não apresenta funções de engenharia reversa e importação.
- Não apresenta assistentes para a criação de tabelas, índices e outros objetos do banco, que devem ser manuseados via DDL, função básica em qualquer ferramenta visual de desenvolvimento.
- Ausência de versão para Linux
- Falta de recursos mais avançados, restritos à versão entreprise.

Resultado geral:

É uma ferramenta boa para os usuários da plataforma Windows que utilizem muito código Pl/ PgSQL. As futuras versões podem trazer avanços mais significativos. Só deve ser utilizada após testes criteriosos. A versão entreprise não foi avaliada neste post.

Versão Alfa do PostgreSQL 9.0 Lançada!

2010-03-01T07:38:00.007-03:00

A versão de desenvolvimento ALFA do PostgreSQL 9.0 está disponível para downloads e testes.

Na verdade, é a quarta versão alfa, uma vez que três versões anteriores haviam sido lançadas como sendo para o PostgreSQL 8.5.

Faça o download aqui!

Veja as notas de lançamento com os avanços desta versão aqui!

Select - Cláusulas FOR UPDATE, FOR SHARE e NOWAIT

2010-02-19T08:53:00.006-03:00

Influenciar nos mecanismos de bloqueio do banco de dados nem sempre é um processo intuitivo. No entanto, pode ser bastante útil para garantia da confiabilidade de resultados e para ajustes de desempenho.

A existência de bloqueios sobre os dados acessados durante o acesso concorrente não pode ser ignorada em sistemas com grande número de transações concorrentes mesmo no Postgres que utiliza o protocolo de bloqueios multiversão (MVCC - MultiVersion Concurrency Control).

O PostgreSQL oferece algumas cláusulas relativamente simples que permitem este tipo de controle no caso de consultas no banco de dados: FOR UPDATE, FOR SHARE e NOWAIT.

O uso de consultas com a cláusula FOR UPDATE obriga o servidor a bloquear os registros consultados para leitura e escrita durante o transcorrer da transação. Desta forma se garante que o que está sendo visualizado corresponde ao que está armazenado no banco de dados.

Por sua vez, a cláusula FOR SHARE efetua bloqueio de escrita, mas permite que leituras sejam feitas aos dados consultados.

Em resumo, a cláusula FOR UPDATE restringe os acessos aos dados consultados, enquanto que a FOR SHARE explicitamente autoriza acessos de leitura aos dados consultados. O tipo de transação é que determina se e quando utilizar estas cláusulas.

É importante salientar que ambas as cláusulas se referem apenas os dados que são recuperados na consulta.

Adicionalmente, a cláusula NOWAIT pode ser utilizada tanto com FOR UPDATE quanto com FOR SHARE, e força a ocorrência de erro caso o servidor tenha de esperar para a obtenção de bloqueios nos dados consultados. Desta forma, sacrifica-se a transação para que não se perca tempo na fila de espera por bloqueios.

As cláusulas UNION, INTERSECT e EXCEPT até o momento não são compatíveis com FOR UPDATE e FOR SHARE.

Para os próximos exemplo, serão utilizadas as seguintes tabelas:

CREATE TABLE pai (codpai integer,nomepai varchar(50));
CREATE TABLE filho (codpai integer,codfilho integer,nomefilho varchar(50));

Exemplos:

1 - Sintaxe simples com FOR UPDATE.

SELECT * FROM pai FOR UPDATE;

2 - Sintaxe simples com FOR SHARE.

EXPLAIN SELECT * FROM pai FOR SHARE;

3 - Uso de FOR SHARE em consulta com junção.

SELECT *
FROM pai p, filho f
WHERE p.codpai = f.codpai
FOR SHARE;

4 - Uso de NOWAIT.

SELECT * FROM pai FOR UPDATE NOWAIT;

5 - Uso de FOR UPDATE em transação de atualização.

BEGIN;
SELECT * FROM pai FOR UPDATE;
UPDATE pai SET nomepai = nomepai ' Father';
COMMIT;

Algoritmos: Caixa de Banco Simulado no PostgreSQL

2010-01-27T15:43:00.005-03:00

Ao retirar dinheiro de um caixa eletrônico, solicitamos uma quantia e o caixa decide quantas notas de cada tipo disponível nós receberemos. O algoritmo que faz esta decisão é relativamente simples.

Abaixo coloco uma função que recebe uma solicitação de dinheiro e calcula quantas notas de 100, 50, 10, 5 e 1 serão retornadas ao solicitante:

--Retornando notas do caixa eletrônico
--Notas de 1, 5, 10, 50 e 100
CREATE OR REPLACE FUNCTION caixa_elet (pvalor integer) RETURNS text AS $$
DECLARE
sretorno text;
qnota1 integer;
qnota5 integer;
qnota10 integer;
qnota50 integer;
qnota100 integer;
BEGIN
sretorno := '';
qnota100 := (pvalor - (pvalor % 100))/100;
qnota50 := ((pvalor % 100) - (pvalor % 50)) /50;
qnota10 := ((pvalor % 50) - (pvalor % 10)) /10;
qnota5 := ((pvalor % 10) - (pvalor % 5)) /5;
qnota1 := ((pvalor % 5) - (pvalor % 1)) /1;
sretorno := 'Total: ' || pvalor || chr(10) || 'Notas de 100:' || qnota100 || chr(10) || 'Notas de 50:' || qnota50 || chr(10) || 'Notas de 10:' || qnota10 || chr(10) || 'Notas de 5:' || qnota5 || chr(10) || 'Notas de 1:' || qnota1;
RETURN sretorno; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;

Chamada da função e resultado apresentado:

SELECT caixa_elet(1078);

"Total: 1078
Notas de 100:10
Notas de 50:1
Notas de 10:2
Notas de 5:1
Notas de 1:3"

SELECT caixa_elet(2189);

"Total: 2189
Notas de 100:21
Notas de 50:1
Notas de 10:3
Notas de 5:1
Notas de 1:4"

Agora, gostaria de fazer algumas perguntas para os programadores de plantão:
- O código da função caixa_elet está correto?
- O código da função caixa_elet pode ser melhorado de que formas?
- Que alterações seriam necessárias para acrescentar notas de 20?

Aguardo suas contribuições nos comentários!

8.5 sai de cena antes de ser lançado: nova versão do Postgres será a 9.0!

2010-01-21T13:37:00.003-03:00

O anúncio não é oficial, mas a versão 8.5 terá sua numeração alterada para 9.0. Os testes e o desenvolvimento continuam normalmente.

As razões envolvendo a mudança compreendem o aumento das funcionalidades da nova versão e da abrangência das alterações no código, o grande tempo desde a primeira versão 8.* e um benefício em termos de marketing, pela insinuação de uma mudança mais substancial refletida na alteração de 8.4 para 9.0.

Você concorda com essa atitude?

PostgreSQL e JDBC: Ótimo exemplo de código!

2010-01-05T11:10:00.002-03:00

A utilização de linguagem java nas aplicações com PostgreSQL depende de um middleware para o acesso aos dados. O JDBC oferece conexões com segurança e confiabilidade. Mas como implementar estes acessos?

O link abaixo apresenta um ótimo exemplo introdutório de código java para acesso ao Postgres via JDBC. Confira aqui.

Onde esse elefante está? O que nos diz o google insights sobre o ano de 2009?

2009-12-18T15:28:00.007-03:00

As pesquisas na internet dizem muito sobre a aceitação de produtos, serviços, candidatos a cargos eletivos e sobre o uso de programas e sistemas. Com o PostgreSQL não é diferente. Fiz uma breve pesquisa sobre o ano de 2009 no google insights e os resultados compartilho neste post. O levantamento do ano passado está aqui.

As imagens capturadas e os dados foram todos coletados dia 18/12/2009.

- O ano de 2009 foi relativamente estável. Houve uma queda nas buscas com a festas de fim de ano e um aumento nas pesquisas durante o lançamento da nova versão 8.4, em meados de abril. A figura 1 mostra a evolução destas buscas no ano.

Considerando os últimos 4 anos, houve uma diminuição nas buscas. No entanto isto não significa necessariamente perda de espaço, podendo indicar também uma maior disseminação do conhecimento sobre o banco, que reduz a necessidade de pesquisas na rede.

- No mundo, Japão e Rússia crescem no ranking relativo de buscas e recuperam as primeiras posições perdidas em 2008 para Cuba e China.

- No mundo ganha destaque a busca "PostgreSQL MySQL", indício de que o PostgreSQL não é considerado uma alternativa tão natural ao Oracle ou ao Sql Server, mas uma boa opção em relação ao MySQL (Esta é apenas uma suposição que carece de mais comprovação!).

- No Brasil destaco as buscas "PostgreSQL Windows" e "PostgreSQL Linux". A pontuação destas buscas mostra que a compatibilidae com vários sistemas operacionais é um importante fator para os projetos de software nacionais.

- O Brasil continua em uma posição intermediária. Na América do Sul o destaque fica para a Bolívia, que ocupa a quarta posição entre os 10 maiores índices de busca pelo PostgreSQL no Google.

- Dentre os estados brasileiros, Distrito Federal e Ceará ocupam as duas primeiras posições. Santa Catarina, Paraná e Rio Grande do Sul estão nas três posições subsequentes, o que mostra a força do PostgreSQL na Região Sul.

Não recomendo que estes dados sejam utilizados para a tomada de decisões. São informações retrospectivas sujeitas a erros estatísticos! 2009 está consumado. Agora é trabalhar pelo 2010!

GreenSQL: O Rinoceronte Amigo do Elefante!

2009-12-17T14:36:00.003-03:00

Segurança nunca é demais e cautela e caldo de galinha não fazem mal a ninguém! No caso do PostgreSQL não é diferente, e qualquer atualização de segurança é sempre recomendada.

O GreenSQL é um firewall de código aberto que visa proteger bases de dados de ataques tipo "SQL Injection", verificando os comandos submetidos ao banco, restringindo comandos de administrador para criação e destruição de tabelas e outros objetos e impedindo a submissão de códigos maliciosos. Seu símbolo é o do Rinoceronte.

A versão mais recente agregou o suporte ao PostgreSQL ao do MySQL já existente. Espera-se que o Rinoceronte possa agir como guarda costas do elefante a partir deste lançamento! Abaixo, uma imagem da arquitetura do GreenSQL.

A licença é GPL. Teste e me diga o que achou!

Enquete aponta que PostgreSQL Crescerá com a Compra do MySQL

2009-12-10T09:42:00.004-03:00

A compra recente da Sun pela Oracle, incluindo o banco MySQL começa a trazer mudanças no mercado. Segundo enquete realizada pelo "The 451 Group", há uma tendência de redução do market share do MySQL em detrimento do PostgreSQL e do novo banco de dados MariaDB, que seria um fork livre do MySQL com um processamento de consultas novo e bastante promissor.

Este autor acha que há espaço para todos, e acredita no crescimento do PostgreSQL independentemente do apresentado pelas demais alternativas. Aproveita para lembrar que enquetes revelam apenas intenções, e que o mercado pode tomar outra direção.

O que você acha? O que tem acontecido na sua empresa?

O artigo original pode ser obtido aqui.

O Comando Table

2009-11-27T08:37:00.005-03:00

O comando TABLE é muito pouco conhecido entre os usuário do Postgres, no entanto isto não chega a ser um problema. É um comando que funciona mais como uma curiosidade do que como uma funcionalidade real. Sua função principal é economizar digitação de consultas relativas a todos os dados de uma tabela.

Consultas com a sintaxe abaixo, por exemplo:

SELECT * FROM tabela;

Poderiam ser simplificadas para:

TABLE tabela;

O ganho é apenas de tempo de digitação ou de simplificação. O plano de execução é o mesmo.

O comando TABLE pode ser utilizado no lugar de "SELECT * FROM" de diversas maneiras diferentes:

Exemplo 1:

TABLE ADDRESS; --Recupera todas as colunas e linhas da tabela ADDRESS

Exemplo 2:

TABLE ADDRESS ORDER BY postal_code; --Classifica e recupera todas as colunas e linhas da tabela ADDRESS

Exemplo 3:

TABLE ADDRESS ORDER BY postal_code DESC; --Classifica de modo decrescente e recupera todas as colunas e linhas da tabela ADDRESS

Exemplo 4:

SELECT * FROM ADDRESS
UNION ALL
TABLE ADDRESS; --Uso de TABLE com UNION

Tutorial de PostgreSQL e Hibernate

2009-11-26T14:18:00.001-03:00

Bom tutorial introdutório de PostgreSQL com Hibernate aqui.
O autor autoriza o uso mas não a redistribuição!

PostgreSQL 8.5 - Versão Alfa

2009-11-03T07:45:00.002-03:00

Como desenvolver uma ferramenta altamente poderosa? Certamente um dos passos é a atualização e renovação constante. No caso do PostgreSQL, antes que uma versão seja lançada, existe um planejamento das futuras implementações. Desta forma, sempre existe um horizonte de crescimento.

A versão 8.4 está sendo atalizada com correções e ajustes nas fuincionalidades, mas em paralelo, a nova versão 8.5 está em desenvolvimento, estando em sua versão alfa. O site para download se encontra aqui. A versão alpa 2 está disponível para download e são disponibilizadas informações sobre as novas funcionalidades para a versão 8.5!

Monitoração de Comandos com PG_STAT_STATEMENTS

2009-09-24T13:47:00.004-03:00

Na versão 8.4, foram acrescentados novos recursos de monitoramento de banco que podem ser bastante úteis para se identificar que consultas têm consumido mais tempo, retornam mais dados e quais são executadas com maior freqüência. A pg_stat_statements é uma biblioteca que monitora e coleta estas informações para o usuário.

Para monitorar o que acontece no sgbd, é necessário manter em memória uma rotina que realize essa atividade. Para colocar esta rotina em execução, deve ser alterado o arquivo de configuração, mais precisamente a variável “shared_preload_libraries”, e reiniciado o servidor. Acrescente no arquivo de configuração postgresql.conf a linha abaixo e reinicie o serviço do banco:

shared_preload_libraries = '$libdir/pg_stat_statements'

Para visualizar se a biblioteca realmente foi colocada na memória pode ser usado o comando show:

Show shared_preload_libraries

Execute algumas consultas para que o sgbd armazene valores monitorados. Serão guardados os códigos dos comandos, o número de vezes em que os mesmos foram executados e os tempos de execução.

O próximo passo é executar o script do arquivo “contrib/pg_stat_statements/pg_stat_statements.sql” que se encontra na pasta de contribs para criar uma visão que mostrará os dados monitorados chamada pg_stat_statements. Abaixo, coloco a consulta padrão aos dados de monitoramento e alguns exemplos adicionais de consultas por número de chamadas ao comando, pelo tempo total e pelo número de linhas retornado.

1 - Consulta padrão

select * from pg_stat_statements;

2 - Consultas ordenadas pelo número de chamadas

select * from pg_stat_statements order by calls desc;

3 - Consultas ordenadas pelo tempo total utilizado

select * from pg_stat_statements order by total_time desc;

4 - Consultas ordenadas pelo número de linhas retornado

select * from pg_stat_statements order by rows desc;

Caso a quantidade de dados retornados seja muito grande, utilize a função pg_stat_statements_reset para limpar os dados coletados:

1 – Limpando dados coletados

select pg_stat_statements_reset();

Participe de Concurso "O Elefante está entre nós" e Concorra a 50 Prêmios!!!

2009-08-24T08:55:00.005-03:00

A imaginação é mais importante que o conhecimento. E uma iniciativa brasileira promete estimular e recompensar a criativivade da comunidade PostgreSQL, distribuindo 50 prêmios. Apenas a participação de todos pode fazer desta boa idéia um grande sucesso! Se inscreva na PGCON e mostre seu conhecimento!

O concurso apresenta várias categorias:

Consulta ou script;
Artigo;
Artigo traduzido;
História em quadrinhos; (!!!)
Estudo de caso;

O pai da idéia é o Telles, do blog Savepoint: http://www.midstorm.org/~telles/2009/08/22/concurso-o-elefante-esta-entre-nos/

As regras de cada categoria estão disponíveis aqui!

Se esta iniciativa for bem sucedida, certamente será reproduzida mundo afora. Depende da participação de todos! Este blogueiro parabeniza a iniciativa e tentará participar de pelo menos duas categorias, dentro do espírito olímpico: o "importante é competir".

151 posts!

2009-07-29T10:45:00.002-03:00

Em abril de 2007 este blog surgiu e hoje, pouco mais de 2 anos depois, atingiu a marca de 150 posts.

É muito pouco em comparação com blogs de entretenimento e variedades, mas é muito em relação a blogs de tecnologia, especialmente se a área é banco de dados, e especificamente o banco de dados PostgreSQL. A longevidade do blog também merece destaque, pois muitos outros bons sites deixaram de existir no período, congelaram no tempo, ou mudaram de rumo, abordando outros assuntos.

A dinâmica de inovação constante e os movimentos da comunidade ajudaram a atingir esta conquista.

Deus ajudou dando saúde e disposição! E você, leitor, ajudou de muitas formas, obrigado!

Psql 8.4: Novas Funcionalidades!

2009-07-29T08:45:00.004-03:00

O psql em sua nova versão apresenta uma série de pequenas novidades, inclusive a promessa de compatibilidade com versões anteriores do Postgres, permitindo que de um console se acesse bancos de servidores mais antigos. A lista de alterações completa pode ser vista aqui. Abaixo, comento algumas das mudanças que reputo como mais significativas:

Listando o Tamanho de Objetos

Pequenas funcionalidades podem ser ainda mais interessantes para o usuário do utilitário que os avanços mais complexos. A facilidade de se obter informações detalhadas, particularmente sobre espaço ocupado por objetos em disco certamente é uma delas.

O uso da sintaxe \dt lista as tabelas criadas no banco. Na versão 8.4, pode ser utilizada a sintaxe \dt+, que retorna o espaço em disco ocupado pela tabela.

Este recurso também pode ser utilizado para índices. Digite \di para informações dos índices, e \di+ para um maior detalhamento com espaço em disco ocupado e descrição.

Para bancos de dados, a sintaxe é a mesma. Digite \l para informações dos bancos de dados, e \l+ para um maior detalhamento com espaço em disco ocupado e descrição.

Abrindo Editor para Funções

Editar funções dentro do psql não é uma tarefa muito agradável. Paras códigos mais extensos chega a ser penosa. Para minorar o problema, a opção \ef abre um editor externo para edição do código.

A sintaxe é: \ef

No windows, abre o notepad. Edite o texto, salve e feche o editor. No psql, digite ';' e tecle ENTER. O sistema vai registrar as alterações na função.

PostgreSQL 8.4 - Silêncio no Planeta Postgresql!

2009-07-29T07:55:00.002-03:00

Achei interessante o silêncio sobre a nova versão do PostgreSQL na comunidade. O site do Planeta PostgreSQL BR não teve nenhum post novo desde 08/07/2009.

21 dias sem posts? Ou todos estão muito ocupados ou faltam novidades significativas. Espero que seja a primeira opção, pois foi o que aconteceu comigo!

PostgreSQL 8.4: Falta um bom instalador para Windows!

2009-07-08T09:30:00.005-03:00

Dentre as mais de 290 mudanças da versão 8.3.x para a 8.4.0, uma importante lacuna que surgiu foi a do instalador para windows. O projeto pgInstaller, mantido por Dave Page e Magnus Hagander gerou instaladores intuitivos e poderosos, com recursos de configuração mais avançados que facilitavam a adição de contribs e a atualização de versão do SGBD em ambiente Windows. No entanto, o projeto só continuará a ser mantido nas versões 8.2.x e 8.3.x.

Para Linux, além do one-click installer e de live cds, customizações de instaladores para Fedora, SUZE, Ubuntu e Debian estão disponíveis para o PostgreSQL 8.4.

Mais informações aqui!

Espera-se que sujam mais opções de instaladores além do limitado "One-Click Installer" da página oficial do PostgreSQL. Quem se habilita a fazer um?

Creio que o impacto para a difusão do PostgreSQL 8.4 pode ser significativo na plataforma Windows. Não podemos subestimar o impacto da facilidade de instalação para a adoção e atualização de qualquer ferramenta. O que vocês acham?

Complemento 13/08/2009:

- Depois de instalar em algumas máquinas, posso afirmar que o one-click installer é bastante funcional e estável. No entanto, continuo a me sentir incomodado por ter menos uma opção de instalação. E o desafio continua a quem se dispor a fazer um instalador alternativo!!!

PostgreSQL 8.4 Enfim Liberado!

2009-07-01T15:50:00.002-03:00

O download do PostgreSQL 8.4 (8.4.0) foi enfim liberado!

A nova versão promete mais velocidade e facilidade de uso, melhorias no monitoramento e administração. É instalar e testar para ver! Em breve detalharei neste espaço as principais novidades deste aguardado release!

Abaixo, os links mais úteis:

* Download
http://www.postgresql.org/download/

* Notas de Lançamento
http://www.postgresql.org/docs/8.4/static/release-8-4.html

* Funções implementadas na versão
http://www.postgresql.org/about/press/features84.html

* Press Release
http://www.postgresql.org/about/press/presskit84.html

PostgreSQL 8.4: Release Candidate Liberada!

2009-06-16T13:11:00.003-03:00

Depois do teste de softwares em versões beta, a versão Release Candidate é a mais próxima da versão oficial. A poucos minutos a versão RC do PostgreSQL 8.4 foi liberada para download aqui!

Lembro que esta não é uma versão definitiva e não deve ser utilizada em sistemas de produção, mas pode ser utilizada, por exemplo, para o desenvolvimento de produtos enquanto a oficial não chega de fato. Teste e compartilhe suas primeiras impressões!

Enquete: Que Prioridades o Desenvolvimento do Postgres deve Assumir?

2009-06-15T07:45:00.004-03:00

Que Prioridades o Desenvolvimento do Postgres deve Assumir? Você já pensou nisso?

Essa enquete (What's your highest priority for PostgreSQL development?) está disponível no site da comunidade internacional do PostgreSQL. Se você tem alguma prioridade que não está na lista, podes coloca-l a como comentário.

Fazer acesso à enquete!

Números Aleatórios com o PostgreSQL: A Função RANDOM()

2009-06-10T12:46:00.006-03:00

Gerar números aleatórios é uma necessidade importante para a geração de grandes bases de dados de teste. Para maior veracidade nos testes, uma certa aleatoriedade é esperada deste tipo de base de dados. Aí entram em cena as rotinas de geração de números aleatórios.

A função RANDOM() é o método nativo do PostgreSQL para a geração de seqüências de números aleatórios. Neste post vamos apresentar exemplos práticos do uso desta função que podem ser úteis no cotidiano.

Ao se executar a função RANDOM(), é retornado um número aleatório entre zero e 1 com muitas casas decimais. Dependendo da necessidade, pode-se gerar um número positivo, um valor dentro de um determinado determinado intervalo. Valores e intervalos podem assumir valores nulos e negativos com pequenas variações de sintaxe.

* Sintaxe

1 - A sintaxe básica.

SELECT random();

Retorna: 0.896639783866704, 0.516120770014822...

2 - Busca de números com um valor dentro de um intervalo começado com zero é um pouco mais complicada. O exemplo abaixo retorna um valor entre 0 e 99. Para aumentar ou diminuir o intervalo, trocar o número 100 por outro valor. Ajustar as casas decimais do comando CAST se desejar valores fracionários. A função ROUND() elimina decimais indesejadas.

SELECT round(CAST (random()*100 AS NUMERIC),0);

3 - Busca de números com um valor dentro de um intervalo qualquer demanda atenção. O exemplo abaixo retorna um valor entre 27 e 90. Para aumentar ou diminuir o intervalo, trocar os números 27 e 91 por outros valores. Ajustar as casas decimais do comando CAST se desejar valores fracionários.

SELECT 27 + round(CAST (random()*(91-27) AS NUMERIC),0);

4 - Busca de números com um valor dentro de um intervalo começado com valor negativo. Bastante similar ao exemplo anterior.

SELECT -57 + round(CAST (random()*(91+57) AS NUMERIC),0);

Geração de CPFs Fictícios com Pl/ PgSQL

2009-06-03T13:11:00.004-03:00

A geração de massas de dados para teste é um processo muito importante, para verificar o comportamento de um banco de dados em relação à carga de processamento que o mesmo tem de desempenhar. A geração de CPFs fictícios é uma atividade relativamente comum em empresas brasileiras que armazenam este tipo de identificador. No entanto muitas vezes os testes são feitos com valores incorretos, isto é, que não respeitam as regras para os dois dígitos verificadores.

Abaixo está um código que se propõe a retornar CPFs aleatórios com dígitos verificadores corretos, implementado em Pl/ PgSQL.

Os comandos mais importantes deste código são:
- Random() - Geração de números aleatórios
- substring() - Extração de parte de uma string com base nso parâmetros fornecidos
- CAST () - Conversão de tipos no PostgreSQL
- trim() - Eliminação de espaços em branco de strings

Os testes foram muito positivos. Coloco para você, leitor, as seguintes perguntas:
- Este código segue um algoritmo correto?
- Ele pode ser melhorado? De que forma?

CREATE OR REPLACE FUNCTION gerar_CPF() RETURNS varchar AS $$
-- ROTINA DE GERAÇÃO DE CPF SEM LOOP
-- Retorna string com CPF aletório correto.
DECLARE
vet_cpf integer [11]; --Recebe o CPF
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[1] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[2] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[3] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[4] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[5] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[6] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[7] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[8] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);

-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
vet_cpf[9]:=0;
ELSE vet_cpf[9]:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:= (vet_cpf[0]*11) + (vet_cpf[1]*10) + (vet_cpf[2]*9) + (vet_cpf[3]*8) + (vet_cpf[4]*7) + (vet_cpf[5]*6) + (vet_cpf[6]*5) + (vet_cpf[7]*4) + (vet_cpf[8]*3) + (vet_cpf[9]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
vet_cpf[10] := 0;
ELSE
vet_cpf[10] := (11-rest);
END IF;
--Retorno do CPF
RETURN trim(trim(to_char(vet_cpf[0],'9')) || trim(to_char(vet_cpf[1],'9')) || trim(to_char(vet_cpf[2],'9')) || trim(to_char(vet_cpf[3],'9')) || trim(to_char(vet_cpf[4],'9')) || trim(to_char(vet_cpf[5],'9')) || trim(to_char(vet_cpf[6],'9')) || trim(to_char(vet_cpf[7],'9'))|| trim(to_char(vet_cpf[8],'9')) || trim(to_char(vet_cpf[9],'9')) || trim(to_char(vet_cpf[10],'9')));
END;
$$ LANGUAGE PLPGSQL;

Chamada da função, retornando um CPF aleatório.

select gerar_CPF() ;

A função para fazer o teste está neste post. O resultado para um CPF correto é 1.

select CPF_Validar_Sem_Loop(gerar_CPF());
select CPF_Validar_Sem_Loop('66067526557'); --Gerado pelo programa

PostgreSQL no IV Festival Software Livre da Bahia

2009-05-13T08:37:00.004-03:00

Eventos de software livre ganham maior relevância, à medida em que se consolidam. O III ENSL - Encontro Nordestino de Software Livre e o IV Festival Software Livre da Bahia, que antes eram iniciativas distintas, se fundiram em um único evento que será realizado nos dias 29 e 30 de maio de 2009, no campus da Universidade Estadual da Bahia (UNEB), em Salvador.

A programação é vasta e eclética e abrange cinco grandes áreas: Cultura Digital Livre; Casos de Sucesso; Ferramentas e Soluções; Desenvolvimento de Software e Educação e Inclusão Digital.

O PostgreSQL será discutido na palestra "Tuning de Banco de Dados Livre: O Caso do PostgreSQL", ministrada por mim.

A programação detalhada e as inscrições estão disponíveis até 24/05 no site do evento!

A Oracle comprou a SUN e conseqüentemente o Java e o MySQL. E como fica o PostgreSQL?

2009-04-28T16:55:00.007-03:00

A Oracle comprou a SUN e conseqüentemente o Java e o MySQL. Mais informações podem ser obtidas aqui. Que impactos esta aquisição causará sobre o PostgreSQL?

Ainda é muito cedo para ter certezas sobre este tema, mas ele certamente é bastante crítico para a área de banco de dados. A Oracle adquiriu nos últimos anos uma série de empresas e seus softwares de banco, dentre elas o Berkeley DB, que passaram a figurar entre as soluções desta empresa. As novas aquisições continuarão a ser livres e a serem aprimoradas? O suporte do java ao PostgreSQL estará ameaçado? O espaço para o PostgreSQL diminuirá ou será ampliado? Estas e outras questões permanecem em aberto.

Baseado nestas dúvidas tive a idéia de criar um tópico aberto. Peço que coloquem opiniões e links para reportagens sobre o impacto desta aquisição para a comunidade de banco de dados e particularmente para os usuários do PostgreSQL.

Atualização (13/05/2009): anexo link para posts sobre o assunto no site do Fernando Ike e do Telles.

Versão 8.4: primeira release beta disponível!

2009-04-16T08:40:00.002-03:00

O grupo de desenvolvimento do PostgreSQL anunciou que a primeira versão 8.4 BETA está disponível para download. A colaboração da comunidade nos testes é de vital importância para o diagnóstico e correção de quaisquer problemas na ferramenta. Esta versão não deve ser utilizada para aplicações corporativas, por ser menos estável que as releases padrão.

Testemos, critiquemos e participemos!!!

Mais informações em: http://www.postgresql.org/developer/beta.

Em breve apresentaremos um detalhamento das novas funcionalidades da versão 8.4 neste espaço.

Fique Atualizado em PostgreSQL com o Addict-o-matic!!!

2009-04-08T15:00:00.004-03:00

Ficar atualizado em tecnologias de hardware e software não é das tarefas mais fáceis. O site Addict-o-matic faz a coleta de uma série de notícias, posts em blogs, vídeos e imagens em uma série de fontes na rede. O resultado de uma pesquisa sobre o PostgreSQL não decepcionou.

Clique aqui para experimentar! Podem ser fornecidas várias opções de pesquisa, e o site funciona bem como agregador de informações.

A dica do site foi do blog da colunista Sandra Carvalho.

Ano Bissexto no PostgreSQL!

2009-04-03T18:06:00.002-03:00

Às vezes o mais difícil de fazer são coisas realmente simples. Os exemplo de código abaixo são implementações de uma rotina que calcula se um ano é bissexto ou não.

Segundo a Wikipedia, as regras do ano bissexto são poucas, mas não são triviais:
* São bissextos todos os anos múltiplos de 400, p.ex: 1600, 2000, 2400, 2800
* Não são bissextos todos os múltiplos de 100 e não de 400, p.ex: 1700, 1800, 1900, 2100, 2200, 2300, 2500...
* São bissextos todos os múltiplos de 4 e não múltiplos de 100, p.ex: 1996, 2004, 2008, 2012, 2016...
* Não são bissextos todos os demais anos.

Com base no que é apresentado neste post, peço que você que me responda três perguntas:
- Os dois exemplos abaixo estão corretos?
- Qual dos dois apresentaria alguma vantagem em relação ao outro? Ou são ambos equivalentes?
- É possível melhorar as implementações? De que formas?

As duas funções criadas retornam 1 se ano for bissexto, 0 se não for e 99 se for anterior a 1582, para anos acima de 1582.

Exemplo 1:

CREATE OR REPLACE FUNCTION ano_bissexto (pAno integer) RETURNS integer AS $$
DECLARE
ret integer;
BEGIN
IF $1<1582 THEN RETURN 99; END IF;
ret :=0; --Inicialização
IF ($1%400=0) THEN
ret:=1; /*Bissexto*/
ELSE
IF ($1%4=0) AND ($1%100<>0) THEN
ret:=1; /*Bissexto*/
END IF;
END IF;
RETURN ret;
END;
$$ LANGUAGE plpgsql;

SELECT ano_bissexto(1600);

Exemplo 2:

CREATE OR REPLACE FUNCTION ano_bissexto_menor (pAno integer) RETURNS integer AS $$
DECLARE
ret integer;
BEGIN
IF $1<1582 THEN RETURN 99; END IF;
ret :=$1; --Inicialização
IF (ret%100=0) THEN
ret:=ret/100;
END IF;
IF ret%4=0 THEN
RETURN 1; --Bissexto
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

SELECT ano_bissexto_menor(1600);

Enquete Encerrada: PostgreSQL é um Ótimo Nome para Banco de Dados!

2009-04-02T16:30:00.006-03:00

A enquete que perguntava a respeito do nome do PostgreSQL foi encerrada, e foi considerado que o nome tradicional deve ser mantido por uma maioria esmagadora. Os nomes alternativos sugeridos não tiveram aceitação e a opção de outros nomes não foi acolhida. Abaixo, os dados:

Opção	Votos	Percentual
Deve ser mantido.	152	69,72
Que tal PgDB?	15	6,88
Postgree seria melhor	35	16,06
Outro	9	4,13
Não sei.	7	3,21
Totais	218	100

Concorda com o resultado? Discorda? Deixe seu comentário!

Log com Arquivos CSV

2009-03-27T14:44:00.004-03:00

Arquivos texto são muito utilizados em sistemas de informação. No entanto, sua manipulação automática muitas vezes é difícil por serem meios não estruturados de armazenamento de informações. O uso de arquivos CSV (Comma-Separated-Values - arquivos com valores separados por vírgula) facilita a utilização de arquivos texto para o armazenamento de dados. Um dos usos de arquivos CSV é no armazenamento do log de bancos de dados PostgreSQL, que passa a ser mais facilmente tratado por meio de planilhas eletrônicas, que apresentam os itens do log de forma tabular.

A utilização de log em arquivo CSV é muito simples, bastando se alterar o arquivo POSTGRESQL.CONF, alterando o parâmetro log_destination, e fazer o reload da configuração. Comente o valor anterior do parâmetro e coloque esta linha:

log_destination = 'csvlog'

A partir deste momento os arquivos de log criados serão do tipo CSV. Abaixo, uma imagem ilustrando como fica armazenado fisicamente o log:

Que linguagem você utiliza com o PostgreSQL?

2009-03-16T07:06:00.002-03:00

Que linguagem você utiliza com o PostgreSQL? Esta é a enquete atual do site do PostgreSQL. Participe!

www.postgresql.org/community

Possíveis Funcionalidades das Versões Futuras do PostgreSQL

2009-03-11T12:46:00.003-03:00

Pouco se sabe ainda das novas funcionalidades a serem implementadas na versão 8.4 do PostgreSQL. Recentemente, uma apresentação recente de Bruce Momjian apresentou uma descrição de algumas das principais alterações em implementação. As funcionalidades prometem melhor desempenho e facilidade em certos tipos de consulta.

Algumas das principais alterações da versão 8.4 seriam:

- Column-Level Permissions - Permissões de acesso em por coluna.
- Windowing Functions: Sum and Rank - Ranqueamento de registros e somatório em uma sintaxe simples.
- WITH Queries: Simple and Recursive - sintaxe alternativa para a realização de consultas, inclusive permitindo recursividade.
- Parallel Restore of Dumps - Uso de threads para ganhar paralelismo na restauração de backups. Deve depender do sistema operacjional para funcionar.
- Visibility Maps Reduce Vacuum Overhead - Aprimoramento do desempenho das rotinas de Vacuum.
- No Need for Free-Space Map Configuration - Remoção de configurações dos arquivos de configuração.
- Default Values for Function Arguments - Funções com valores DEFAULT para retorno.
- Outras alterações, principalmente voltadas para o desempenho.

Confira as alterações segundo o texto original aqui.

Esta informação foi obtida no blog do Guedes.

Documentando Objetos no PostgreSQL

2009-02-13T09:47:00.005-03:00

Onde guardar a documentação sobre os objetos do seu banco de dados? A importância a e grande quantidade de informação sobre tabelas, seus campos, índices e outros objetos faz com que essa questão ganhe importância. Um bom lugar para fazer esse registro é justamente o próprio banco de dados. No PostgreSQL é possível se comentar os objetos armazenados no banco, atualizar, remover e consultar essas informações, através do comando COMMENT.

O comando COMMENT armazena um comentário textual associado a um objeto do banco de dados. Sua sintaxe é relativamente simples:

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'

A grande lista de tipos de objetos para os quais o comando pode criar comentários é bastante abrangente. Compreende tabelas, campos de tabelas, índices, tablespaces, esquemas, databases, tipos, funções, etc. Os comentários são armazenados em campos TEXT, que permitem documentações largamente detalhadas.

Os comentários são consultados por meio do utilitário PSQL e de algumas funções disponibilizadas pelo PostgreSQL (obj_description, col_description, and shobj_description).

Exemplos de Código - Criação de Comentário

1 - Criação de Objetos do Tipo Tabela.

create table starttest (col int);
create table starttest2 (col int);

2 - Definição de Comentários

COMMENT ON TABLE starttest IS 'Tabela do sistema SITP';
COMMENT ON TABLE starttest2 IS 'SITP - Modulo II';

3 - Remoção de Comentários

COMMENT ON TABLE starttest IS NULL;

Exemplos de Código - Consulta de Comentários

1 - Recuperação de OID de objeto em PG_CLASS.

SELECT relname, relfilenode
FROM PG_CLASS
WHERE relname LIKE 'starttest%';

2 - Consulta dos commentários associados pelo OID.

SELECT obj_description(17173);

3 - Consulta dos comentários de todos os objetos de PG_CLASS.

SELECT obj_description(p.relfilenode), * from pg_class p;

4 - Consulta dos comentários de todos os objetos de PG_TYPE.

SELECT obj_description(p.typrelid), * from PG_TYPE p;

Utilizaçao Prática

O uso do comando COMMENT para documentar objetos do banco é pouco difundido em parte pelo fato do mesmo ser pouco conhecido, não apresentar similares em alguns SGBDs e por não ser gerado por muitas das ferramentas do mercado, que armazenam estas informações em formato próprio, sem transferi-las ao SGBD.

Também existem questões de segurança, pois um usuário malicioso que tenha acesso ao banco pode recuperar os comentários sobre os seus objetos. No entanto, o interesse real dos invasores de bancos de dados se refere às informações armazenadas, e não à documentação dos bancos de dados.

Deve ser evitado armazenar qualquer informação crítica para a segurança com o comando COMMENT, tais como senhas, informações sobre criptografia e política de acesso utilizados, uma vez que não existem restrições de segurança para os comentários armazenados, isto é, qualquer usuário criado no SGBD visualiza todas as informações.

Os comentários são exportados ao se fazer um backup do banco com os utilitários do PostgreSQL.

Configuração Básica de Driver ODBC para PostgreSQL

2009-01-28T11:18:00.009-03:00

Acesso a dados demanda um bom middleware. E a tecnologia ODBC - Open Database Connectivity, é uma opção bastante estável, disponível desde 1992. Por apresentar drivers para praticamente todos os bons SGBDs do mercado, muitas soluções são implementadas utilizando esta tecnologia.

O suporte às funcionalidades de um SGBD relacional e a grande disponibilidade de drivers são grandes pontos fortes deste middleware. Como problemas podem ser elencadas a falta de atualizações recentes, com defasagem em termos de novas funcionalidades oferecidas por outros componentes de acesso a dados e o fato de ser uma tecnologia proprietária.

Longe de esgotar este tópico, este post mostra uma configuração básica de ODBC para PostgreSQL, como a que foi utilizada para a utilização da ferramenta DbDesigner Fork.

* Etapa 1- Instalação
É sempre recomendável baixar a versão mais atualizada disponível, no site da comunidade PostgreSQL.ORG: http://www.postgresql.org/ftp/odbc/versions/

No nosso caso, a instalação será feita em uma máquina com Windows XP. Existem formas de utilizar o ODBC no Linux e no UNIX e meios de se fazer pontes envolvendo ODBC e JDBC, mas estas variações estão (bastante) fora do escopo deste post.

Descompacte o arquivo ZIP e execute o instalador. É sempre aconselhável ler antes o arquivo readme e se for uma atualização, pode ser disparado o arquivo upgrade.bat. Na instalação padrão um assistente relativamente simples se encarregará do processo sem que se precise fazer configurações complicadas.

* Etapa 2 - Configuração

Após a instalação, abra o painel de controle do windows (Menu Iniciar/ Painel de Controle). Dispare a opção "Ferramentas administrativas" e depois selecione "Fontes de Dados (ODBC)".

Será aberta a tela "Administrador de Fonte de Dados ODBC". Nesta tela, selecione a aba "Drivers ODBC que estão instalados no sistema". Observe na imagem abaixo que foram instalados os drivers para PostgreSQL ANSI e UNICODE.

Para criar um data source, selecione a aba "Fonte de dados do usuário" e o botão "Adicionar". Aparecerá uma tela como a que está abaixo. Selecione o driver do PostgreSQL e confirme.

Em seguida, preencha os valores para o nome da fonte (Description), banco de dados para o qual aponta (Database), servidor em que está o banco de dados (Server), Porta de Comunicação (Port), usuário e senha. O servidor pode assumir os valores "localhost" ou um endereço IP. Utilize o botão TEST para verificar se os dados foram devidamente fornecidos e se a conexão pode ser feita e a opção SAVE para gravar a configuração do driver.

A opção "Configurar" permite a alteração de uma fonte de dados do usuário, abrindo a tela abaixo. Os botões Datasource, Global e Manage DSN estão fora do escopo deste post.

Após fazer a instalação, a nova fonte de dados do usuário aparece na lista, podendo ser utilizada por várias aplicações compatíveis com o ODBC.

Mais informações sobre ODBC podem ser obtidas nestas fontes:
- Na Wikipedia
- Site Easysoft, com informações sobre ODBC em UNIX e Linux

Power Architect - Ótima Ferramenta Livre!!!

2009-01-14T12:28:00.007-03:00

Falar de ferramentas de banco de dados geralmente implica em destacar suas limitações. Uma exceção é o Power Architect, que apresenta boas e úteis funcionalidades para o desenvolvimento de aplicações que tenham um certo grau de complexidade. É uma ferramenta livre e de código aberto, cujo download pode ser feito aqui.

Não é exclusiva para o PostgreSQL, podendo também ser utilizada em projetos com Oracle, SQL Server, MySQL, Derby e HSQLDB. O desenvolvimento é pago por taxa de suporte premium de 199 dólares ao ano, o que é relativamente baixo em se falando de ferramentas de base de dados.

As principais funcionalidades são:

Boa interface visual com diagramas de ER e OLAP
Acesso a bancos de dados via JDBC
Conexão a múltiplas fntes de dados ao mesmo tempo
Comparação de modelos de dados com identificação de diferenças e geração de scripts de sincronização
Suporte a "Drag-and-drop" de tabelas de um modelo para o playpen (diagrama)
Geração de banco de dados através do projeto (Forward-engineers)
Engenharia reversa para a construção dos projetos
Salvamento da estrutura de dados em um projeto que pode ser trabalhado remotemante
Dados do projeto armazenados em um formato XML de fácil navegação
Licença livre GPL (version 3)
Suporte rudimentar a ETL - Extração, Transformação e Carga de Dados
Suporte a esquemas OLAP para projeto de Data Warehouses

Alguns problemas ou desvantagens foram constatados. Falta suporte a visões, triggers, restrições tipo check e stored procedures, recursos importantes para o projeto de bancos de dados de maior complexidade. Outros problemas menores constatados foram: falta Suporte a trabalho em grupos, ausência de Controle de versões e falta de interface WEB.

Abaixo, algumas telas capturadas:

Tela de conexão a banco de dados PostgreSQL, tendo ao fundo uma visão geral da ferramenta.

Parâmetros para a comparação entre bancos de dados

Tela de resultado da comparação entre bancos de dados.

* Avaliação Geral

A ferramenta é superior ao DBDesigner e pode ser utilizada para projetos que não sejam de pequeno porte. Apresentou poucos bugs nos testes realizados. No entanto, as desvantagens apesar de poucas são significativas.

PostgreSQL: primeira release de 2009!!!

2009-01-12T12:17:00.002-03:00

A equipe do PostgreSQL lançou a primeira release de 2009, apresentando uma versão instável que não deve ser utilizada para ambientes de produção, que consiste basicamente em uma visão snapshot das atualizações feitas no desenvolvimento do banco. O instalador pode ser baixado aqui. A documentação desta versão está disponível aqui, mas não conta por exemplo com uma seção "release notes" apesar de apreentar no título referência à versão 8.4.

É uma boa opção para desenvolvedores e testadores que querem se antecipar às novas funcionalidades da versão 8.4 que estão em desenvolvimento ou colaborar nos testes.

Acredita-se que no primeiro semestre serão lançadas as primeiras versões de teste para a nova release 8.4 deste SGBD.

Boas Festas!!!

2008-12-23T13:32:00.003-03:00

Não sou o primeiro, nem quero ser o último a desejar a você boas festas e um abençoado 2009!!!

DbDesigner Fork: Fraco, mas Gratuito!

2008-12-23T12:33:00.010-03:00

Ferramentas para projeto de banco de dados estão entre as mais úteis para o desenvolvimento de sistemas. Infelizmente, também estão entre as mais difíceis de se encontrar dentre os softwares gratuitos. O DbDesigner Fork é uma boa opção livre para a modelagem de bancos de dados, apresentando suporte para Oracle, SQL Server, MySql e Firebird, além do PostgreSQL.

O projeto está disponível para Windows e Linux e sua última versão está datada de julho de 2007. Originou-se do DBDesigner, uma ferramenta mais antiga. Seu desenvolvimento apresenta alguma taxa de atividade e possivelmente será feito algum novo release em breve. Gera arquivos XML com a modelagem realizada em uma boa interface visual.

Apresenta uma série de recursos bastante úteis:
- Engenharia Reversa
- Sincronização
- Modelagem visual de diagramas de Entidade-Relacionamento, com integridade referencial
- Recursos para exportação SQL
- Importação da ferramenta ErWin
- Exportação dos diagramas como figuras (PNG e BMP)

DbDesigner e PostgreSQL

Nesta análise me concentrarei basicamente nas características específicas que o software tem em relação com o o PostgreSQL. A ferramenta apresentou um resultado razoável, pois foram constatados bugs e limitações. O ponto forte, além da gratuidade, foi a interface amigável, apesar de não tão moderna.

A conexão com o banco foi dificultada pelo fato da ferramenta só aceitar conexão ODBC com o PostgreSQL. Tive de baixar e instalar o driver ODBC, além de criar um data source no sistema operacional.

A interface visual é simples e a seleção da visualização em query mode permitiu a realização de consultas ao SGBD sem sair da ferramenta.

A engenharia reversa funcionou muito bem. Puxou os dados do PostgreSQL rapidamente e criou objetos no diagrama corrente. No entanto, não importou as chaves estrangeiras. Quando realizada mais de uma vez, criou mais de uma tabela com o mesmo nome no diagrama.

A sincronização de banco de dados simplesmente não funcionou. Fiquei em dúvida se a mensagem de erro apresentada foi em virtude de bug da ferramenta ou da versão do driver ODBC utilizada. Abaixo, a tela de sincronização:

Na exportação de dados, uma decepção: não há ligação direta para o PostgreSQL. O DbDesigner exporta um script SQL que deve ser submetido ao banco.

Avaliação geral

Creio que houve uma série de avanços em relação às versões anteriores da ferramenta, mas ainda está distante de ser utilizável sem ressalvas, especialmente nas grandes empresas. Abaixo listo algumas das limitações da ferramenta:

- Falta Suporte a trabalho em grupos.
- Ausência de Controle de versões.
- Grande espaçamento de tempo entre as atualizações de versão.
- Número grande de bugs e de requested features por atender.
- Falta de interface WEB

Observações

Aconselho a quem deseja utilizar esta ferramenta para suas aplicações que preserve o modelo gerado, que é criado em formato XML, em uma ferramenta de controle de versões. Em releases anteriores do DbDesigner cheguei a perder algumas horas de trabalho por corrupção do arquivo.

Retornando registros de consultas genéricas com RETURN QUERY

2008-12-05T11:30:00.006-03:00

Recentemente, recebi uma dúvida sobre o RETURN QUERY, funcionalidade implementada na versão 8.3 do PostgreSQL, que permite o retorno de conjuntos de linhas e colunas de uma função. A dúvida do Patrick é bastante comum e até pouco tempo eu não teria uma resposta adequada:

"Patrick Espake disse...

Eu estou tentando usar isso, para retorna uma linha que vem de um inner join de diversas tabelas.

O que eu coloco para o RETURNS SETOF? Visto que os meus dados vem de uma junção de diversas tabelas.

Obrigado."

A solução do problema do Patrick é relativamente simples. O Return Query aceita o tipo RECORD como valor de retorno. Portanto, fica fácil retornar resultados de consultas genéricas. Para ilustrar, segue um exemplo de código Pl/ PgSQL abaixo.

A tabela indivíduo será utilizada para o teste do RETURN QUERY retornando uma consulta qualquer.

CREATE TABLE individuo (
cod integer PRIMARY KEY,
nome varchar(50));
INSERT INTO individuo VALUES (1,'Teste');
INSERT INTO individuo VALUES (2,'Teste 2');

Agora, a função RET_ROWS mostra como utilizar o retorno de tipos RECORD para funções:

--Retornando consulta de várias linhas
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT * FROM individuo; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;

A grande diferença desta forma de programar está na chamada da função. Ao utilizar valor de retorno do tipo RECORD, deve ser indicada a lista de campos a serem retornados e seus tipos. Esta pode ser vista como uma limitação, embora na prática também seja uma forma de se garantir a confiabilidade dos resultados retornados na execução de comandos SQL dentro de funções pela validação dos valores retornados.

SELECT * from ret_rows() as (c1 integer, c2 varchar(50)) ; --Declaração dos campos do record

Falta considerar a execução de uma junção dentro da função, que é nossa questão original. Para tanto, será criada uma tabela com os filhos dos indivíduos para sofrer junção com a de indivíduos. O exemplo abaixo executou sem qualquer problema na versão 8.3:

CREATE TABLE filhos (
cod integer PRIMARY KEY,
filhos integer);
INSERT INTO filhos VALUES (1,1);
INSERT INTO filhos VALUES (2,3);

--Retornando Resultado de Junção
CREATE OR REPLACE FUNCTION ret_filhos (psql integer) RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT individuo.nome, filhos.filhos FROM individuo, filhos WHERE individuo.cod = filhos.cod AND individuo.cod = $1; -- Consulta
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;

SELECT * from ret_filhos(1) as (c1 varchar(50), c2 integer) ; --Declaração dos campos de retorno
SELECT * from ret_filhos(2) as (c1 varchar(50), c2 integer) ;

Outras Soluções

Outras soluções para o retorno de registros em funções são a consolidação e retorno dos dados dentro de um campo texto ou XML.

Resolveu, Patrick?

PostgreSQL: Tendências pelo Mundo e no Brasil!

2008-11-28T11:04:00.007-03:00

Você já se perguntou que países mais utilizam o PostgreSQL? Em que colocação ficaria o Brasil neste ranking? As buscas sobre o PostgreSQL estão aumentando ou diminuindo? Este tipo de pergunta é difícil de responder em virtude da facilidade de instalação e difusão do software. Estatísticas confiáveis são difíceis de conseguir e as que são disponíveis são certamente sujeitas a erros substanciais. Por exemplo, o fato de termos o PostgreSQL instalado, não significa que esteja sendo realmente executado.

No site do Google Insights podem ser buscadas informações relativas a buscas de termos em geral. A busca por PostgreSQL revelou interessantes idéias. Confira por conta própria aqui.

Algumas curiosidades:

- A procura por PostgreSQL tem diminuído nos últimos 4 anos. Este dado não significa uma diminuição da base instalada, uma vez que o que é medido é a quantidade de buscas a respeito.

- Se em 2004, Japão e Rússia eram os países com maior interesse, Cuba e China hoje ocupam esta posição. Abaixo, uma visão do dia de hoje (28/11/2008)

- O Brasil ainda ocupa uma posição intermediária em buscas na internet sobre o tema. O Paraguai é o país da América do Sul que mais pesquisa sobre o PostgreSQL.

- Em 2008 a quantidade de buscas está estável, com queda à medida que se aproxima o fim do ano.

- Dentre os estados brasileiros, temos como grandes buscadores o Distrito Federal, o meu Ceará e Santa Catarina. A região Norte em Geral e alguns estados apresentaram um baixíssimo índice de buscas sobre o PostgreSQL, enquanto que a Região Sul é a que apresentou uma maior difusão, com todos os estados apresentando bom índice de buscas. Estes dados apresentam uma forte influência da concentração dos profissionais de informática no país.

Funções de Suporte a Tipos ENUM (8.3)

2008-11-28T09:31:00.002-03:00

O PostgreSQL 8.3 disponibiliza algumas funções que permitem a consulta a campos ENUM com pouquíssimo esforço. As funções implementadas são: enum_first, enum_last e enum_range.

Para exemplificar sua utilização, vamos criar o tipo enum signos:

CREATE TYPE signos AS ENUM ('Áries', 'Touro', 'Gêmeos', 'Câncer', 'Leão', 'Virgem', 'Libra',
'Escorpião', 'Sagitário', 'Capricórnio', 'Aquário', 'Peixes');

* enum_first

1- Primeiro elemento de um tipo ENUM

SELECT enum_first(null::signos);

* enum_last

1- Último elemento de um tipo ENUM

SELECT enum_last(null::signos);

* enum_range - Intervalos de elementos de um tipo ENUM.

1 - Retornando todos os valores em ordem

SELECT enum_range(null::signos);

2 - Retorna valores até o ENUM Virgem, incluindo o 'Virgem'

SELECT enum_range(null, 'Virgem'::signos);

3 - Retorna valores a partir do ENUM Virgem, incluindo o 'Virgem'

SELECT enum_range('Virgem'::signos, null);

4 - Retorna valores do intervalo entre Touro e Virgem, incluindo os dois itens.

SELECT enum_range('Gêmeos'::signos,'Virgem'::signos);

Campos Tipo ENUM no PostgreSQL 8.3

2008-11-27T10:57:00.004-03:00

Campos com enumerações de valores aceitos são implementados em vários bons bancos de dados. No caso do PostgreSQL, este recurso poderia ser realizado através de constraints tipo CHECK ou domínios. Na versão 8.3 foi introduzido um tipo de dado ENUM, facilitando ainda mais a operação de campos com uma lista restrita de valores permitidos.

Vantagens:
- Cria listas de valores aceitos que podem ser reutilizadas várias vezes, inclusive dentro de uma mesma tabela, para disciplinar os valores aceitos por um determinado campo.
- Apresenta funções específicas para manipulação dos tipos ENUM criados.
- Melhor uso de espaço: o armazenamento campos Enum ocupam 4 bytes em disco. Dependendo da quantidade de valores armezenados, pode ser economizado um bom espaço no banco de dados.
- Podem ser visualizados através de consultas a pg_enum.

Desvantagens:
- O tipo ENUM é diferente dos demais tipos do PostgreSQL, portanto não pode ser comparado com valores de outros tipos. Na ausência de CAST, empregue um operador customizado ou uma função que realize esta conversão.
- Falta de recursos para alterações no tipo. Não é possível simplesmente inserir ou retirar valores de um tipo, mas os valores de campos de tabelas tipo ENUM podem ser livremente alterados.
- Enumerados que não sejam de valores alfanuméricios não podem ser criados. Para enumerados de datas e números deve ser empregada uma restrição do tipo CHECK, por exemplo, ou outra maneira de simular o ENUM.

Sintaxe:

CREATE TYPE name AS ENUM
   ( 'label' [, ... ] )

Exemplos:

1 - Criação e Utilização de Tipo Enumerado

CREATE TYPE dia_sem AS ENUM ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab');

--Utilização na criação de tabelas
CREATE TABLE registro (
dia_coleta dia_sem,
dia_analise dia_sem,
dia_resultado dia_sem);

INSERT INTO registro VALUES ('Seg', 'Ter', 'Qua');
INSERT INTO registro VALUES ('Seg', 'Ter', 'Dom');
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuW'); --ERRO - Não encontrado no tipo ENUM
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuA'); --ERRO - Case sentitive

2 - Implementação similar com a cláusula check

CREATE TABLE registro_teste (
dia_coleta text check (dia_coleta in ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab')));

3 - Criação de Tipo Enumerado II

CREATE TYPE situacao AS ENUM ('Em elaboração', 'Proposto',
'Em homologação', 'Aprovado', 'Recusado', 'Cancelado', 'Efetivado');

--Utilização na criação de tabelas
CREATE TABLE documento (
descricao varchar(50) PRIMARY KEY,
estado situacao);
INSERT INTO documento VALUES ('Ata de reunião de 12/12/2008', 'Aprovado');
INSERT INTO documento VALUES ('Levantamento do Protótipo', 'Cancelado');

4 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Tipo e Valor)

SELECT * from pg_enum;

5 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Valor, do Tipo e o Valor)

SELECT oid,* from pg_enum;

PostgreSQL em Evento de Curitiba

2008-10-20T11:01:00.004-03:00

O SERPRO - Serviço Federal de Processamento de Dados, promove o II Fórum de Tecnologia em Software Livre, em Curitiba. O evento ocorrerá de 10 a 12 de novembro de 2008, na Regional Serpro de Curitiba.

O PostgreSQL faz parte da programação por meio de uma palestra sobre as funcionalidades da versão 8.3 e de um minicurso de PostgreSQL Avançado, ministrado por mim. Plataformas, tecnologias e processos baseados em software e formatos livres e abertos fazem parte da programação do evento.

Participe! As incrições podem ser feitas pelo site: http://www.cta.softwarelivre.serpro.gov.br/

PostgreSQL no Novo Concurso do SERPRO

2008-10-09T13:14:00.003-03:00

(09/10/2008) Saiu o edital do concurso do SERPRO - Serviço Federal de Processamento de Dados e pela primeira vez o banco de dados PostgreSQL aparece no edital. No cargo 5: ‘ANALISTA – ESPECIALIZAÇÃO: DESENVOLVIMENTO DE SISTEMAS’, aparece no conteúdo programático o tópico ‘PostgreSQL em Linux’. Dos bancos de dados proprietários, apenas o ADABAS (Software AG) foi citado explicitamente.

Uma curiosidade: no cargo 17, ANALISTA – ESPECIALIZAÇÃO: SUPORTE TÉCNICO, o PostgreSQL também faz parte do conteúdo programático, mas esta escrito errado.

E difícil afirmar que cairá ao menos uma questão de PostgreSQL no concurso, no entanto este edital consiste em mais um indicador da crescente utilização deste banco de dados na esfera federal brasileira.

Confira aqui o edital completo e boa sorte!

SERPRO Participa da PgCon 2008

2008-10-03T08:40:00.002-03:00

O SERPRO - Serviço Federal de Processamento de Dados participou da PgCon 2008 como patrocinador. A ferramenta é o principal banco de dados livre homologada para compor as soluções da empresa, que conta com um portfólio de centenas de sistemas ativos.

Saiba mais neste link.

Esqueça o Comando SHOW: Use PG_SETTINGS

2008-10-02T10:13:00.006-03:00

O comando SHOW é bastante utilizado para visualizar as configurações de um servidor PostgreSQL de datas, log, gerência de memória etc. Uma vez que na versão em que estou trabalhando tempos 187 parâmetros distintos, este comando se revela bastante prático para se ter uma visão geral da configuração e bem fácil de usar sem.
Abaixo, exemplos de sua sintaxe:

- SHOW ALL

- SHOW work_mem

No entanto, apresenta uma série de desagradáveis limitações:

- O resultado da consulta das variáveis de configuração não pode ser alterado (essa atualização pode ser feita pelo comando SET).
- Os campos mostrados são poucos e até insuficientes dependendo da necessidade (nome do parâmetro, valor corrente e descrição)
- Não é possível selecionar um subconjunto dados para ser visualizado (linhas e colunas), tampouco agregar colunas adicionais na consulta retornada.
- Não é possível montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 variáveis importantes, deve-se usar SHOW ALL ou fazer 10 comandos SHOW indicando as variáveis desejadas.

Apesar de sua utilizada, este comando simplesmente limita a flexibilidade de consultas e relatórios sobre as configurações do PostgreSQL. Como contornar esta limitação?

A tabela virtual PG_SETINGS contorna TODAS AS LIMITAÇÕES apresentadas pelo comando SHOW. As únicas restrições são:
- Não se pode inserir registros ou excluí-los (assim como não se pode adicionar e excluir variáveis de configuração)
- Certas variáveis não podem ser alteradas com o servidor em funcionamento ou apresentam restrições (como 'autovacuum' e 'bonjour_name', por exemplo).

As vantagens obtidas são várias:
- O resultado da consulta das variáveis de configuração pode ser alterado, respeitando-se as restrições de cada variável e seus valores aceitos .
- São mostradas mais informações sobre cada item de configuração (11 colunas)
- É possível selecionar um subconjunto dados para ser visualizado (utilizando comando SELECT!!!)
- É possível (e fácil) montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 (ou mais) variáveis importantes, pode ser empregado apenas um comando que retorne exclusivamente as variáveis desejadas.

Exemplos de utilização de PG_SETTINGS:

1 - Sintaxe básica, retornando todas as configurações.

SELECT * FROM pg_settings;

2 - Seleção de variáveis de configuração que comecem com 'AUTO'.

SELECT * FROM pg_settings WHERE NAME LIKE 'auto%';

3 - Consulta de variáveis de configuração com UNION.

SELECT * FROM pg_settings WHERE NAME LIKE 'auto%'
UNION
SELECT * FROM pg_settings WHERE NAME LIKE 'Date%';

4 - Consulta de variáveis de configuração com junção.

SELECT * FROM pg_settings p1, pg_settings p2
WHERE P1.NAME LIKE 'log_%' AND P1.NAME = P2.NAME AND UPPER(P2.UNIT) = 'KB';

Que tipo de aplicação você implementa com PostgreSQL?

2008-09-22T09:03:00.003-03:00

Esta e a enquete que está sendo realizada no momento pela comunidade postgresql.org

Participe!!! O link está abaixo:

http://www.postgresql.org/community/

Exemplos de Código e SQL e PgSQL

2008-09-08T09:13:00.003-03:00

O site abaixo apresenta uma série de bons exemplos (e simples de reproduzir) de código SQL e PlPgSQL. São abordados assuntos como funções matemáticas, de datas, índices, tabelas, consultas, cursores, tipos de dados, entre outros recursos do banco. Boa sugestão para a lista de favoritos de quem trabalha com o PostgreSQL:

http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm

pgCon 2008 - Participe Você Também!

2008-09-05T15:39:00.002-03:00

Antes tarde do que nunca, segue lembrete do maior evento de PostgreSQL nacional, a pgCon 2008!
A expectativa dos organizadores é de um grande crescimento do número de participantes em relação ao ano passado.

Site oficial: http://pgcon.postgresql.org.br/

Inscrições: http://pgcon.postgresql.org.br/inscricoes.html

Grade do evento: http://pgcon.postgresql.org.br/programacao.html

Aproveite os preços promocionais!

Templates no PostgreSQL...

2008-09-05T12:43:00.002-03:00

Templates são uma poderosa ferramenta para a criação de bancos de dados padronizados, e uma característica estável implementada no PostgreSQL. No entanto, nem sempre é bem corretamente utilizada e entendida. Neste texto será comentado o seu funcionamento e os benefícios que podem ser obtidos.

* Templates de Bancos de Dados

Templates são modelos. Criar um banco de dados com base em um template é simplesmente utilizar um banco de dados como modelo para a criação de outro. Como ganhos do uso de templates podem ser destacados:
- Melhor padronização dos bancos de dados criados
- Maior velocidade na criação de novos bancos de dados, evitando-se a necessidade de se rodar scripts e criar objetos após a sua criação.

* Implementação no PostgreSQL

No PostgreSQL, a criação de qualquer banco de dados se dá pela cópia de objetos e estrutura de um banco já existente para o novo a ser criado. São definidos por padrão dois bancos de dados templates: template0 e template1, no entanto qualquer banco de dados pode ser utilizado como template em um servidor PostgreSQL.

O template0 é mais enxuto e deve ser predominantemente utilizado para criação de bancos que serão populados via restauração de backup. Não apresenta todos objetos e funções do template1, o qual é mais utilizado e é recomendado como principal padrão para criação de novos bancos de dados.

Acrescentar funções, tabelas e outros objetos a um banco de dados template, é torná-los disponíveis a qualquer novo banco de dados que for criado posteriormente. A atualização de bancos criados anteriormente com o template que foi atualizado deve ser feita de forma manual caso seja necessária.

Abaixo, a criação de bancos de dados utilizando templates:

-- Criação sem parâmetro -T, cria banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate "Banco de Dados Template 1"

-- Criação de novo banco de dados com base no template 0
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres -T template0 dbtemplate2 "Banco de Dados Template 0"

-- Criação de novo banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate3 "Banco de Dados Template 1"

* Personalização de Template no PostgreSQL

Personalizar um template para uso na criação de novos bancos de dados é relativamente fácil e demanda apenas algumas precauções:
- Evitar alterar os templates padrão do PostgreSQL (template0 e template1) - Crie novos bancos de dados e personalize-os, utilizando-os posteriormente como templates.
- Planeje que tipo de função o banco de dados template deve oferecer: banco de dados web, banco de dados para Data Warehouse, etc., criando templates específicos mais adequados.
- Nomeie seus templates de forma distinta, para diferenciá-los de bancos de dados de aplicações.
- Não utilizar bancos de dados de aplicações como templates. O processo de cópia do template para o novo banco de dados exige que o template não esteja sofrendo acesso por parte de outros usuários, o que pode gerar erros na criação de novos bancos.

Abaixo, uma proposta de atividade para testar a personalização do banco de dados template dbtemplate3:

- Abra o banco de dados dbtemplate3.
- Crie as rotinas CPF_formatar e CPF_validar que estão na seção de algoritmos deste blog.
- Feche o banco de dados.
- Crie novo banco de dados, chamado "corporativo", utilizando como template o dbtemplate3.
- Abra o banco de dados "corporativo" e localize as funções que você adicionou ao template.

DBLINK: Consultas, Comandos SQL, Transações e Funções

2008-08-18T11:35:00.004-03:00

A realização de consultas e a execução de comandos SQL são as principais atividades realizadas com o DBLINK. Continuando os comentários iniciados no post anterior, este tópico apresenta exemplos de aplicação desta tecnologia do PostgreSQL.

As rotinas dblink e dblink_exec permitem a realização de consultas, inserção, alteração e exclusão de dados em bancos distintos. Podem ser utilizadas dentro de uma transação ou de uma função.

* Consultas

As consultas são feitas com a rotina dblink e seguem uma sintaxe diferente do tradicional. Pode ser criada uma conexão para a execução da consulta ou pode ser usada uma que já tenha sido criada anbteriormente.

A rotina dblink tem dois parâmetros, sendo o primeiro a conexão e o segundo o comando a ser executado. Além dos parâmetros, o dblink pede que se liste os campo e seus tipos de dados.

O exemplo abaixo mostra uma consulta que utiliza a conexão "myconn", criada pela função dblink_connect:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda usando conexão criada
SELECT estoque_prod.* FROM dblink('myconn', 'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);

O exemplo abaixo mostra uma consulta que apresenta como parâmetro uma string para conexão:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda com conexão dentro do comando
SELECT * FROM dblink('hostaddr=10.200.239.55 port=5432 dbname=estoque user=postgres password=post',
'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);

* Inserção, Alteração e Exclusão de Dados

Estas operações são implementadas por meio da rotina dblink_exec. Como parâmetros são fornecidos a conexão e o comando a ser executado:

--Inserção de registros com DBLINK_EXEC
--Detalhe: uso das aspas simples. As mesmas devem estar duplicadas.
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (6, ''Relogio'', 400, 110.90);');

--Atualização de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - 1 WHERE cod_prod = 6;');

--Exclusão de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'DELETE FROM ESTOQUE_ITEM WHERE cod_prod = 6;');

* Dblink Dentro de uma Transação

Comandos do dblink funcionam normalmente dentro de transações.

Abaixo, um exemplo de transação de venda, sensibilidando dois bancos de dados distintos:

BEGIN;
INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (1, 1000 , DEFAULT);
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (5, ''Relogio'', 400, 110.90);');
END;

* Uso do Dblink Dentro de Funções

O dblink pode ser livremente utilizado dentro de funções. Pode ainda apresentar parametrização e a chamada à função pode estar inscrita dentro de uma transação maior.

Abaixo, um exemplo com dois parâmetros, simulando uma transação de compra e venda:

-- Uso de dblink DENTRO de uma função
CREATE OR REPLACE FUNCTION func_venda(cod_prd integer, quant integer) RETURNS INTEGER AS $$
DECLARE
comando_venda TEXT;
comando_estoque TEXT;
BEGIN
comando_venda := 'INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (' || $1 || ',' || $2 || ', DEFAULT);';
EXECUTE comando_venda;
comando_estoque := 'SELECT dblink_exec(''myconn'', ''UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - ' || $2 || ' WHERE cod_prod = ' || $1 || ''');';
EXECUTE comando_estoque;
RETURN 1; --Retorno com sucesso
END;
$$ LANGUAGE plpgsql;

SELECT func_venda(1,1);

DBLINK: Introdução, dblink_connect/ dblink_disconnect e dblink_get_connections

2008-08-14T13:34:00.005-03:00

O dblink é um dos mais importantes contribs do PostgreSQL. Consiste em um conjunto de métodos para acesso a outros bancos de dados do PostgreSQL em uma mesma sessão.

Com esta biblioteca é possível fazer consultas, inserções, alteração e exclusão de dados em mais de um banco de dados (PostgreSQL) sem sair da sessão corrente, o que facilita muito a comunicação entre bancos de dados.

Não faz parte do núcleo deste SGBD, mas pode ser instalado sem grandes problemas e é relativamente fácil de se utilizar.

As principais funções do dblink são:
- dblink_connect
- dblink_get_connections
- dblink - para consultas
- dblink_exec - utilizado na inserção, alteração e exclusão de dados
- dblink_disconnect - liberar conexões dblink

Existem alternativas ao uso desta solução para a comunicaçção entre bancos de dados, tais como cargas diárias de bancos de dados, e outros softwares, mas o dblink tem sido bastante empregado na construção da interoperabilidade de bancos de dados do PostgreSQL e se posiciona como boa alternativa.

Ressalvas:
- Este artigo não visa esgotar a sintaxe do dblink e todos os seus comandos. Apenas mostra uma visão geral do seu funcionamento.

Para testar o dblink, crie dois bancos de dados distintos, chamados estoque e vendas, no mesmo servidor ou em máquinas distintas, e insira as seguintes tabelas e dados:

--Banco de Dados VENDA
CREATE TABLE VENDA_ITEM (
cod_venda SERIAL PRIMARY KEY,
cod_prod INTEGER NOT NULL,
quant_vendida INTEGER NOT NULL,
data DATE DEFAULT current_date NOT NULL);

--Banco de Dados ESTOQUE
CREATE DATABASE ESTOQUE;
DROP TABLE ESTOQUE_ITEM;
CREATE TABLE ESTOQUE_ITEM (
cod_prod SERIAL PRIMARY KEY,
desc_prod VARCHAR(50) NOT NULL,
quant_estocada INTEGER NOT NULL CHECK (quant_estocada > 0),
preco NUMERIC(10,2) CHECK (preco > 0));

INSERT INTO ESTOQUE_ITEM VALUES (1, 'Lápis Preto', 10000, 1.50);
INSERT INTO ESTOQUE_ITEM VALUES (2, 'Borracha', 50, 0.90);
INSERT INTO ESTOQUE_ITEM VALUES (3, 'Caderno 200 Folhas', 175, 8.90);
INSERT INTO ESTOQUE_ITEM VALUES (4, 'Agenda do Ano', 40, 12.50);

* dblink_connect

A rotina dblink_connect cria uma conexão na sessão corrente do PostgreSQL para outro banco de dados. Antes de utiliza-la, é necessário configurar o banco de dados para aceitar a conexão, alterando o arquivo pg_hba e as permissões do usuário.

Como parâmetros, devem ser fornecidos o nome da conexão e uma string de parâmetros de conexão para o banco a ser acessado:

--Criação de Conexão com o DBLINK
-- Conecta com o banco de dados estoque
SELECT dblink_connect('myconn', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Cria nova conecxão com o banco de dados estoque, sem interferir na anterior
SELECT dblink_connect('myconn2', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Conecta com o banco de dados vendas
SELECT dblink_connect('myconn3', 'hostaddr=10.211.239.55 port=5432 dbname=vendas user=postgres password=post');

Ressalvas:
- Evite criar muitas conexões pelo dblink. Cada conexão que for criada vai ocupar memória extra e pode impactar no desempenho do seu sistema. A execução dos exemplos anteriores vai criar três conexões ativas na memória.
- Pode haver lentidão de acesso na conexão dblink. Se um dos bancos de dados conectados estiver com problemas de lentidão, a conexão como um todo será lenta.

* dblink_get_connections

Esta função mostra as conexões ativas do dblink. Não mostra as demais conexões ao banco de dados.

Não apresenta parâmetros. Abaixo, a sintaxe:

--Consultando conexões abertas com DBLINK
SELECT dblink_get_connections();

* dblink_disconnect

A rotina dblink_disconnect encerra conexões criadas com o dblink, liberando a memória. Recebe como parâmetro o nome da conexão a ser encerrada. Execute este comando periodicamente para liberar recursos de hardware, evitando manter conexões ociosas.

--Desconexão
SELECT dblink_disconnect('myconn3');

Os próximos posts comentarão as demais funcionalidades do dblink, apresentando exemplos.

Referência:
- Livro PostgreSQL Prático - dblink

Aprovado! Instalador Amigável para Linux

2008-08-01T11:17:00.020-03:00

Mês passado criei um post sobre novos instaladores do PostgreSQL para Linux e Mac. Depois de um tempinho, resolvi testar no Linux.

Na plataforma Linux, a instalação do PostgreSQL normalmente é feita com uma série de comandos do sistema operacional, o que é uma boa maneira de ter maior controle dos resultados. No entanto este processo pode ser dificultoso para neófitos e mesmo para os mais experientes. O procedimento também pode ficar difícil se algo não sair conforme o manual ou tutorial utilizado estiver indicando.

O instalador novo é uma boa opção para quem quer um servidor rodando, mas não deseja perder tempo em configurações avançadas e digitação em prompts de comando. Sua interface é gráfica, simples e agradável.

Os testes foram feitos em uma máquina com Linux Fedora 2.

Preparação da Instalação

Faça o login na estação Linux como usuário root. O próximo passo é fazer o download do instalador e copiá-lo em uma pasta do sistema de arquivos.

Abra o terminal, entre no diretório em que está o instalador e use o comando exec para rodar o programa:

exec /home/usr/postgresql-8.3.3-2-linux.bin

O sistema abrirá uma tela com um assistente amigável para a instalação do PostgreSQL como servidor na máquina.

Etapas da Instalação

As telas são bem simples e futuras versões do instalador devem ter novos recursos. Abaixo, a tela inicial.

Em seguida aparece a tela de seleção do diretório de instalação.

Telas de diretório de dados e de senha do superusuário. Por padrão este instalador cria um superusuário chamado POSTGRES .

Etapas de definição da porta de comunicação utilizada (padrão 5432) e locale (localização geográfica associada a um conjunto de parâmetros: país, idioma, etc.).

Terlas de seleção de linguagens procedurais para as funções e procedimentos e a progress bar de instalação. Observe que as linguagens serão instaladas no template1, geralmente utilizado como base para criação de novos bancos de dados.

Resultado da Instalação

Imediatamente após a instalação pode-se trabalhar com o PostgreSQL.
Abaixo, a tela de conclusão da instalação e visualização dos componentes instalados.

Considerações Finais

Espero que este instalador facilite a vida de quem está iniciando na ferramenta ou de quem tem de fazer várias instalações em curto prazo. Acredito que certas etapas poderiam ser unificadas e que novos parâmetros poderiam ser configuráveis para uma instalação mais personalizada. Apesar destas ressalvas, minha avaliação é super positiva.

Validação de CPF - Algoritmo Sem Loops

2008-07-25T11:20:00.004-03:00

O algoritmo para validação de CPF pode ser implementado sem loops. A solução alternativa que aparece convertida para Pl/ PgSQL foi retirada do site do Clube do Hardware, com alguns ajustes. O código ficou bem pequeno e os loops foram trocados por operações com vetores. Observe que no PostgreSQL os vetores começam a partir da posição zero.

O código aparece abaixo:

-- Formatar CPF com vetor
CREATE OR REPLACE FUNCTION CPF_Validar_Sem_Loop(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF SEM LOOP
-- Conversão para o PL/ PGSQL: Cláudio Bezera Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://forum.clubedohardware.com.br/validacao-cpf/51717
-- Retorna 1 para CPF correto.
DECLARE
vet_cpf integer [11]; --Recebe o CPF DIGITADO
dv integer; -- Primeiro número do DV (Dígito Verificador)
dv1 integer; -- Segundo número do DV (Dígito Verificador)
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (par_cpf FROM 1 FOR 1) as integer);
vet_cpf[1] := cast(substring (par_cpf FROM 2 FOR 1) as integer);
vet_cpf[2] := cast(substring (par_cpf FROM 3 FOR 1) as integer);
vet_cpf[3] := cast(substring (par_cpf FROM 4 FOR 1) as integer);
vet_cpf[4] := cast(substring (par_cpf FROM 5 FOR 1) as integer);
vet_cpf[5] := cast(substring (par_cpf FROM 6 FOR 1) as integer);
vet_cpf[6] := cast(substring (par_cpf FROM 7 FOR 1) as integer);
vet_cpf[7] := cast(substring (par_cpf FROM 8 FOR 1) as integer);
vet_cpf[8] := cast(substring (par_cpf FROM 9 FOR 1) as integer);
vet_cpf[9] := cast(substring (par_cpf FROM 10 FOR 1) as integer);
vet_cpf[10] := cast(substring (par_cpf FROM 11 FOR 1) as integer);
-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv:=0;
ELSE dv:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:=(vet_cpf[0]*11)+(vet_cpf[1]*10)+(vet_cpf[2]*9)+(vet_cpf[3]*8)+(vet_cpf[4]*7)+(vet_cpf[5]*6)+(vet_cpf[6]*5)+(vet_cpf[7]*4)+(vet_cpf[8]*3)+(dv*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv1:=0;
ELSE dv1:=(11-rest); END IF;
IF (dv = vet_cpf[9]) and (dv1 = vet_cpf[10]) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;

Sintaxe para a chamada da função criada:

SELECT CPF_Validar_Sem_Loop ('número do cpf');

Caso queira relembrar o algoritmo com loops ou ver ais comentários sobre o cálculo de CPF, clique aqui.

Instalação no Linux e Mac com Apenas um Clique!

2008-07-18T11:52:00.002-03:00

Estão disponíveis os betas de instaladores para Linux e Mac que permite a instalação com apenas "um clique". A criação destes instaladores foi feita péla equipe do EnterpriseDB. Espera-se que a dificuldade de instalação nestas plataformas seja minimizada.

Os instaladores contém o PostgreSQL 8.3.3 com pgAdmin 1.8.4, pl/Java e o plugin para debug da linguagem pl/pgsql.

O download pode ser feito em:

Linux 32 e 64 bits: http://www.postgresql.org/download/linux

Mac OS X 10.4: http://www.postgresql.org/download/macosx

PostgreSQL: Presença Fraca no Youtube

2008-07-09T08:25:00.002-03:00

A presença do PostgreSQL entre os grandes SGBDs pode ser medida de diversas formas. Em busca de vídeos interessantes de PostgreSQL, fiz uma busca no Youtube, e me decepcionei. Encontrei apenas 356 vídeos. É muito pouco para cobrir todas as funcionalidades e possibilidades deste banco de dados.

Considerando-se que este resultado envolve todos os idiomas, os resultados são ainda mais fracos, pois temos muito pouca coisa em Português. A qualidade da imagem também deixa a desejar, possivelmente pelo formato do Youtube, que reduz a resolução e prejudica apresentações que capturam telas do computador e as filmagens de apresentações em telões multimídia. Faltam comentários nos vídeos, a visualização ainda é pequena por parte da comunidade e muitos permanecem sem avaliação (o Youtube tem uma graduação que vai até 5 estrelas). Vi bons vídeos em italiano, mas acho que a estrada a ser trilhada pelo PostgreSQL em vídeo ainda está no seu início.

Para ter uma visão comparativa, fiz outras buscas para outros SGBDs. Entre os bancos de dados livres, apesar do PostgreSQL só estar atrás do MySQL, não se pode dizer que é uma boa colocação. A abrangência, quantidade e qualidade dos vídeos pode melhorar muito. O próprio campeão MySQL teve muito mais resultados encontrados, mas bem abaixo do que poderia ser. O fato é que ainda não utilizamos vídeos como forma de aprendizado e divulgação de conhecimento como poderíamos.

Mesmo os Bancos de Dados proprietários apresentam poucas opções, mas o SQL Server teve quase o dobro de vídeos que o PostgreSQL. O Oracle também decepcionou e pode ser alcançado pelo PostgreSQL com algum trabalho da comuindade.

O ranking total aparece abaixo. Faltam alguns SGBDs, mas o propósito não era uma busca exaustiva. A grande surpresa foi o DB2, com a terceira colocação. Os bancos de dados livres lideram com o MySQL e o PostgreSQL ocupa apenas uma posição intermediária, com o quinto lugar.

1 - MySQL: 1330
2 - SQL Server: 674
3 - IBM DB2: 461
4 - Oracle (busca por "Oracle SQL"): 406
5 -PostgreSQL: 356
6 - Firebird (busca por "Firebird SQL"): 171
7 - ACCESS (busca por "ACCESS SQL"): 50
8 - SQLite: 22
9 - ADABAS: 5
10 - HSQLDB: 0

O que podemos fazer a respeito?

Bom, creio que podemos influir positivamente quanto a esta questão:

- Assistir a vídeos de PostgreSQL no Youtube, dando feedback aos seus criadores, através de comentários e da colocação das estrelas, indicando os melhores. Esta ação permite que se possa estimular a produção de melhores vídeos e o reconhecimento dos autores.
- Postar novos fragmentos de palestras e de cursos com conteúdo novo e diversificados. Muitos eventos de Software Livre, de Banco de Dados em Geral e de PostgreSQL são gravados e poderia ser feita seleção e publicação de parte deste material.
- Produzir novos vídeos voltados para a internet. Podem ser pequenos, podem ser focados em temas específicos, indo do básico ao avançado. É a melhor forma de aprender a fazer mais e melhor.

Quem se habilita?

Este é um problema realmente grave?

Acredito que não, mas que é um ponto que pode ser melhor trabalhado pela comunidade do PostgreSQL.

Limitações: Configuração de Case Sensitivity and Accent Sensitivity

2008-06-23T15:17:00.003-03:00

A partir de hoje colocarei à medida que forem surgindo, limitações que encontro no PostgreSQL. A primeira limitação a ser mencionada é a falta de uma configuração para Case Sensitivity e Accent Sensitivity no SGBD.

Case Sensitivity e Accent Sensitivity são características particularmente desejáveis nos bancos de dados atuais. A não existência de configuração para o armazenamento e/ou recuperação de dados com base nestas características é uma lacuna a ser preenchida neste banco de dados. Atualmente não há este recurso e as buscas de dados levando em conta ou não maiúsculas e minúsculas exigem alterações nas aplicações (com UPPER, LOWER, LIKE e ILIKE).

Uma consulta com Case Sensitivity (sensível ao caso) retorna valores levando em consideração se os caracteres buscados estão em maiúsculas ou minúsculas. Desta forma, a busca pelo valor 'CLA' retorna resultados diferentes da busca por 'Cla'. O não uso de sensitividade nas buscas de caracteres é chamado de Case Insensitivity.

Uma consulta com Accent Sensitivity (sensível ao acento) retorna valores levando em consideração se os caracteres buscados estão em maiúsculas ou minúsculas. Desta forma, a busca pelo valor 'CLA' retorna resultados diferentes da busca por 'Cla'. O não uso de sensitividade nas buscas de caracteres acentuados é chamado de Accent Insensitivity.

Proposta:

- A implementação de tal característica como configuração poderia ser feita para o banco todo, por tablespace, tabela ou visão, facilitando a implementação e evitando alterações nas aplicações mais complexas que necessitam destas funcionalidades.

- Poderia ser feita via comando SET e na criação de objetos do banco, como na de tabelas, indicando que colunas são SENSITIVE e INSENSITIVE.

- O padrão deve ser INSENSITIVE por questões de desempenho.

Ressalvas:

- Novas propostas para esta funcionalidade são bem vindas e podem ser colocadas no comentário deste post.

Formatação de CPF - Uma função simples

2008-06-11T08:39:00.002-03:00

O CPF, número do Cadastro de Pessoa Física, apresenta o formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador. Uma vez que o mesmo é bastante utilizado, sua validação e apresentação são opções que podem ser implementadas dentro do banco de dados , com reuso do mesmo código para uso em vários sistemas.

Neste post vamos fazer uma função simplificada para formatação e apresentação de CPF.

A consulta abaixo faz a formatação de um CPF. Apresenta a desvantagem de se ter de repetir várias vezes o valor do CPF a ser apresentado.

SELECT substring('12345678912' FROM 1 FOR 3) || '.' || substring('12345678912' FROM 4 FOR 3) || '.' || substring('12345678912' FROM 7 FOR 3) || '-' || substring('12345678912' FROM 10 FOR 2);

O uso de uma função aumenta a simplicidade e facilita o reuso:

CREATE OR REPLACE FUNCTION CPF_formatar(par_cpf varchar(11)) RETURNS varchar(14) AS $$
-- ROTINA DE FORMATAÇÃO DE CPF
-- Código PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Retorna o CPF formatado no formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador
-- em caso de erro retorna 'ERRO'
BEGIN
IF char_length(par_cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 'ERRO';
END IF;
RETURN substring(par_cpf FROM 1 FOR 3) || '.' || substring(par_cpf FROM 4 FOR 3) || '.' || substring(par_cpf FROM 7 FOR 3) || '-' || substring(par_cpf FROM 10 FOR 2);
END;
$$ LANGUAGE PLPGSQL;

SELECT CPF_formatar('12345678912');

Validação de CPF com Pl/ PgSQL

2008-06-08T14:05:00.004-03:00

A partir de hoje passamos a divulgar algoritmos de funções e consultas que sejam de utilidade pública. A validação de CPF com Pl/ PgSQL foi escolhida em primeiro lugar por ser um algoritmo simples mas bastante útil (além disto, procurei em vários sites e não encontrei um exemplo em PL/ PGSQL).

O CPF é utilizado por muitos sistemas brasileiros como identificação dos indivíduos. Validar o CPF é fazer a verificação dos dois últimos dígitos que são gerados a partir dos nove primeiros. O código abaixo foi uma tradução mais ou menos literal do código em javascript deste site.

Talvez possa ser feita otimização ou melhoria neste algoritmo, mas a idéia é que vocês o melhorem e atualizem neste site. Estejam à vontade para utilizar e compartilhar este código.

CREATE OR REPLACE FUNCTION CPF_Validar(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF
-- Conversão para o PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://webmasters.neting.com/msg07743.html
-- Retorna 1 para CPF correto.
DECLARE
x real;
y real; --Variável temporária
soma integer;
dig1 integer; --Primeiro dígito do CPF
dig2 integer; --Segundo dígito do CPF
len integer; -- Tamanho do CPF
contloop integer; --Contador para loop
val_par_cpf varchar(11); --Valor do parâmetro
BEGIN
-- Teste do tamanho da string de entrada
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Inicialização
x := 0;
soma := 0;
dig1 := 0;
dig2 := 0;
contloop := 0;
val_par_cpf := $1; --Atribuição do parâmetro a uma variável interna
len := char_length(val_par_cpf);
x := len -1;
--Loop de multiplicação - dígito 1
contloop :=1;
WHILE contloop <= (len -2) LOOP
y := CAST(substring(val_par_cpf from contloop for 1) AS NUMERIC);
soma := soma + ( y * x);
x := x - 1;
contloop := contloop +1;
END LOOP;
dig1 := 11 - CAST((soma % 11) AS INTEGER);
if (dig1 = 10) THEN dig1 :=0 ; END IF;
if (dig1 = 11) THEN dig1 :=0 ; END IF;

-- Dígito 2
x := 11; soma :=0;
contloop :=1;
WHILE contloop <= (len -1) LOOP
soma := soma + CAST((substring(val_par_cpf FROM contloop FOR 1)) AS REAL) * x;
x := x - 1;
contloop := contloop +1;
END LOOP;
dig2 := 11 - CAST ((soma % 11) AS INTEGER);
IF (dig2 = 10) THEN dig2 := 0; END IF;
IF (dig2 = 11) THEN dig2 := 0; END IF;
--Teste do CPF
IF ((dig1 || '' || dig2) = substring(val_par_cpf FROM len-1 FOR 2)) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;

PGCon 2008: Seja um palestrante!

2008-06-02T08:23:00.004-03:00

Uma das melhores formas de colaborar com a comunidade de software livre é compartilhar os conhecimentos. A PGCon 2008 oficialmente divulgou hoje a chamada de palestras e tutoriais.

A PGCon é a maior convenção brasileira, e possivelmente latino-americana de PostgreSQL. O site da edição de 2008 que ocorrerá dias 26 e 27 de Setembro de 2008 no Centro de Convenções da Unicamp em Campinas, SP, já está no ar aqui!

Esta é uma arena aberta àqueles que têm o espírito de compartilhamento e conteúdo para divulgar. Não perca esta oportunidade!

===================================================
Comunicado Oficial:
===================================================

A Comunidade Brasileira de PostgreSQL tem o prazer de convida-lo para
participar do PGCon Brasil 2008[1]. Após a realização do PGCon Brasil
2007[2], estamos novamente convidando a comunidade brasileira a enviar
suas propostas de trabalho para a segunda edição deste evento. O PGCon
Brasil 2008 será realizado nos dias 26 e 27 de setembro de 2008 na
UNICAMP[3] (Campinas-SP).

* Instruções:
- As palestras terão duração de 50 minutos, incluindo o tempo
para as perguntas. Haverá também a escolha de apenas um tutorial
que poderá ter até 120 minutos de duração.
- Qualquer palestrante pode enviar mais de uma proposta;
- O PGCon Brasil não se responsabiliza pelos gastos
com deslocamento, hospedagem e alimentação dos palestrantes;
- Todas as propostas deverão ser enviadas até 22/06/2008 em
formato texto para o e-mail; "pgcon@postgresql.org.br" contendo o
seguinte formulário:

* Formulário:
o Nome completo
o E-mail
o Telefones de contato
o Endereço completo
o Local de onde você virá (UF / Cidade)
o Mini currículo (até 500 caracteres)
o Título
o Tipo (palestra e/ou tutorial)
o Nível (iniciante, intermediário ou avançado)
o Resumo da palestra (até 500 caracteres)
o Descrição completa da palestra (até 3000 caracteres)

* Calendário:
- 02/06/2008 - Publicação da chamada de trabalhos;
- 22/06/2008 - Última data para envio de propostas;
- 11/07/2008 - Última data para seleção de palestras e envio
dos resultados aos palestrantes;
- 18/07/2008 - Última data para confirmação das palestras
selecionadas e publicação do resultado;
- 15/08/2008 - Última data para envio do rascunho das apresentações;
- 24/09/2008 - Última data para envio da versão final das apresentações;

Uso de SET em Funções Pl/PgSQL

2008-05-29T09:18:00.007-03:00

Na versão 8.3 do PostgreSQL foi implementado um recurso interessante, que permite a parametrização da execução de uma função utilizando o comando SET no momento da sua criação. Desta forma, a configuração é alterada apenas durante a sua execução, retornando aos valores prévios após o seu encerramento.

Valores de tablespace padrão (default_tablespace), encoding do cliente (client_encoding) entre outros são definidos de forma explícita, de modo que se force o comportamento esperado da função não importando que programa faça a sua chamada de execução.

Existe também a opção de utilização da cláusula FROM CURRENT para fazer com que a alteração de configuração seja mantida após a execução da função.

Nem todos os parâmetros de execução podem ser alterados com o comando SET dentro de uma função. Os erros são revelados no momento de criação da função.

O comando SHOW mostra a lista de parâmetros e seus valores correntes.

Criação de Função com Parâmetros de Configuração

O código abaixo mostra a criação de uma função que atualiza a tabela salário. Após o corpo da função são definidos os valores dos itens de configuração array_nulls, default_tablespace, commit_delay e client_encoding.

CREATE TABLE SALARIO (cod integer PRIMARY KEY, provento real);
INSERT INTO SALARIO VALUES (1, 1000.00);
INSERT INTO SALARIO VALUES (2, 100.00);

CREATE OR REPLACE FUNCTION salario_aumento() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET array_nulls = OFF
SET default_tablespace = 'pg_default'
SET commit_delay TO 10
SET client_encoding TO UNICODE;

Mantendo Alterações na Configuração

O código abaixo mostra a criação de uma função na qual é alterado e mantido o valor do client_encoding. A cláusula FROM CURRENT indica que parâmetro terá o valor de configuração que foi alterado mantido.

CREATE OR REPLACE FUNCTION salario_parametros() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET client_encoding TO UNICODE
SET client_encoding FROM CURRENT;

Ressalva no Uso de SET em Funções

Alguns dos itens de configuração não são alteráveis pelo comando SET dentro de uma função. Neste caso, será retornado erro no ato da criação da função. No exemplo abaixo, tanto o log_checkpoints quanto o transaction_isolation não puderam ser alterados no escopo de uma função.

-- Não funcionaram
CREATE OR REPLACE FUNCTION salario_parametros_teste() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET log_checkpoints = ON
SET transaction_isolation = SERIALIZABLE;

ERROR: parameter "log_checkpoints" cannot be changed now

********** Error **********

ERROR: parameter "log_checkpoints" cannot be changed now
SQL state: 55P02