Novo Endereço

Pessoal, este blog e seu conteúdo até o momento foram migrados para o octopress e estamos em novo endereço:

http://fabriziomello.github.io

Obrigado pela compreensão.

Novidades

Em breve teremos novidades por aqui, pois começarei um trabalho de tradução dos posts do blog http://www.depesz.com/ do amigo Hubert Depesz Lubaczewski, da Polônia.

O blog do Hubert é bem ativo e possui inúmeros posts interessantes e conversando com ele achamos válida a idéia de ter uma versão em PT-BR do mesmo.

Logo teremos o primeiro post aqui... aguardem...

Bad Smells (Mal cheiros) em Bancos de Dados

Uma estrutura de um banco de dados, diferentemente da estrutura de um software, tende a deteriorar naturalmente com o passar do tempo. Dentre várias causas de deterioração podemos citar o crescimento progressivo do volume de dados devido ao aumento natural de usuários que o utilizam e também ao seu próprio tempo de uso, tornando um modelo de dados que no início era eficiente para solução proposta em um modelo ineficiente e defasado.

Essa deterioração natural aliada a mudanças em requisitos de negócio exigem modificações e refatorações tanto no software que os implementa quanto em seus bancos de dados. Entretanto a refatoração de um banco de dados é mais complexa que a de um software devido aos seguintes motivos: (i) além de manter comportamento também é necessário manter as informações (dados) e (ii) acoplamento com diversas origens (outras aplicações, frameworks, integrações, etc).

Devido a essas dificuldades a evolução de uma estrutura de banco de dados torna-se um desafio, ocorrendo assim um fenômeno conhecido como Bad Smells (mal cheiros), da mesma forma que ocorre com o código de um software. Em software um code smell (bad smell) é uma categoria comum de problema no código fonte que indica a necessidade de refatoração, e o mesmo ocorre com bancos de dados, onde são chamados database smells.

Abaixo segue uma lista de alguns database smells:

• Multi-purpose column (coluna com mais de uma função)
• Multi-purpose table (tabela com mais de uma função)
• Redundant data (dados redundantes/repetidos)
• Tables with many columns (tabelas com muitas colunas)
• "Smart" columns (colunas "espertas")
• Lack of constraints (falta de restrições)
• Fear of change (medo da mudança)

Dentre os database smells citados acima, devemos ter atenção especial ao "Fear of change", pois pode ser considerado o pior de todos, pois:

• inibe a inovação, 
• reduz a efetividade, 
• produz ainda mais bagunça e 
• ao longo do tempo a situação fica cada vez pior. 

Lembra do valor "Coragem" do XP (eXtreme Programming)? Mudança é algo que deve ser considerado natural e irá acontecer em projetos de software, isso é um fato, não podemos fugir disso, então precisamos das práticas e ferramentas adequadas para que a mudança não seja um entrave na evolução do seu projeto, e sim algo natural e, de certa forma, trivial para sua equipe, e o mesmo deve ocorrer com seu banco de dados.

Esse post é apenas uma introdução a este assunto pouco explorado e que tenho como proposta para conclusão de Pós-Graduação em Tecnologias Aplicadas a Sistemas de Informações com Métodos Ágeis que estou realizando na http://www.uniritter.edu.br.

A idéia é montar uma taxonomia de Database Smells através de um catálogo, inclusive sugerindo os Database Refactoring que podem ser utilizados em sua solução, e futuramente, quem sabe em um futuro trabalho de Mestrado fazer um paralelo dos Database Smells com Code Smells, principalmente se os mal cheiros em códigos podem refletir mal cheiros no seu banco de dados. ;-)

Database Refactoring

Contextualização

Refatoração de código (Code Refactoring) é uma disciplina/processo que consiste em melhorar a estrutura interna de um software sem modificar seu comportamento externo, e uma Refatoração de Banco de Dados (Database Refactoring) parte do mesmo princípio, porém além de manter o comportamento externo também deve manter a semântica da informação que ele mantém/armazena, e por esse motivo é considerada mais difícil.

Um outro conceito que posso destacar a respeito de Database Refactoring é:
"Mudança disciplinada na estrutura de uma base de dados que não altera sua semântica, porém melhora seu projeto e minimiza a introdução de dados inconsistentes"

O ponto interessante deste último é o texto "minimiza a introdução de dados inconsistentes", pois esse é o grande objetivo de realizarmos um refactoring na estrutura de um banco de dados, ou seja, melhorar o desing atual para melhorar a consistência dos dados e também a qualidade dos novos dados que serão adicionados ao seu banco de dados.

E esta tarefa não é das mais simples, pois existe um fator preponderante no que diz respeito a dificuldade de execução deste tipo de refactoring que é o acoplamento, que será visto logo a seguir.

Acoplamento

Figura 1. Baixo Acoplamento

É a medida de dependência entre dois elementos. Quanto mais acoplados dois elementos estiverem, maior a chance que a mudança em um implique na mudança em outro.

Figura 2. Alto Acoplamento

Simples assim, quanto mais o seu banco de dados estiver acoplado, ou seja, dependente de diversas aplicações externas, mais difícil será a aplicação de um refactoring.

A Figura 1 demonstra um cenário "Single-Database Application" que é bem simplificado, onde a aplicação de um refactoring será mais tranquilo.

Com certeza o cenário da Figura 2, o "Multi-Database Application" é o pior caso, pois exige muito cuidado e planejamento para execução do refactoring, então veremos a seguir uma sugestão de processo para execução.

Processo de Refatoração

Figura 3. Processo de Database Refactoring

Um processo é um conjunto organizado de atividades com um objetivo em comum. Executar um database refactoring em um cenário "Single-Database Application" ou "Multi-Application Database" requer um processo, por mais simples que seja. A grande diferença na execução em ambos cenários é que no caso do "Multi-Application Database" o período de transição (mais abaixo falaremos) geralmente será mais longo.

É bom sempre ter em mente que um database refactoring, como já vimos, não é uma atividade simples então caso seja identificada a real necessidade de refatorar um banco de dados então podemos usar o seguinte roteiro (processo) para se guiar:

• Escolher o refactoring mais apropriado;
• Depreciar o esquema original;
• Testar antes, durante e após;
• Modificar esquema;
• Migrar os dados;
• Modificar código externo;
• Executar testes de regressão;
• Versionar seu trabalho;
• Anunciar o refactoring.

Figura 4. Regra Geral Processo Refatoração

Na Figura 4 é demonstrado um pequeno processo descrevendo um fluxo básico para aplicação de um refactoring.

Atente bem para o "Período de Transição", que é a fase mais importante, principalmente para cenários "Multi-Database Application" (Figura 2), onde você precisa ter em mente que não conseguirá realizar o refactoring e fazer o deploy em produção de todas as aplicações ao mesmo tempo. Na verdade você nem conseguirá alterar todas as aplicações ao mesmo tempo, principalmente se você tiver dependência de terceiros, então você precisará suportar o esquema original e o esquema resultante ao mesmo tempo, para somente quando todas aplicações estiverem suportando apenas o esquema resultante, ou novo esquema, você poderá aposentar de vez o antigo esquema e assim finalizar este período.

Estratégias de Database Refactorings

Existem alguns pontos a considerar com estratégias para adoção de um database refactoring:

• Pequenas mudanças são mais fáceis de aplicar;
• Identifique unicamente cada refactoring;
• Implemente uma grande mudança realizando várias pequenas mudanças;
• Tenha uma tabela de configuração/versionamento do seu banco de dados;
• Priorize triggers ao invés de views ou sincronizações em lote;
• Escolha um período de transição suficiente para realizar as mudanças;
• Simplifique sua estratégia de controle de versão de banco de dados;
• Simplifique negociações com outros times;
• Encapsule acesso ao banco de dados;
• Habilite-se a montar facilmente um ambiente de banco de dados;
• Não duplique SQL;
• Coloque os ativos de banco de dados sobre controle de mudanças;
• Seja cuidadoso com políticas.

Os items acima mostram apenas algumas sugestões, em forma de "lições aprendidas", de algumas estratégias que você pode considerar quando tiver a necessidade de realizar um refactoring.

Para apoiar essas estratégias existe um catálogo que descrevem diversos tipos de refactorings em bancos de dados e exemplos de uso, que veremos a seguir.

Catálogo de Database Refactorings

Este catálogo é dividido em algumas categorias:

• Structural: são mudanças na estrutura do banco de dados (tabelas, colunas, visões, etc).
• Data Quality: são mudanças que melhoram a qualidade das informações contidas em um banco de dados.
• Referential Integrity: são mudanças que asseguram que uma linha referenciada exista em outra relação e/ou assegura que uma linha que não é mais necessária seja removida apropriadamente.
• Architectural: são mudanças que melhoram a maneira que programas externos interagem com a base de dados.
• Method: são mudanças que melhoram a qualidade de uma Procedure um Função.
• Transformations: mudanças que alteram a semântica do esquema do banco pela adição de novas funcionalidades.

No meu github é possível encontrar exemplos práticos de aplicação passo-a-passo de um refactoring em um modelo inicial, passando por um período de transição e chegando ao modelo final. 

Considerações Finais

Devemos levar em consideração que apesar destas técnicas serem direcionadas para refatoração, ou seja, mudar estrutura sem mudar sua semântica, as mesmas podem e devem ser utilizadas para evolução da sua aplicação, ou seja, se você precisa construir uma nova feature em sua aplicação que está em produção, você poderá recorrer das práticas aqui apresentadas para evoluir seu esquema de forma mais consistente e segura.

Baseado no exposto podemos facilmente responder a pergunta "Por quê Refatorar?":

• aceitar mudança de escopo;
• fornecer feedback rápido;
• melhoria contínua;
• aumentar simplicidade para facilitar entendimento;
• tornar os modelos mais próximos do mundo real;
• termos modelos simples para facilitar:
• manutenção e
• evolução da aplicação

E para refatorarmos precisamos ter conhecimento, disciplina, simplicidade, bom senso e persistência, sem contar no ponto fundamental que é organização.

Referências

• Livros: 
• Refactoring Databases (Scott W. Ambler e Pramod J. Sadalage)
• Agile Database Techniques (Scott W. Ambler)
• Refactoring (Martin Fowler)
• Agile Modeling (Scott W. Ambler) 
• Links:
• http://www.agiledata.org
• http://www.databaserefactoring.org
• http://visaoagil.wordpress.com
• http://www.refactoring.com
• http://www.postgresql.org
• http://www.epictest.org
• http://www.slideshare.net/diogobiazus/testes-unitarios-com-postgre-sql
• Slides
• http://www.slideshare.net/fabriziomello/tag/refactoring
• http://www.slideshare.net/antonkeks/database-refactoring

PostgreSQL 9.3devel - Nova extensão "postgres_fdw"

Apartir da release 9.1 o PostgreSQL adicionou suporte a foreign tables, viabilizando assim uma forma simples de gerenciar fontes de dados externas dentro do PostgreSQL.

Através dessa infraestrutura é possível a implementação dos FDW (Foreign Data Wrapper), que são uma espécie de driver para acessar uma fonte de dados externa.

Já existem diversos FDW implementados que permitem acessar outros bancos de dados (oracle, mysql, etc), arquivos (texto, csv, etc), bases NoSQL (mongodb, couchdb, redis, etc) e outras fontes de dados diferentes, tais como: twitter, ldap, www, etc.

Hoje foi commitado no git do PostgreSQL um FDW específico para acessar bases PostgreSQL, chamado "postgres_fdw", então vou demonstrar como instalar/configurar de uma forma muito simples.


1) Instalar o "postgres_fdw" apartir do git

Nesse exemplo vou mostrar como instalar/compilar o PostgreSQL apartir do git oficial, mas se vc tiver uma conta no github pode usar tb o nosso clone do repositório oficial.

git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
./configure --prefix=$HOME/pgsql
make
make install
cd contrib/postgres_fdw
make
make install

2) Inicializar um novo cluster e colocar o PostgreSQL para executar

$ cd $HOME/pgsql
$ mkdir data
$ chmod 700 data
$ ./bin/initdb -D data 
$ ./bin/pg_ctl -D data -l startup.log start

3) Criar bases e tabela para testes

$ cd $HOME/pgsql
$ ./bin/createdb bd1
$ ./bin/createdb bd2
$ ./bin/psql bd2 -c "create table foo(bar integer);"
$ ./bin/psql bd2 -c "insert into foo(bar) select * from generate_series(1, 10);"

Para explicar, criamos 2 (duas) bases de dados "bd1" e "bd2", e no "bd2" criamos uma tabela chamada "foo" a qual iremos acessar apartir do "bd1" criando uma foreign table como explicarei em seguida.


4) Criar a extensão "postgres_fdw" no "bd1"

$ ./bin/psql bd1
psql (9.3devel)
Type "help" for help.

bd1=# CREATE EXTENSION postgres_fdw ;
CREATE EXTENSION

bd1=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

5) Criar conexão com "bd2" no "bd1"

bd1=# CREATE SERVER conexao_bd2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'bd2');
CREATE SERVER

bd1=# \des+
                                              List of foreign servers
    Name     |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |  FDW Options   | Description 
-------------+----------+----------------------+-------------------+------+---------+----------------+-------------
 conexao_bd2 | fabrizio | postgres_fdw         |                   |      |         | (dbname 'bd2') | 
(1 row)

bd1=# CREATE USER MAPPING FOR current_user SERVER conexao_bd2 ;
CREATE USER MAPPING

bd1=# \deu+
         List of user mappings
   Server    | User name | FDW Options 
-------------+-----------+-------------
 conexao_bd2 | fabrizio  | 
(1 row)

6) Acessar a tabela "foo" do "bd2" apartir do "bd1"

bd1=# CREATE FOREIGN TABLE foo (bar integer) SERVER conexao_bd2 ;
CREATE FOREIGN TABLE

bd1=# \d
            List of relations
 Schema | Name |     Type      |  Owner   
--------+------+---------------+----------
 public | foo  | foreign table | fabrizio
(1 row)

bd1=# SELECT * FROM foo;
 bar 
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

Considerações

Segundo a própria documentação oficial, o "postgres_fdw" é uma alternativa mais robusta em relação ao antigo "dblink" pois nos oferece uma sintaxe mais padronizada e simplificada para acessar tabelas remotas, inclusive com melhor desempenho em muitos casos.

E pelo que pude acompanhar do seu desenvolvimento, parece que essa FDW deve servir como modelo para o desenvolvimento de outras FDW para acessar outras bases de dados relacionais.

De uma forma muito simples é possível acessar tabelas de outra base de dados PostgreSQL, e o mesmo ocorre com outras fontes de dados, mas por enquanto apenas para leitura (SELECT), entretanto já está em revisão um patch para permitir escrita (INSERT/UPDATE/DELETE) em foreign tables, vamos aguardar.

PostgreSQL + Unlogged Tables + Partitioning + Parallel Programming = ETL reescrito passando de ~8h para ~25min de execução

Já faz algum tempo que não escrevo nada por aqui, mas não é por falta de tempo ou coisa parecida, é que na realidade não tenho muita intimidade com artigos ou posts em blogs, mas resolvi escrever um "causo" a pedido do amigo Fernando Ike sobre um tweet que lancei há algum tempo depois de obter sucesso em um projeto.

Aviso antecipadamente que o post é um pouco longo, então se não estiver com paciência agora recomendo vc sair tomar um café (ou uma cerveja) e voltar outra hora... desculpe mesmo, tentei reduzir o máximo... :-(

Contextualização

Para vcs entenderem porque cheguei aqui, vou começar dos primórdios... eu tenho (ou tinha...hehehe...) um problema com um ETL em uma aplicação de um cliente (não tenho autorização para "dar nome aos bois") que basicamente processava os registros de uma grande tabela com dados financeiros e gerava uma "posição" da mesma calculando correção monetária, juros, multas, descontos, etc...

Esse ETL sempre foi e ainda é ridiculamente simples, porque basicamente é uma PL/pgSQL dentro do PostgreSQL que faz todo esse trabalho de ler os dados de uma tabela, processar e carregar os mesmos em outra tabela. Até aqui tudo bem, sempre funcionou maravilhosamente bem, mas com bases pequenas... mas também não queremos maravilhas de desempenho processando milhares de registros em uma única transação né, é óbvio que isso gera problemas.

Primeira tentativa... e um sucesso, digamos, opaco...

Há alguns anos eu já tinha melhorado essa rotina dividindo o processamento em lotes menores, através de um shell script que fazia esse trabalho de divisão em lotes por uma coluna da tabela que categorizava os registros em um determinado tipo, então primeiro identificamos os tipos existentes na tabela origem e processava os mesmos gerando tabelas individuais para cada tipo, e ao final juntava tudo na tabela de destino e removia as tabelas temporárias... e vejam só, SEMPRE a MESMA tabela de destino, e para isso precisamos remover os índices, executar o ETL e depois criar novamente os índices para termos um desempenho decente. Claro que junto dessa rotina implementamos também uma outra para expurgo de registros desnecessários/obsoletos (antigos), o que também sempre foi uma rotina que onerava bastante o servidor pois era SEMPRE a MESMA tabela de destino, então imaginem precisar remover uma porção de registros de uma tabela com mais de 100milhões de registros... isso me lembra um post do Fábio Telles: "Não use DELETE use INSERT" que ajudou muito para torná-la "menos pior".

Na época (2007/2008) essa melhoria ajudou pois desafogou bastante a carga de processamento desse ETL, porém com o passar do tempo e a tabela com dados financeiros crescendo constantemente, o ETL foi ficando cada vez mais oneroso chegando ao seu ápice (final de 2012) de ~8h de execução para processar ~8.5milhões de registros. Eu sei que esse número não é tão expressivo assim, mas a complexidade do processamento envolvido para fazer os cálculos de corrreção, juros e multas e as diversas configurações existentes para cada um justificam, de certa forma, todo esse tempode processamento, sem contar que o coitado do servidor ficava "imprestável".

Mas vejam bem, estou falando de dados *financeiros* que sob ponto de vista do negócio se fazem muito necessários para vários tipos de procedimentos e análises. Ainda existem instâncias com bases menores (outros clientes) que rodam esse ETL _diariamente_ por necessidade de negócio, mas nesse cliente em especial não é possível fazer isso, nem que eles quisessem  pois o tempo total de execução consume 1/3 de um dia, então os finais de semana são usados :-)

A hora da verdade ...

Após todos os problemas e sem muitas perspectivas, discutimos sobre a re-implementação da PL/pgSQL que executava o ETL, porém isso não é algo trivial, ainda mais em um ERP complexo onde tal iniciativa teria um impacto de grandes proporções visto que seria necessária uma re-modelagem em  alguns pontos criticos. Apesar de ser uma idéia interessante,  não existe tempo hábil para tanto, pois o cliente não pode mais aguardar uma solução, pois qto mais tempo demorar pior fica.

Como eu já tenho algum tempo de estrada com PostgreSQL e conheço bem a estrtutura do ETL e do ERP em questão, sugeri a equipe que eu poderia re-implementar o antigo shell script reaproveitando a PL/pgSQL do ETL existente (sem mudar regras de negócio), usando tecnologias e técnicas conhecidas. Então o que fiz:

1) Particionamento da tabela: esse foi o ponto fundamental, pois dividimos a grande tabela em outras menores tomando como base uma coluna que indica a "data" em que os dados financeiros foram calculados, e que o ERP usa constantemente para ler informações da mesma, portanto as queries iriam se beneficiar do recurso. Sobre esse assunto, além da documentação oficial, vcs podem dar uma olhada em alguns artigos recém lançados pelo Fábio Telles sobre esse assunto.

2) Implementação de um script PHP (não estou de sacanagem... é PHP mesmo, mas no console) que tivesse a habilidade de gerar processos filhos (fork) para processamento em paralelo, e para isso usei uma classe para realizar esse trabalho. Confesso que no inicio tive um certo receio em implementar essa rotina em PHP, inclusive cogitei a possibilidade de fazê-la em Perl, Python ou Ruby, mas como eu domino mais esta do que as outras e o tempo era curto implementei nela mesmo, e os resultados foram muito satisfatórios.

COPY no lugar de INSERT

A primeira coisa que fiz para continuar esse projeto foi *abolir* o INSERT... isso mesmo... não tem INSERT... vc deve estar pensando que estou maluco e se perguntando: "Tá e como adicionar linhas a uma tabela então?" R: usando COPY, ao invés de INSERT... na realidade implementei uma classe que armazena uma coleção (linhas) em memória, e quando eu preciso uso um método para persistir os dados em uma tabela usando COPY... simples assim... então o código usado para INSERT é algo do tipo:

DDL da tabela exemplo:

create table foo (
  bar integer
);

PHP:

$tabela = new PgCopy('foo');
for ($i=0; $i<10; $i++) {
    $tabela->bar = $i;
    $tabela->insertValue(); // adiciona em memória
}
$tabela->persist(); // realiza COPY dos dados em memória

Dividir para conquistar

Um dos problemas que tinhamos com o processo antigo era justamente que ele era linear, ou seja, um processo apenas com inicio, meio e fim. Então resolvi investir em programação paralela, dividindo o grande volume de registros a processar em vários trabalhos menores sendo capaz de executar alguns em paralelo, de acordo com o nro de núcleos do servidor.

Para tal atividades crio uma tabela que planeja a execução do trabalho, ou seja, cria lotes para que o script possa processar em paralelo, isso baseado em uma chave artifical (sequencial) que existe no modelo e facilitou a criação de trabalhos com lotes de N registros (neste caso usei 1000).

A tabela que crio para planejar a execução do ETL é algo do tipo:

create table jobs (
  id_start bigint,
  id_end bigint,
  status varchar,
  constraint jobs_pk primary key (id_start, id_end),
  constraint jobs_status_ck check (status in ('NOT RUNNING', 'RUNNING', 'FINISHED'))
);

Dessa forma utilizo o "id_start" e "id_end" para buscar as informações na origem em "lotes" de 1000 (mil) registros, e com isso consigo disparar vários processos em paralelo, e dessa forma conseguimos aproveitar melhor os recursos do servidor e assim agilizar bastante o processo.

Unlogged Tables são bem legais

Esse novo recurso presente apartir da versão 9.1 permite criar tabelas que não são escritas no log de transações (WAL), acelerando e muito a inserção de registros na mesma.

Assim cada processo disparado pelo script gera e escreve em uma unlogged table os dados, e junto com os processos de trabalho (workers) implementei um processo especial que serve com um tipo de coletor de lixo (garbage collector) para ir gradativamente lendo os lotes processados (unlogged tables geradas) e inserindo (com copy claro) na partição de destino.

Com essa estratégia posso ter um certo nível de escala na escrita pois consigo separar as tabelas em tablespaces distintas. Claro que se algum imprevisto ocorrer, tipo um desligamento não previsto do servidor, o próprio script tem habilidade de detectar essa situação e fazer uma limpeza geral antes de inicar um novo processo, até mesmo porque as unlogged tables tem seu conteúdo eliminado nessas situações, e não queremos perder parte dos registros não é mesmo... :-)

Finalizando...

Resumindo o que fiz foi:
- Particionar uma tabela grande em outras menores
- Planejar o processamento dividindo em lotes menores para poder fazer processamento paralelo
- Utilizar unlogged tables para receber os dados oriundos dos lotes que são processados em paralelo
- Implementar um processo que irá ler os lotes já processados e inserir os registros na partição de destino.

Existem outras coisas que foram feitas para melhorar o desempenho, tipo desligar o autovacuum nas tabelas, aumentar o work_mem, criar índices necessários ao final do processamento, e outros que podem ser feitos e que vcs podem visualizar neste post do Fábio.

Bom, se vc chegou até aqui então obrigado pela paciência e se quiser mais informações fico a disposição.


---
Fabrízio de Royes Mello
fabriziomello [at] gmail.com

PGBR2013 - Chamada de Trabalhos

Pessoal,

Está aberta a chamada de trabalhos para o PGBR2013. Vamos lá, se inscreva... estamos ansiosos para saber o que você tem feito com o PostgreSQL... nos encontramos em Porto Velho/RO de 15 a 17 de agosto de 2013.

--
Fabrízio de Royes Mello
fabriziomello [at] gmail.com

Patch sobre CREATE SCHEMA IF NOT EXISTS aceito para o PostgreSQL 9.3devel

Após algumas discussões [1] meu patch para adicionar a cláusula "IF NOT EXISTS" no "CREATE SCHEMA" foi aceito e efetuado o commit na branch master do git oficial do PostgreSQL [2] pelo commiter Mr. Tom Lane.

Agora na 9.3 poderemos usar a cláusula "IF NOT EXISTS" no "CREATE SCHEMA" para que não gere um erro (cancelando transação atual) caso o esquema que está sendo criado já exista, por exemplo:

BEGIN;
  CREATE SCHEMA IF NOT EXISTS foo;
  CREATE TABLE foo.bar();
COMMIT;

O exemplo acima caso o esquema "foo" já exista não será gerado um erro e a transação irá prosseguir normalmente. O comportamento é similar ao já existente "IF NOT EXISTS" do "CREATE TABLE" [3].

[1] https://commitfest.postgresql.org/action/patch_view?id=907
[2] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fb34e94d214d6767910df47aa7c605c452d11c57
[3] http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html

---
Fabrízio de Royes Mello
fabriziomello [at] gmail.com

Instalar/Configurar "Debugador" de PL/pgSQL (pldebugger) no PostgreSQL 9.1 usando Ubuntu

O passo-a-passo abaixo foi realizado no Ubuntu 10.04LTS com o PostgreSQL 9.1 instalado via ppa do Martin Pitti, mas creio que pode ser executado tranquilamente utilizando a última versão LTS do Ubuntu, a 12.04LTS.

1) Download dos fontes do PostgreSQL

$ wget -c http://ftp.postgresql.org/pub/source/v9.1.3/postgresql-9.1.3.tar.bz2


2) Precisamos instalar ferramentas necessárias para compilação da extensão

$ sudo apt-get update
$ sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libssl-dev git-core


3) Compilar o PostgreSQL e a extensao pldebugger

$ tar jxvf postgresql-9.1.3.tar.bz2
$ cd postgresql-9.1.3
$ ./configure --prefix=/usr --includedir=/usr/include --mandir=/usr/share/man --infodir=/usr/share/info --sysconfdir=/etc --localstatedir=/var --libexecdir=/usr/lib/postgresql-9.1 --srcdir=. --mandir=/usr/share/postgresql/9.1/man --docdir=/usr/share/doc/postgresql-doc-9.1 --datadir=/usr/share/postgresql/9.1 --bindir=/usr/lib/postgresql/9.1/bin --libdir=/usr/lib/postgresql/9.1/lib --includedir=/usr/include/postgresql/ --enable-integer-datetimes --with-openssl
$ make
$ cd contrib
$ git clone git://git.postgresql.org/git/pldebugger.git
$ cd pldebugger
$ make
$ sudo make install

Obs: lembrando que, apesar de extenso, o "./configure" deve ser todo escrito na mesma linha

4) Após compilar e instalar a extensao pldebugger precisamos ativa-a no PostgreSQL

$ sudo vim /etc/postgresql/9.1/main/postgresql.conf

alterar
 
  #shared_preload_libraries = ''

para
 
  shared_preload_libraries = '$libdir/plugin_debugger'
 

5) Reiniciar PostgreSQL

$ sudo /etc/init.d/postgresql restart 9.1


6) Criar a EXTENSAO "pldbgpapi" na(s) base(s) de dados que deseja fazer debug de pls

$ psql -U postgres bdteste
psql (9.1.3)
Digite "help" para ajuda.


bdteste=# CREATE EXTENSION pldbgapi;
CREATE EXTENSION
bdteste=#


7) Pronto, agora vc pode usar o PGAdmin para "debugar" suas funções PL/pgSQL



---
Fabrízio Mello
fabriziomello [at] gmail.com

Vídeo com atividade do repositório GIT do PostgreSQL nos últimos meses

Pessoal,

Montei um pequeno vídeo [1] que mostra a atividade do repositório GIT do PostgreSQL nos últimos meses.

Para gerar o vídeo usei o Gource [2].

[1] http://www.vimeo.com/30394945

[2] http://code.google.com/p/gource/

---
Fabrízio Mello
fabriziomello [at] gmail.com

PGBR2011 - Chamada de Trabalhos

Pessoal,

A chamada de trabalhos do PGBR2011 está aberta.

Vejam mais informações interessantes de como participar no excelente post do Fábio Telles.

---
Fabrízio Mello
fabriziomello [at] gmail.com

PGDay/RS 2011 - Targettrust irá Sortear 1 (um) Curso de PostgreSQL (20h)

Durante o PGDay/RS 2011 a T@rgetTrust, empresa especializada em treinamentos, irá sortear 1 (um) curso, a escolha do sorteado, da sua Formação PostgreSQL:

• PostgreSQL Fundamentos
• PostgreSQL Procedural
• PostgreSQL Administração
• PostgreSQL Administração Avançada

Mais informações acesse o site do evento em:

http://www.postgresql.org.br/eventos/pgday/rs

Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com

PGDay/RS 2011 em Porto Alegre/RS

O que é?

Postgres ou PostgreSQL é um projeto de Sistema Gerenciador de Banco de Dados open-source que foi iniciado em 1986, na Universidade de Berkeley, na Califórnia.

PGDay, ou Dia do Postgres, é um evento não tão formal quanto ao PGBR (Conferência sobre PostgreSQL no Brasil) e de caráter regional, podendo ser realizado com poucas pessoas em qualquer/vários estados do País.



Quais são os objetivos?

É uma chance para que acadêmicos, DBAs e desenvolvedores Brasileiros de PostgreSQL aprendam, troquem experiências, exponham casos de sucessos, desafios e fortifiquem sua rede de relacionamento profissional.


Qual o público alvo?

Dentre o público esperado estão:

• Administradores de Bancos de Dados;
• Acadêmicos de Cursos Técnicos ou Universidades;
• Desenvolvedores de Softwares;
• Organizações governamentais;
• Entusiastas de Software Livre;

Data e Local

O evento será dia 19 de Agosto de 2011, no Auditório do Bloco D da Uniritter localizado na Rua Orfanotrófio, 555, bairro Alto Teresópolis CEP: 90840-440 em Porto Alegre, RS.


Inscrições, Palestras, Atividades e mais informações veja em:
http://www.postgresql.org.br/eventos/2011/pgday/rs


Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com

PGBR2011 - Conferência Nacional PostgreSQL (3-4 Novembro, São Paulo)

O PGBR (antes conhecido como PGCon Brasil) é o maior evento sobre PostgreSQL das Américas: em 2009 e 2008, o evento trouxe mais de 300 profissionais de TI e, em 2007, mais de 200. Em 2011, serão 3 salas simultâneas com tutoriais, palestras e mesas de alto nível, contando com desenvolvedores nacionais e internacionais do PostgreSQL além de profissionais renomados no mercado brasileiro.


Venha conhecer de perto uma das comunidades de Software Livre que mais cresce no Brasil e no mundo que conta com o suporte de empresas de grande porte como CAIXA, Skype, BASF e Cisco. Conheça alguns dos maiores casos de sucesso brasileiros em órgãos públicos e privados, as novidades da versão 9.1 e o que está previsto para a versão 9.2 do PostgreSQL. Você terá a oportunidade também de conhecer técnicas avançadas de montitoramento, ajustes de performance, técnicas de replicação, migração, alta disponibilidade e muito mais.


Mais informações no sítio do evento: http://pgbr.postgresql.org.br


Aproveite também e preencha a nossa pesquisa sobre uso do PostgreSQL no Brasil:
https://spreadsheets.google.com/viewform?formkey=dFNOS0pjUFp3MFM0Y0xWT1RIWUZfRGc6MA


Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com

Vaga DBA PostgreSQL em Porto Alegre/RS

Pessoal,

Estamos com uma vaga para DBA PostgreSQL em Porto Alegre/RS, com as seguintes requisitos:

ATRIBUIÇÕES:

1. instalar/configurar Linux para posterior instalação do PostgreSQL
2. instalar/configurar o PostgreSQL na empresa (produção, desenvolvimento e testes) e nos clientes (produção e testes)
3. implantação e manutenção de políticas e rotinas de backup das bases de dados da empresa e dos clientes (scripts de backup padronizados)
4. implantação e manutenção de polĩticas e rotinas de segurança de dados
5. monitorar servidores PostgreSQL da empresa e dos clientes (coletar informações e estatísticas para geração de base de conhecimento dos clientes)
6. atender chamados dos clientes por eventuais problemas relacionados ao servidor de Banco de Dados PostgreSQL
7. apoio aos programadores na criação de SQLs complexos, bem como na construção de procedimentos armazenados e funções (stored procedures e functions)
8. apoio aos programadores na modelagem física de banco de dados
9. realizar migrações de versão do PostgreSQL
10. sugerir boas práticas de uso de SQL e Modelos de Dados a equipe de desenvolvimento
11. capacitação da equipe de desenvolvimento da empresa no uso de novos recursos oferecidos pelo PostgreSQL
12. otimização de performance (tunnig) do PostgreSQL e sistema operacional (Linux)

CONHECIMENTOS:

1. PostgreSQL
2. Linux
3. Shell Script
4. Programação PHP (desejável)

Interessados enviar Curriculum por e-mail para curriculos [at] dbseller.com.br que após análise inicial dos mesmos entraremos em contato para agendar entrevista.

Cordialmente,

Fabrízio de Royes Mello

fabriziomello [at] gmail.com

Função "array_diff" em PostgreSQL

Estava realizando algumas queries aqui no meu trabalho e tive a necessidade de uma função que retornasse um array contendo os elementos de um determinado array e não estão presentes em outro, igual ao array_diff do PHP [1].

Para resolver esse pequeno problema bastou um SELECT, vejam a implementação da função "array_diff" para PostgreSQL abaixo:

CREATE OR REPLACE FUNCTION array_diff (anyarray, anyarray) RETURNS anyarray

AS $$

  SELECT array(

    SELECT $1[s.i]

      FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)

     WHERE NOT $1[s.i] = ANY($2)

  );

$$

LANGUAGE sql;

postgres@bdteste=# SELECT array_diff(array[1, 2, 3], array[1, 4, 3]);

 array_diff

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

 {2}

(1 row)

postgres@bdteste=# SELECT array_diff(array[1, 4, 3], array[1, 2, 3]);

 array_diff

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

 {4}

(1 row)

Espero ter ajudado de alguma forma.

[1] http://br.php.net/manual/en/function.array-diff.php

Cordialmente,

Fabrízio de Royes Mello

fabriziomello [at] gmail.com

CONSEGI 2010

Nos dias 18, 19 e 20 de agosto/2010 ocorreu o evento denominado CONSEGI2010, onde tive oportunidade de participar como palestrante/instrutor em algumas atividades relacionadas ao PostgreSQL.

Abaixo seguem os links para download dos arquivos da palestra de das oficinas que ministrei:

Dia 19/08 - Palestra: Database Refactoring com PostgreSQL

Dia 20/08 - Oficina: PostgreSQL Básico

Dia 20/02 - Oficina: PostgreSQL Avançado

Obrigado a todos que estiveram presentes prestigiando o evento e as atividades que exerci.

Cordialmente,

Fabrízio de Royes Mello

fabriziomello [at] gmail.com

PostgreSQL na Memória RAM (In-Memory Database)

Recentemente (dia 24/06/2010) em seu blog, replicado no planet-postgresql, o Sr. Robert Haas postou o artigo denominado "PostgreSQL as an In-Memory Only Database".

Baseado em seu relato e no abordado na lista pgsql-performance montei este pequeno tutorial para exemplificar na prática o que foi discutido, ou seja, uma forma fácil e rápida de criar o seu próprio "PostgreSQL as an In-Memory Database".

Estou utilizando o Ubuntu 9.04 e PostgreSQL 8.3 para executar as atividades:

1) Montando partição em memória com tmpfs:

$ sudo -s

$ mkdir /mnt/in_memory

$ mount -t tmpfs -o size=2G,nr_inodes=8k,mode=0700 tmpfs /mnt/in_memory/


2) Criando cluster na partição criada:

$ chown -R postgres. /mnt/in_memory/

$ su - postgres -c "/usr/lib/postgresql/8.3/bin/initdb -D /mnt/in_memory"


3) Ajustando configurações do cluster (conforme recomendações do artigo citado):

$ vim /mnt/in_memory/postgresql.conf

fsync=off
synchronous_commit=off
full_page_writes=off
bgwriter_lru_maxpages=0

Obs: caso vc já tenha algum processo do PostgreSQL executando na mesma máquina desse teste então vc deve verificar se não será necessário modificar também a variável "port" do postgresql.conf, pois o padrão é 5432.


4) Iniciando processo servidor:

$ su - postgres -c "/usr/lib/postgresql/8.3/bin/pg_ctl -D /mnt/in_memory -l /mnt/in_memory/postgres.log start"


5) Verificando se o cluster está funcionando:

$ ps ax | grep post
19614 pts/3 S 0:00 /usr/lib/postgresql/8.3/bin/postgres -D /mnt/in_memory
19653 ? Ss 0:00 postgres: writer process
19654 ? Ss 0:00 postgres: wal writer process
19655 ? Ss 0:00 postgres: autovacuum launcher process
19656 ? Ss 0:00 postgres: stats collector process

$ /usr/lib/postgresql/8.3/bin/psql -U postgres -p 5437 -l
Lista dos bancos de dados
Nome | Dono | Codificação
-----------+----------+-------------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 registros)

Acredito que neste ponto você já esteja com o seu PostgreSQL na memória pronto e funcional.

Lembre-se que o mesmo está "literalmente" na memória RAM, portanto ao desligar o micro o seu cluster inteiro será perdido, por isso o seu uso fica limitado como um Cache ao invés do uso do memcached ou alternativas NoSQL, porém com todos recursos disponíveis no PostgreSQL.


Por favor reportem problemas com esse mini-tutorial e/ou críticas e sugestões.


Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com

PostgreSQL 8.2.x em Ubuntu > 8.04

Como é de conhecimento de todos as versões do Ubuntu superiores a 8.04 (hardy) não possuem em seus repositórios padrões o PostgreSQL 8.2.x, então o caminho natural para que seja feita a instalação é download dos fontes, compilar e instalar... para quem tem bastante intimidade com Linux + PostgreSQL essa é uma atividade corriqueira e que normalmente é realizada em ambientes de produção.

Porém tenho recebido de clientes e amigos questionamentos de como instalar, de maneira mais simples, o PostgreSQL 8.2.x em Ubuntu > 8.04, pois bem então ai vai a dica.

No console, como usuário root, execute:

$ apt-get update

$ echo "deb http://archive.ubuntu.com/ubuntu/ hardy main universe" > /etc/apt/sources.list.d/ubuntu-8.04.list

$ apt-get update

$ apt-get install postgresql-8.2

$ rm -f /etc/apt/sources.list.d/ubuntu-8.04.list

$ apt-get update


Com isso basta executar um pg_lsclusters ou um dpkg --list 'postgresql*' no console que poderão verificar que a versão 8.2.7 estará instalada.

Um pequeno contratempo em relação a essa abordagem é que teremos a versão 8.2.7 instalada e atualmente já temos disponível a 8.2.16, mas é possível sim atualizar a versão através da compilação pelos fontes mantendo toda a infra-estrutura do Ubuntu com o postgresql-common, mas isso fica para um próximo post.

Por favor reportem possíveis problemas com esse mini-tutorial.


Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com

PostgreSQL 8.5 vs 9.0

Não é oficial ainda mas parece que não teremos mais uma versão 8.5 do PostgreSQL... a nova versão será 9.0 conforme discussão na lista de desenvolvimento do mesmo [1].

O motivo dessa mudança é o grande número de novas funcionalidades que irá incorporar a nova versão do PostgreSQL... isso é muito interessante pois a evolução do "elefantinho" está cada vez mais acelerada...

Vida longa ao PostgreSQL!!!


[1] http://archives.postgresql.org/pgsql-hackers/2010-01/msg02056.php


Cordialmente,

Fabrízio de Royes Mello
fabriziomello [at] gmail.com