Versão 8.3 será bem mais rápida: primeiro benchmark...

A versão 8.3 do PostgreSQL apresenta uma série de alterações para aumento desempenho. Espera-se que o banco seja claramente mais rápido que as suas versões anteriores. O blog do Stefan Kaltenbrunner fez um benchmark da versão 8.2 com e sem tuning de performance com a 8.3 versão beta 4 com e sem tuning. Os resultados são animadores.

Confira os resultados e não deixe de testar a nova versão você mesmo para questionar ou postar os seus comentários:

http://www.kaltenbrunner.cc/blog/index.php?/authors/1-Stefan-Kaltenbrunner


A figura abaixo deixa uma ótima expectativa quanto aos ganhos de velocidade na próxima versão.

Os Comandos EXECUTE e DEALLOCATE

Os Comandos EXECUTE e DEALLOCATE são utilizados respectivamente para executar e desalocar da memória prepared queries.

Sintaxe - EXECUTE:

EXECUTE name [ (parameter [, ...] ) ]

Exemplos - EXECUTE:

1 - EXECUTE sem parâmetros.

EXECUTE consulta_atores;

2 - EXECUTE com um parâmetro.

EXECUTE consulta_cinema (1);

3 - EXECUTE com mais de um parâmetro.

EXECUTE consulta_cinema_intervalo (2,3);

Sintaxe - DEALLOCATE:

DEALLOCATE [ PREPARE ] nameExemplos - DEALLOCATE:

Exemplos - DEALLOCATE:

1 - Deallocate com a cláusula PREPARE opcional.

DEALLOCATE PREPARE consulta_atores;

2 - Deallocate com a sintaxe padrão.

DEALLOCATE consulta_cinema_intervalo ;

O Comando Prepare

Este comando é utilizado para a criação de Prepared Queries no PostgreSQL.

A execução da consulta é feita com o comando EXECUTE e a liberação da memória ocupada pela Prepared Query é feita com o comando DEALLOCATE.

Caso não se utilize DEALLOCATE, a consulta será perdida após o término da sessão.

Sintaxe:

PREPARE name [ (datatype [, ...] ) ] AS statement

Onde:

datatype - tipo de parâmetro recebido pela consulta preparada. Pode haver zero, 1 ou N parâmetros fornecidos através do comando EXECUTE.

Exemplos:

1 - Comando Prepare simples

PREPARE consulta_atores AS
SELECT * FROM cinema, cinema_atores WHERE cinema.id = cinema_atores.id_filme;

2 - Comando Prepare com um parâmetro

PREPARE consulta_cinema (integer) AS
SELECT * FROM cinema WHERE cinema.id = $1;


3 - Comando Prepare com mais de um parâmetro

PREPARE consulta_cinema_intervalo (integer, integer)AS
SELECT * FROM cinema WHERE cinema.id >= $1 AND cinema.id <= $2 ;


Prepared Queries com PostgreSQL

Prepared Queries são consultas preparadas, um recurso que pode ser empregado para aumentar o desempenho das aplicações com PostgreSQL. Uma consulta preparada é processada, gerando um plano de execução que pode ser executado várias vezes em uma sessão, com ganho de performance.

Não é possível compartilhar prepared queries entre várias sessões do PostgreSQL, no entanto cada sessão pode definir suas Prepared Queries.

Principais comandos relacionados:
- Prepare
- Execute
- Deallocate

Quarta turma do "Meu Curso de PostgreSQL"

Terminou semana passada a quarta turma do "Meu Curso de PostgreSQL", aqui no trabalho. Eu sou o instrutor e trabalho com material próprio. Espero que venham novas turmas!

- 1a turma: 24h - Brasília - Agosto de 2007
- 2a turma: 40h - Fortaleza - Novembro de 2007
- 3a turma: 40h - Salvador - Dezembro de 2007
- 4a turma: 40h - Recife - Dezembro de 2007

FISL 2008 - Submissão de Palestras Prolongada!

Novas submissões de palestras podem ser feitas até 11/01/2008! São aceitas palestras sobre software livre em geral, incluindo o PostgreSQL.

Site de submissão de trabalhos.

Submeta sua palestra: 9º Fórum Internacional Software Livre - FISL

Já estão abertas as inscrições para o 9º Fórum Internacional Software Livre - FISL. São aceitas palestras em várias áreas, incluindo sobre o banco de dados PostgreSQL.

O evento se realizará nos dias 17, 18 e 19 de abril de 2008, no Centro de Eventos PUCRS, na cidade de Porto Alegre, Rio Grande do Sul. A submissão de propostas de palestras devem ser feitas até o dia 15/12/2007.

Não é necessário ter a palestra pronta para que a mesma possa ser inscrita. Basta um tema e algumas informações básicas.

Site de submissão de palestras.

Terceira Turma do "Meu Curso de PostgreSQL"!

Terminou semana passada a terceira turma do "Meu Curso de PostgreSQL", aqui no trabalho. Eu sou o instrutor e trabalho com material próprio. Espero que venham novas turmas!

- 1a turma: 24h - Brasília - Agosto de 2007
- 2a turma: 40h - Fortaleza - Novembro de 2007
- 3a turma: 40h - Salvador - Dezembro de 2007

Continue Testando! PostgreSQL 8.3 - Versão beta 4 disponível!

A versão beta 4 foi lançada sem alterações nas funcionalidades, mas com correções importantes. Os testadores estão se esmerando para produzir uma versão estável e com o mínimo de falhas.

Os links para quem quiser colaborar nos testes estão no post abaixo.

PostgreSQL BETA 3 Disponível para Testes

A versão beta do PostgreSQL Beta 3 foi lançada e está disponível no site postgresql.org. As funcionalidades estão definidas desde primeiro de abril, e as implementações e testes tem sido feitos desde então.

Existe grande expectativa para o lançamento da versão 8.3 pelo acréscimo de novas funcionalidades e recursos para melhor desempenho de aplicações críticas.

• Última release beta: http://www.postgresql.org/ftp/source/v8.3beta
• Instalação Windows Beta: http://www.postgresql.org/ftp/binary/v8.3beta3/win32

PostgreSQL 8.3: Você Sabe o que são UUIDs?

Enquanto a versão 8.3 continua em testes, as novas funcionalidades ainda são desconhecidas para muitos dos usuários. Uma das inovações prevista é o uso de UUIDs.

O termo UUID (Universally Unique Identifier) nomeia identificadores que podem ser empregados por exemplo como substituição à chave primária das tabelas, servindo como alternativa aos campos tipo SERIAL do PostgreSQL e ao uso de OIDs.

Os OIDs (identificadores de objetos) do PostgreSQL não são recomendáveis para tabelas com muitos registros, mas o novo UUID promete suprir esta lacuna.

Também são chamados de GUIDs (Globally Unique Identifier), termo que também pode designar a implementação da Microsoft dos UUIDs em seus produtos.

A UUID é essencialmente um número de 16 bytes. Com 128 bits, a quantidade de registros que pode ser endereçada é enorme, viabilizando a sua utilização como identificador em grandas bancos de dados. O número de variações possíveis de UUIDs é de 2^16*8 = 2¹²⁸ = 256¹⁶ ou cerca de 3.4 × 10³⁸. Para dar uma idéia do tamanho deste número, basta afirmar que para se cobrir todas as possibilidades e exaurir um campo tipo UUID, teria de ser gerado 1 trilhão de UUIDs a cada nanosegundo por 10 bilhões de anos (o cálculo não é meu)!

Exemplo de UUID em Hehadecimal: 5f23d248-a353-4044-9e5a-04518ac20b94

Boas referências de código podem ser obtidas na Wikipedia. Outro site interessante é o GUID generator

PostgreSQL em Evento do SERPRO

Esta semana ocoreu o I Forum de Software Livre do SERPRO (Serviço Federal de Processamento de Dados), organizado pelo comitê de Software Livre da Regional Curitiba.

O PostgreSQL foi a principal tecnologia de banco de dados abordada no evento, que foi aberto à comunidade e contou com a presença de estudantes universitários e funcionários públicos de diversas instituições como SANEPAR e CELEPAR. O evento contou com palestrantes de peso, como Bruno Sousa, vulgo "Javaman", e Richard Stallman, pioneiro mundialmente conhecido da área de Software Livre.

Com relação ao PostgreSQL, foi apresentada por mim a palestra "Portabilidade com PostgreSQL", além de dois mini cursos sobre as funcionalidades deste banco de dados.

O impulso do Software Livre, e particularmente da utilização do PostgreSQL se intensificou no SERPRO após a ascenção de Marcos Mazoni ao cargo de Diretor-Presidente da empresa, o que gera indícios da adoção em massa desta tecnologia nos projetos da instituição.

Pesquisa Acadêmica com PostgreSQL

Chegou às minhas mãos um ótimo artigo relacionado com o PostgreSQL. Os pesquisadores Marcos Antonio Vaz Salles e Sergio Lifschitz estão trabalhando em configuração e otimização autônoma de banco de dados, entre outras áreas e este é apenas mais um dos trabalhos do grupo que envolve ainda outros pesquisadores (site do grupo de pesquisa).

O título, resumo, abstract e o e-mail dos autores se encontram abaixo.

Caso saibam de mais equipes de pesquisadores que trabalhem com PostgreSQL, e de bons artigos que possam ser divulgados, favor indicar!!!

Espera-se que a comunidade de desenvolvimento busque um contato maior com a academia, pois estes trabalhos poderiam ser incorporados ao PostgreSQL, mas por não serem conhecidos, ficam restritos à comunidade universitária.

Peço aos desenvolvedores brasileiros do PostgreSQL que entrem em contato, pois é uma forma de se incorporar a qualidade acadêmica brasileira no banco de dados que todos utilizamos.

Os autores que autorizaram este post salientaram que tudo o que é produzido é aberto, então vamos a eles!

==================================

Um Agente de Software para Criacão de Índices no PostgreSQL

Marcos Antonio Vaz Salles, Sergio Lifschitz

Departamento de Informática–Pontíficia Universidade Católica do Rio de Janeiro (PUC-Rio)

- sergio@inf.puc-rio.br
- Marcos Salles site pessoal com artigos e outras informações

Abstract. This paper briefly describes a prototype developed as part of a Masters thesis focused on autonomic index creation for database systems. The system is composed of a software agent that collects SQL commands submitted to the DBMS, analyzes which indexes are appropriate for those commands and automatically creates them. The analysis process uses server extensions that enable the creation of hypothetical indexes. The agent’s implementation was
done in C++ and integrated in the open source DBMS PostgreSQL. The server extensions made in PostgreSQL to simulate hypothetical index configurations were coded in C.

Resumo. Este artigo descreve brevemente um prototipo desenvolvido como parte de uma dissertação de mestrado focada na criação autônoma de índices em sistemas de bancos de dados. O sistema e composto de um agente de software que coleta comandos SQL processados pelo SGBD, analisa quais índices seriam adequados para estes comandos e os cria automaticamente. O processo de análise se utiliza de extensões codificadas no servidor para a criação de índices hipotéticos. A implementacão do agente foi realizada em C++ e integrada no SGBD de codigo fonte aberto PostgreSQL. As extensões feitas no PostgreSQL para simulação de configurações hipotéticas de índices foram escritas em C.

Aqua Data Studio - Boa Ferramenta Visual...

O Aqua Data Studio é uma ferramenta proprietária para criação e administração de Bancos de Dados. Suporta Oracle, DB2 UDB, SQL Server, Sybase ASE, Sybase Anywhere, Sybase IQ, Informix, MySQL e PostgreSQL.

Um aluno me apresentou e não é que adorei a ferramenta? Apesar de limitada, oferece mais facilidades gráficas que o PgAdmin III para os iniciantes. A versão completa é a 6.5, mas a versão 4.7.2 pode ser baixada e utilizada academicamente e para fins pessoais. O suporte é pago, claro!

O site da empresa é o: http://www.aquafold.com/

Pontos Fortes (Versão 4.7):
- Facilidades para importação e exportação de dados
- Rica interface visual com bons editores, assistentes e query builder
- Aceitação de múltiplos SGBDs, incluindo o PostgreSQL
- Suporte disponível (Pago)
- Backup (pelo script generator)
- Bastante utilizada. O site indica mais de100 instituições que utilizam a ferramenta, indicando que seu aprimoramento continuará por muito mais tempo.

Pontos Fracos (V. 4.7):
- Instalador muito grande (48 megas!).
- Possui um gerador de diagramas de entidade-relacionamento, mas que não é muito flexível. Também parece exigir que se crie o banco para gerar o diagrama, e não o sentido inverso.
- Recursos de comparação de bancos de dados não disponíveis na versão gratuita. (bom, eles tem que faturar, né?)

Veja algumas telas capturadas:

- Editor de Funções
- Query Analyser


- Query Builder


- Diagrama de Entidade-Relacionamento Gerado pela Ferramenta. Cortesia de Marcelo Robson e Vilani Queiroz (SERPRO).

Banner da PgCon: Divulgue!

Lançado o banner promocional da PgCon brasileira. Esteja livre para colocá-lo no seu site, blog ou comunidade!

A PgCon promete ser o principal evento de PostgreSQL no país. Mais informações no site do postgresql.org.br. Link à direita!

Segunda Turma do "Meu Curso de PostgreSQL"!

Começou esta semana a segunda turma do "Meu Curso de PostgreSQL", aqui no trabalho. Eu sou o instrutor e trabalho com material próprio. Espero que venham novas turmas!

- 1a turma: 24h - Brasília - Agosto de 2007
- 2a turma: 40h - Fortaleza - Novembro de 2007

Criação de Índices com Procedures no PostgreSQL...

Vamos ver se consigo ajudar. Esta é uma dúvida do Juliano Fischer:

"Ola, estou com um problema em uma implementação e creio que pode me ajudar.
Preciso criar um subprograma armazenado que crie um indice,se já existir, informar o usuario, cado contratio, crie um indice.

criaIndice(campo,tabela);"

Não é tão difícil! A solução está abaixo, mas cuidado pois o meu código pode e deve ser melhorado. Nomes de parâmetros, métodos e variáveis e bons comentários podem ser adicionados.

Além disso, o exemplo só verifica um campo e exige que se tenha cuidado na nomenclatura dos campos, pois utiliza a cláusula LIKE.

1 - Recuperar informações dos índices.

A dica é utilizar a pg_indexes, visão de sistema mais amigável para saber se o índice existe:

select * from pg_indexes;

2 - Criar uma função parametrizada que indica se os índices existem. Observem que utilizei a linguagem SQL, e não Pl/ PgSql. Poderia utilizar qualquer outra liguagem, mas a lógica seria similar.

-- Verifica se existe o índice. Retorna ZERO se o índice não for encontrado
CREATE OR REPLACE FUNCTION retindex(in tabela varchar, in campo varchar) RETURNS bigint AS $$
select count(*) from pg_indexes where tablename = $1 and indexdef like '%' || $2 || '%'
$$
LANGUAGE SQL;


3 - Criar função parametrizada que cria o índice caso o mesmo não exista, e que utilize a função anterior. Linguagem Pl/ PgSQL.

-- Verifica e cria novo índice se for o caso
CREATE OR REPLACE FUNCTION criaindex (tabela varchar, campo varchar) RETURNS VARCHAR AS $$
DECLARE
func_cmd VARCHAR;
BEGIN
if retindex($1,$2) > 0 then
RETURN 'OK';
else
func_cmd := 'CREATE INDEX ' || $1 || '_IDX ON ' || $1 || ' (' || $2 || ')';
EXECUTE func_cmd;
RETURN func_cmd;
end if;
END;
$$ LANGUAGE plpgsql;

4 - Testando tudo

select retindex ('teste','c1');
select criaindex ('teste', 'c1');

Companheiros, quem pode dar um código que melhore ainda mais esta criação de índices com função armazenada? Juliano, resolvi seu problema?

A Função DATE_PART

Esta utilíssima função retorna parte de uma data, hora ou timestamp para manipulação. É bastante prática e utilizada nas aplicações PostgreSQL. Equivale à função EXTRACT.

Aceita como parâmetros um texto com a parte da data a ser extraída e outro parâmetro com a data/ hora/ timestamp da qual será extraída parte.

Sintaxe:
1- date_part(text, timestamp) double precision
2 - date_part(text, interval) double precision

Exemplos:

1 - Date_part com current_date. Retornando dia da semana, dia, mês e ano.

SELECT date_part('dow', current_date); --DAY OF WEEK
SELECT date_part('day', current_date);
SELECT date_part('month', current_date);
SELECT date_part('year', current_date);

2 - Data_part com current_date retornando dia da semana, dia, mês e ano na mesma linha.

SELECT date_part('day', current_date) as dia, date_part('month', current_date) as mês, date_part('year', current_date) as ano;

3 - Data_part com current_time.

SELECT date_part('hour', current_time);
SELECT date_part('minute', current_time);
SELECT date_part('second', current_time);
SELECT date_part('milliseconds', current_time);

4 - Data_part com current_time retornando hora, minuto, segundo e milésimos de segundo em uma só linha.

SELECT date_part('hour', current_time), date_part('minute', current_time), date_part('second', current_time), date_part('milliseconds', current_time);

5 - Date_part com current_timestamp.

SELECT date_part('dow', current_timestamp);
SELECT date_part('day', current_timestamp);
SELECT date_part('month', current_timestamp);
SELECT date_part('milliseconds', current_timestamp);

6 - Date_part com current_timestamp, retornando dia da semana, dia, mês e os milisegundos da hora corrente.

SELECT date_part('dow', current_timestamp), date_part('day', current_timestamp), date_part('month', current_timestamp), date_part('milliseconds', current_timestamp);

7 - Date_part com conversão da string para campos data/ hora/ timestamp.

SELECT date_part('day', date '12-12-2007');
SELECT date_part('month', date '12-12-2007');
SELECT date_part('hour', time '02:12:45');
SELECT date_part('minute', time '02:12:45');
SELECT date_part('year', timestamp '12-12-2007 02:12:45');
SELECT date_part('hour', timestamp '12-12-2007 02:12:45');

8 - Sintaxes utilizando interval.

SELECT date_part('year', interval '4 years 3 months');
SELECT date_part('month', interval '4 years 3 months');
SELECT date_part('hour', interval '12 hour 10 minutes 12 seconds');
SELECT date_part('minute', interval '12 hour 10 minutes 15 seconds');
SELECT date_part('second', interval '12 hour 10 minutes 15 seconds');

Ótimo Livro Brasileiro de PostgreSQL!!!

O livro "Dominando o PostgreSQL", de Jorge Luiz Gonzaga, cobre as principais funções do banco, desde a instalação até a construção de funções e triggers. É uma ótima opção para quem utiliza versões 8.X. Não costumo comentar livros, mas ele tem se revelado uma ótima fonte.

Ficha técnica:
- Dominando o PostgreSQL
Autor: Gonzaga, Jorge Luiz
Editora: Ciência Moderna
ISBN: 9788573935592
Idioma: Português
Páginas: 244
Publicação: 2007
Edição: 1
Encadernação: Brochura
Peso: 512.00 gramas (!)

Pontos fortes:
- Grande Abrangência. São 36 capítulos falando de quase todos os aspectos desta tecnologia, incluindo instalação Linux e Windows.
- Suporte à linguagem SQL bastante detalhado, com muitos exemplos de sintaxes alternativas que aumentama produtividade.
- Cobre as versões 8.x, o que a maioria dos livros nacionais ainda não faz.

Pontos fracos:
- Ausência de índice remissivo (aquele que fica no fim da página)
- Certos temas mereciam maior detalhamento, como o WAL - Write-Ahead Log.
- O livro como um todo poderia ter textos mais detalhados. Os capítulos são muito curtos e os textos diretos demais, resultando em poucas páginas por capítulo.

Conclusão: é um ótimo livro. Não conheço melhor livro em português. E você?

PostgreSQL 8.3 Beta Liberado para Testes

Não recomendada para uso comercial, estas versão aguarda que você faça seu download e testes. Ah, você já pode baixar na página do programa de beta testes do PostgreSQL: Seja um Beta Tester.

Novas funcionalidades:
- Melhorias de desempenho como alterações para o Distributed Checkpoint, Commit Assíncrono entre outros recursos
- TSearch2 para busca em texto passa a ser integrada ao código e sintaxe mais amigável. (Possível tema para próximos posts)
- SQL:XML syntax. (Precisa de comentário? Tenho de testar isso logo!!!)
- Logging para arquivos CSV carregáveis no banco.
- Rebuilding automático de planos de execução de consultas em cache. (ótima idéia de melhoria de desempenho)
- ENUMs, UUIDs and arrays of tipos complexos.
- Suporte de autenticação para GSSAPI e SSPI. (o que diabos é isso?)

Podemos colaborar bastante baixand, testando o beta e encaminhando as alterações.

Documentação completa das novidades nas Release Notes.

A Função TIMEOFDAY

A função timeofday() retorna um texto com a data e hora. Não é muito utilizada.

Exemplos:

1 - Sintaxe básica.

select timeofday();
Retorno: "Thu Oct 04 12:33:55.953000 2007 BRT"

Função NOW

Função equivalente a CURRENT_TIMESTAMP. Pode ser utilizada em consultas, alterações, inserções e exclusões de registros.

Exemplos:

1 - Sintaxe básica.

select now();
Retorno: "2007-10-04 11:24:15.718-03"

2 - Consulta com outra função.

select now(), current_timestamp;
Retorno: "2007-10-04 11:25:11.625-03";"2007-10-04 11:25:11.625-03"

3 - Uso de NOW() em inserção.

insert into testedata values (current_date, current_time, now());

4 - Uso de NOW() em consulta.

select * from testedata where ctimestamp = now();

CURRENT_DATE, CURRENT_TIME e CURRENT_TIMESTAMP

São funções que retornam respectivamente a data atual, a hora corrente e o timestamp (data + hora) registrado no relógio do computador. Podem ser utilizadas na inserção, atualização, consulta e exclusão de dados.

Exemplos:

1 - Retornando a data atual.

SELECT current_date;

2 - Retornando a hora atual.

SELECT current_time;

3 - Retornando o timestamp corrente.

SELECT current_timestamp;

4 - Consulta retornando current_date, current_time e current_timestamp.

SELECT current_date, current_time, current_timestamp;

5 - Utilização das funções para inserção, alteração e exclusão de dados.

CREATE TABLE testedata(
cdata DATE,
ctime TIME,
ctimestamp timestamp);

insert into testedata values (current_date, current_time, current_timestamp);

insert into testedata values ('2007-10-21', '11:04:50.123', '2007-10-21 11:04:50.123');

update testedata set cdata = current_date;

delete from testedata where cdata <> current_date;

6 - Utilização das funções para consultas aos dados.

select * from testedata where ctime <> current_time;

Comandos de Manipulação de DATA/ HORA

O PostgreSQL manipula campos tipo data e hora com muita desenvoltura. Apresenta ainda o tipo de dados timestamp que engloba data e hora no mesmo campo. As principais funções de manipulação de datas são:

- current_date
- current_time
- current_timestamp
- now()
- timeofday()
- date_part
- date_trunc
- extract

Estas funções podem ser utilizadas na inserção, atualizações e exclusão dos dados, como será exemplificado nos próximos posts.

Site da PG Conference 2007 Funcionando!

O site da PG Conference 2007 do Brasil já está no ar.
As pré inscrições estão abertas, com um grande desconto (70%) aos que se anteciparem.

É só seguir o link abaixo:

PG Conference 2007

PostgreSQL - Versões Antigas Desativadas

Mensagem de Dave Page, do PostgreSQL Core Team, informa que as primeiras versões do PostgreSQL para Windows serão descontinuadas (8.0 and 8.1), sem a geração de novas atualizações e correções de bugs.

A razão desta descontinuidade seria a dificuldade de se corrigir certos problemas em todas as versões, processo que subtrai recursos do desenvolvimento de novas versões. Em certos casos a correção de bugs exigiria muito tempo e isso colaborou bastante para esta decisão.

Os usuários são aconselhados a migrar para a versão 8.2 e para a nova 8.3 cujo lançamento se aproxima. A versão 8.2 e as posteriores são consideradas maduras e devem durar tanto na plataforma Windows quanto as geradas para outros sistemas operacionais.

Os comandos para backup e recuperação podem ser utilizados para esta migração e você pode encontra-los nos posts anteriores.

O PostgreSQL não é perfeito. Nem o Oracle também...

O PostgreSQL não é perfeito. Nem o Oracle também. No blog do Telles foram colocados dois textos com as desvantagens de um banco sobre o outro. Vale a pena conferir.

Oracle tem 16 recursos que fariam falta ao PostgreSQL

PostgreSQL tem 17 recursos que fariam falta ao Oracle

Relatório do IDC Ignora PostgreSQL e MySQL!!!

Relatório recente do IDC sequer listou os bancos de Dados MySql e PostgreSQL no seu relatório "Worldwide RDBMS 2006 Vendor Shares: Preliminary Results for the Top 5 Vendors". O resultado é que os dados foram anexados à propaganda da Microsoft.

Como não há qualquer menção no texto a software livre, PostgreSQL ou MySQL, parece que a pesquisa ignorou a utilização destes programas pelas organizações. Na verdade, o texto do relatório coloca "Vendors" de software livre e não ferramentas, eliminando do escopo os programas livres para a mesma finalidade e apresentando uma versão deturpada do mercado.



Crítica adicional pode ser feita ao fato de serem apresentados apenas os TOP 5. Se o sexto maior vendedor de bancos de dados tiver uma boa solução ela sequer será mencionada. Só podem haver "TOP 5 VENDORS". Passando disto, é concorrência demais.

Consulte e tire suas conclusões.

Mas nem tudo são críticas e más notícias: o mercado cresceu mais de 14% no período, estimulando a contratação de bons profissionais para a gestãod e infra-estruturas de banco de dados.

Agora fiquei com uma dúvida: onde consultar o market share do PostgreSQL?

O Comando RESET

Este comando reverte as configurações que estejam sendo utilizadas no momento para os valores padrão. Equivale à execução de um comando "SET configuration_parameter TO DEFAULT".

Sintaxe:

RESET configuration_parameter
RESET ALL

ALL - Altera todos os valores de configuração para os valores padrão. Deve ser utilizado com parcimônia, pois pode causar efeitos colaterais alterando parâmetros que não necessitem atualização.

Exemplos:

1 - Exemplo que altera o estilo de data. O comando SHOW mostra o resultado.

show datestyle;
set datestyle to postgres,dmy;
show datestyle;
RESET datestyle;
show datestyle;

2 - Sintaxe com a cláusula ALL.

RESET ALL;

Post Número 51!!!

É uma grande satisfação ter chegado ao post 51.
Agora temos que ultrapassar a barreira dos 100!

A colaboração de todos é muito importante para isso!

O Comando SHOW

O PostgreSQL tem dúzias de variáveis de configuração que tem influência no comportamento do banco, em sua segurança e no desempenho. Acompanhar estes parâmetros de forma simples permite melhor controle do funcionamento do servidor.

O Comando SHOW é uma extensão do PostgreSQL que facilita este acompanhamento. Este comando permite que se visualize todos os valores das variáveis de configuração do seu banco de dados. O autovacuum está ativado? Qual é o estilo de data que está sendo utilizado no momento? Perguntas como essa são facilmente respondidas pela utilização deste comando.

Problemas conhecidos:
- Este comando não permite a cláusula WHERE. Desta forma recuperamos todas as dezenas de parâmetros ou apenas parâmetros específicos, um de cada vez.
- O comando não aceita recuperar uma lista de parâmetros definida pelo usuário (SHOW par1, par2,..., parN).
- Testes com os comandos SELECT, UNION e SELECT INTO não funcionaram, isto é, não é possível utilizar o SHOW com estes comandos.

Sintaxe:

SHOW name
SHOW ALL

Exemplos:

1 - Mostrando todos os parâmetros de configuração

SHOW ALL;

2 - Mostrando parâmetros específicos

SHOW array_nulls;
SHOW autovacuum;

O Comando ALTER INDEX

Este comando altera um índice, podendo mudar o nome, o tablespace e redefinir parâmetros do mesmo.

Dependendo da sintaxe, pode demandar um comando REINDEX para efetivar a alteração.

O tipo de índice (btree, hash, etc.) não pode ser alterado através do comando ALTER INDEX, assim como os campos indexados e outras características. Neste caso, o objeto índice deve ser excluído e recriado.

Sintaxe:

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

Exemplo:

0 - Índices utilizados no teste.

create index tstdel_ind on tstdel (cod);
create index tstdel_ind_date on tstdel (date);
create index tstdel_ind_coddate on tstdel (cod, date);

1 - Alteração de nome.

ALTER INDEX tstdel_ind_coddate RENAME TO tstdel_ind_coddata;
ALTER INDEX tstdel_ind_date RENAME TO tstdel_ind_data;

2 - Alteração de tablespace.

ALTER INDEX tstdel_ind SET TABLESPACE pg_default;

3 - Alteração de parâmetro de armazenamento, seguida por um REINDEX.

ALTER INDEX tstdel_ind SET ( fillfactor = 80 );
REINDEX INDEX tstdel_ind;

4 - RESET de alterações de parâmetro de armazenamento, seguido por um REINDEX.

ALTER INDEX tstdel_ind RESET (fillfactor);
REINDEX INDEX tstdel_ind;

Aberta a chamada de trabalhos para o PG Con Brasil!!!

Está aberta a Chamada de Trabalhos da PgCon Brasil 2007.
Mais informações no site do PostgreSQL.ORG.BR ou no e-mail que aparece abaixo:

=============================================

Já estão abertas as inscrições para a apresentação de trabalhos da comunidade no PostgreSQL Conference Brasil (PGCon Brasil). Os interessados devem enviar um e-mail para trabalhos_pgcon@postgresql.org.br contendo os dados a seguir:

* Título da palestra (até 50 toques)
* Assunto - Selecionar a partir da seguinte lista:
o Casos de Sucesso;
o Melhores práticas (configuração, organização, segurança, etc);
o Ajustes de desempenho (tuning);
o Administração de servidores PostgreSQL;
o Técnicas de migração para PostgreSQL;
o Funcionalidades específicas do PostgreSQL
* Resumo da palestra (até 250 toques)
* Principais tópicos abordados (até 250 toques)
* Palestrante(s) com mini currículo (até 250 toques por palestrante)

Observações:

* Os trabalhos devem ser enviados até 21/09/2007 e serão avaliados por uma comissão julgadora de desenvolvedores de PostgreSQL que escolherão até 8 trabalhos a serem expostos durante o PGCon Brasil.
* Os trabalhos que não forem enviados até 21/09/2007 não serão avaliados pela comissão e não participarão do PGCon Brasil.
* Será disponibilizado um projetor multimídia e um computador com uma distribuição linux padrão instalada. Qualquer recurso adicional necessário para a apresentação ficará sob inteira responsabilidade do palestrante.
* Serão selecionados trabalhos para ficarem em lista de espera em caso de desistência de algum trabalho selecionado.
* Os trabalhos terão duração máxima de 50 minutos incluindo o tempo para exposição e perguntas.

Meu Curso de PostgreSQL

Semana passada aconteceu a primeira turma do "Meu Curso de PostgreSQL".
Foi um desafio muito divertido no qual aprendi bastante.
Agradeço a presença e paciência dos alunos.

O Comando Drop Index

Remove um índice do seu banco de dados. Para executar o comando Drop Index, o usuário deve ser owner do objeto.

A remoção de índices relacionados com constraints como as chaves primárias vai causar erro. Neste caso deve ser empregado o comando ALTER TABLE DROP CONSTRAINT .

Sintaxe:

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

A cláusula IF EXISTS restringe a emissão de mensagens de erro caso os índices a serem excluídos não existam. O padrão é a emissão de erro ao se tentar excluir um índice inexistente.

CASCADE - Remove objetos que dependam do índice.

RESTRICT - Faz com que o índice não seja eliminado se existirem objetos filhos. é a opção padrão do PostgreSQL.

Exemplos:

1 - Exclusão de índice com IF EXISTS.

DROP INDEX IF EXISTS pais_cont_pkey_9;

O erro é convertido em NOTICE:

NOTICE: index "pais_cont_pkey_9" does not exist, skipping
Query result with 29 rows discarded.
Query returned successfully with no result in 16 ms.

2 - Exclusão de índice ind4.

DROP INDEX ind4;

PgCon 2007 - Palestras Confirmadas!

Antes mesmo do site da PgCon Brasil 2007 ser lançado, adiantamos aqui a lista com palestrantes confirmados. Boas novidades nacionais e Internacionais! Claro que pode acontecer algum imprevisto até lá, mas o evento promete palestras de peso e grandes encontros.

Josh Berkus - Desenvolvedor do time central do PostgreSQL;
Conheça o palestrante através desta entrevista:
Josh Berkus

Bruce Momjian - Desenvolvedor do time central do PostgreSQL;
O site da criança é simplesmente impressionante:
Bruce Momjian

Euler Taveira - Desenvolvedor nacional
Quem não o conhecia, passou a conhecer depois desta entrevista:
Euler Taveira

Diogo Biazus - Desenvolvedor nacional
Além de ser um desenvolvedor, ele é o contato regional do PostgreSQL.org no Brasil. É o homem que sabe das notícias antes da gente!

Em breve será divulgada a chamada de trabalhos. O prazo não deve ser tão generoso, então aconselho que todos os interessados comecem com antecedência suas pesquisas e preparação de material.

Comentários Sobre Indexação...

Seguem respostas, ao menos as que posso dar hoje, aos comentários do Telles. Você também pode postar os seus!

"Opa, muito bacana esta série sobre índices... este é um assunto que dá pano para manga! Algumas sugestões para explorar mais o
* Seria interessante fazer um EXPLAIN num SELECT de uma grande tabela para comparar o ganho de velocidade e volume em disco de diferentes valores de FILLFACTOR;"

Com certeza esse seria um bom teste a ser feito. Bom, fica na minha fila de coisas a fazer. Como esta lista cresce rápido...

"* Exemplificar o uso da cláusula CONCURRENTLY;"

A cláusula CONCURRENTLY aparece em um dos nossos exemplos, no entanto o texto explicando seu funcionamento pode ser melhorado.

O uso de CONCURRENTLY faz com que o banco de dados ignore que se está fazendo inserções, atualizações e exclusões durante a sua criação, fazendo com que a construção do índice seja mais rápida. No entanto impede que a criação do índice seja feita dentro de uma transação e o índice criado pode não estar 100% correto, em virtude das alterações concorrentes relaizadas durante sua criaçao, demandando um comando REINDEX.

Só utilize esta cláusula se lidares com grandes massas de dados em um sistema em que a performance seja crítica.

"* Apontar quando a utilização de um determinado índice é melhor (B-TREE, HASH, GIST e GIN);"

Nossa, devia ter comentado isso mesmo!
No PostgreSQL, a indexação pode utilizar recursos que reduzam a portabilidade. Caso o mesmo esquema de banco seja utilizado em vários outros SGBDs devem ser ignorados os índices GIST e GIN. Os índices HASH e BTREE têm implementação mais difundida em outras plataformas.

Sugestão:

BTREE - utilizar como padrão. Flexível e com bom custo benefício.

HASH - Utilizar apenas quando as consultas utilizando o operador de igualdade como condicional "=" estiverem lentas, caso não se precise logar a operação, pois operações com HASH não são armazenadas no log do PostgreSQL. O usuário deve lembrar que a reconstrução do índice em caso de falha deve ser disparada manualmente.
A rapidez do HASH é maior que a da árvore B, mas o memso demanda mais manutenção.

GIST - É um "índice programável" para tipos definidos pelo usuário. Não usar caso se deseje compatibilidade com outro banco. Também ainda é um recurso pouco empregado da ferramenta, a não ser que você instale algum módulo extra, que pode utilizá-lo sem que sequer saibamos.
Não consegui fazer funcionar nos meus testes. Acho que é questão de tempo. Aí eu faço um post só para o GIST.

GIN - É um índice muito recentemente adicionado ao banco, voltado para para tipos de dados em que uma chave ocorra mais de uma vez no banco de dados. Não usar caso se deseje compatibilidade com outro SGBD. Também ainda é um recurso pouco empregado da ferramenta, ainda mais por ser relativamente recente.
Achei difícil de utilizar e não consegui fazer funcionar nos meus testes. Acho que é questão de tempo. Aí eu faço um post só para o GIN. Alguém já está usando GIN?

ÍNDICES PARCIAIS - Fáceis de usar e úteis para otimizar certas consultas. Não usar caso se deseje manter compatibilidade para outros bancos.

"* Como criar índices que retornem a correta ordenação para pt_BR utilizando o OPERATOR CLASS;"

Ainda estou estudando isso. Está na minha lista, mas bem atrás.
Alguém conhece algum texto bom a respeito?

"Além disso, seria interessante utilizar uma nomeação para os índices mais consistentes, criar nomes como ind1, ind2 e ind3 não costuma ser muito recomendado."

MEA CULPA!!! Para os próximos exemplos pretendo utilizar NOMEOBJETOINDEXADO_SUFIXO. Minha dúvida é se compensa rever o post e alterar os exemplos...
Alguém sugere uma nomenclatura alternativa?

"Fica aí o desafio... o que você acha? Por último deixo aqui a recomendação de uma apresentação interesante do Sr. Josh Berkus chamada The Joy Of Index que é bem interessante."

Esse texto é realmente MUITO instrutivo.
Nele são detalhados alguns importantes sobre a indexação do PostgreSQL.
Com certeza vou reler mais de uma vez!

O Comando CREATE INDEX

Este comando cria índices relativos aos dados de uma tabela do PostgreSQL. O bom uso dos índices aumenta bastante o desempenho do acesso a dados, daí a importância do comando.

São objetos que ocupam espaço em disco e podem afetar negativamente o desempenho se mal empregados e em virtude disto devem ser devidamente empregados, gerenciados e mantidos. O comando REINDEX pode ser utilizado para reorganizar os índices mais defasados.

Sintaxe:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ]

- UNIQUE - Aceitação ou não de valores repetidos. O padrão é aceitar valores repetidos em um arquivo indexado, mas o uso de UNIQUE gera um erro nesta situação.

- CONCURRENTLY - O uso desta cláusula faz com que não sejam feitos bloqueios à inserção, exclusão e alteração de dados durante a criação do índice. O padrão para a criação de índices é o bloqueio destas operações visando garantir que o índice e seus dados estejam perfeitamente sincronizados, o que pode retardar a criação dos objetos. O comando CREATE INDEX pode ser utilizado em transações apenas se for empregado sem a cláusula CONCURRENTLY.

- Método de indexação - Tipo de índice. Pode ser btree, hash, gist ou gin. O padrão é a árvore B.

- O campo com os valores indexados pode ser na verdade uma expressão calculada utilizando-se uma ou mais colunas da tabela.

- Para cada coluna utilizada no índice deve ser fornecido OPERATOR CLASS, que é o operador a ser utilizado para cada tipo de dado. Normalmente o valor padrão para cada tipo de dado resolve a questão e não se precisa fornecer o OPERATOR CLASS.

- storage_parameter - Atualmente o storage parameter definido para índices é o FILLFACTOR. É uma variável que indica o quanto as folhas da árvore de índice serão utilizadas. Varia de 10 a 100 e seu valor padrão é 90. O valor 100 minimiza o gasto com armazenamento físico do índice, mas pode afetar negativamente o desempenho. O valor 10 privilegia o desempenho, mas requer maior espaço físico para a gravação do índice.

- Cláusula WHERE indicando se o índice é relativo a toda a tabela ou apenas a parte dela (índice parcial).

- Em que tablespace o índice será fisicamente armazenado

Exemplos:

1 – Criação de índice UNIQUE, isto é, sem valores repetidos. Tabela continente, campo nome, nome do índice ind1.

CREATE UNIQUE INDEX ind1 ON continente (nome);

2 – Criação de índice sem UNIQUE, isto é, com valores repetidos e com campo calculado. Ao invés de indexar o campo nome, indexará os valores do campo nome convertidos para minúsculas..

CREATE INDEX ind2 ON continente ((lower(nome)));

3 – Criação de índice UNIQUE com o storage_parameter FILLFACTOR apresentando valor 45.

CREATE UNIQUE INDEX ind3 ON continente (population) WITH (fillfactor = 45);

4 – Criação de índice em determinado tablespace.

CREATE INDEX ind4 ON continente(population) TABLESPACE tspteste;

5 – Criação de índice utilizando a cláusula CONCURRENTLY, com ganho de desempenho.

CREATE INDEX CONCURRENTLY ind5 ON continente (population);

6 – Definição de índice HASH.

CREATE INDEX ind6 ON continente USING hash (nome);

7 – Definição de índice com árvore B.

CREATE UNIQUE INDEX ind7 ON continente USING btree (nome) ;

8 – Definição de índice com UNIQUE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp1 ON continente ((upper(nome)));;

9 – Definição de índice com UNIQUE, BTREE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp2 ON continente USING btree ((cod * 100));

10 – Definição de índice com UNIQUE, BTREE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp3 ON continente USING btree ((cod + tamanho));

11 – Definição de índice parcial, empregando cláusula WHERE em campo numérico.

CREATE UNIQUE INDEX ind9 ON continente (population) WHERE population >10;

12 – Definição de índice parcial, empregando cláusula WHERE em campo alfanumérico.

CREATE UNIQUE INDEX ind10 ON continente (nome) WHERE nome like 'TEC%';

Indexação no PostgreSQL - II

O PostgreSQL oferece atualmente quatro tipos de indexação:

- B-Tree - Cria índices baseados em árvores B. É o tipo padrão empregado na criação de índices. Facilita consultas utilizando a igualdade de valores ou intervalos de valores (>= > = < <=).

- Hash - Índices Hashing organizam um banco de dados com base em funções. O índice hash funciona em comparações de igualdade (=). Em virtude desta limitação e do fato das operações com Índices Hash atualmente não serem logadas, demandando a reorganização manual de índices após falhas no banco, este índice tem o seu uso desencorajado. No entanto, o uso de índices hash geralmente apresenta resultados melhores que os feitos com B-tree.

- GiST - Índices GiST (Generalized Search Tree) oferecem uma infra-estrutura sobre a qual podem ser implementadas consultas. Geram uma árvore de pesquisa balanceada versátil similar a uma árvore B que pode ser utilizada com estruturas de dados definidas pelo usuário e em implementações diversas como índices para cubos Olap, árvores R (R-Trees) e em busca em texto. Internamente é composta por nós que apresentam pares (Valor, ponteiro para dado indexado ou para outro nó).

- GIN - Índices GIN(Generalized Inverted Index) são estruturas invertidas que permitem acesso a dados com mais de uma chave, como arrays. É composto por uma lista de registros tipo (chave, lista de linhas que contém o valor da chave). Internamente cada índice GIN contém uma árvore B com base no valor da chave indexada, cujas folhas apontam para os valores das linhas relacionadas à chave. Busca em fulltext pode ser acelerada utilizando-se este recurso.

A documentação do PostgreSQL afirma que é possível ao usuário definir o próprio método de indexação, no entanto salienta que o processo é difícil.

Versões anteriores do PostgreSQL apresentavam o método R-Tree, que indexava dados com base em árvores R, mas o mesmo foi removido por não apresentar quaisquer vantagens em relação a índices GiST, que é a principal opção para a sua substituição.

Vamos aos comandos!

Indexação no PostgreSQL - I

Índices são objetos criados em um banco de dados que facilitam o acesso a dados em um banco, com ganho de desempenho nas consultas realizadas. Os índices são definidos sobre uma ou mais colunas das tabelas armazenadas, criando estruturas de acesso mais rápidas com base nos campos selecionados.

São bastante utilizados e apresentam bons resultados no PostgreSQL, embora a má utilização possa retardar ao invés de acelerar as rotinas de banco de dados.

A criação, exclusão e alteração de índices no PostgreSQL são feitas através dos comandos:
- CREATE INDEX
- DROP INDEX
- ALTER INDEX

Podem ser criados índices parciais, isto é, índices que não abrangem todos os dados, mas apenas subconjuntos definidos com uma cláusula WHERE, recurso avançado ainda pouco conhecido e empregado, mas com grande potencial de utilização.

PostgreSQL x MySql X Oracle: Empate Técnico?

Pelo menos é o que sugere o último benchmark feito pela SUN. O benchmark envolveu testes de desempenho comparando as ferramentas.

No quesito performance os bancos estão se aproximando, com o MySql ganhando em robustez e o PostgreSql aumentando seu desempenho com implementações mais otimizadas e novos métodos de indexação, processamento de consultas e algoritmos internos. E o Oracle, um SENHOR BANCO DE DADOS, não tem do que se queixar. É uma ótima solução para as empresas, com uma grande equipe e muitos usuários pelo mundo.

Quem ganha com isso somos nós, os usuários. É engraçado ver um elefante alcançar a velocidade de um golfinho. Minha opinião é que o PostgreSQL é o que mais tem crescido. O que você acha?

Quer conferir?

- Blog do Telles - Savepoint

- Comentários de Josh Berkus, líder de desenvolvimento do PostgreSql

- Blog do Kevin Closson com o Ponto de Vista da Oracle

O Comando COPY

Este comando realiza a cópia de dados entre tabelas e arquivos. É muito utilizada para fazer cargas de dados e implementar rotinas de backup e migrações de bancos de dados. Não faz parte da especificação tradicional do SQL.

A sintaxe com a cláusula TO extrai de uma tabela seus dados e os armazena em um arquivo texto. A sintaxe com FROM lê um arquivo texto, binário ou CSV (texto separado por vírgulas) para uma tabela.

O COPY pode ser empregado um comando Select ou cláusula Values para ganho de maior controle sobre os dados gravados/ lidos.

Adicionalmente pode substituir delimitadores nos arquivos gerados e definir caracteres que substituam valores nulos.

Sintaxe:

COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]

COPY { tablename [ ( column [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ HEADER ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Exemplos de COPY de tabela para arquivo:

1 - Sintaxe que lê dados da tabela tstdel e os grava no arquivo teste.txt. Por padrão, o arquivo é criado em modo texto e a separação dos dados no arquivo gerado é feita por tabulações. Valores nulos são gravados como o caracter '\N'.

COPY tstdel TO 'c://teste.txt';

Arquivo gerado:
1 Teste1 2007-07-26 14:35:14
2 Teste2 2007-07-26 14:35:14
3 Teste3 2007-07-26 14:35:14
4 Teste4 2007-07-26 14:35:14
5 Teste5 2007-07-26 14:35:14
6 \N \N

2 - Usando o caracter '|' para separar os campos do arquivo gerado. A cláusula WITH é opcional.

COPY tstdel TO 'c://teste.txt' WITH DELIMITER '|';

Arquivo gerado:

1|Teste1|2007-07-26 14:35:14
2|Teste2|2007-07-26 14:35:14
3|Teste3|2007-07-26 14:35:14
4|Teste4|2007-07-26 14:35:14
5|Teste5|2007-07-26 14:35:14
6|\N|\N


3 - COPY gerando arquivo binário. O arquivo é criado mais rapidamente que em modo texto, mas a portabilidade pode ser menor entre versões do PostgreSQL ou em relação a outros bancos.

COPY tstdel TO 'c://teste.txt' BINARY;

4- A geração de arquivo pode causar problemas com valores nulos. Esta sintaxe troca valores nulos por um caracter "*".

COPY tstdel TO 'c://teste.txt' NULL '*';

5 - O COPY permite a escolha de um conjunto de colunas. Exemplo de cópia das colunas cod e nome.

COPY tstdel (cod, nome) TO 'c://teste.txt';

6 - Uso de select para definir os conjuntos de dados a serem copiados.

COPY (select * from tstdel where cod <= 500) TO 'c://teste.txt';

7 - Geração de arquivos em formato CSV.

COPY tstdel TO 'c://teste.txt' CSV;

8 - Geração de arquivos em formato CSV com HEADER, um cabeçalho com o nome das colunas.

COPY tstdel TO 'c://teste.txt' CSV HEADER;

Arquivo gerado:

cod,nome,date
1,Teste1,2007-07-26 14:35:14
2,Teste2,2007-07-26 14:35:14
3,Teste3,2007-07-26 14:35:14
4,Teste4,2007-07-26 14:35:14
5,Teste5,2007-07-26 14:35:14
6,,

Exemplos de COPY de arquivo (criado com COPY FROM) para tabela:

1 - Sintaxe que lê o arquivo teste.xtx e grava seus dados na tabela tstdel. Por padrão é feita a cópia de arquivo texto com um registro por linha, e campos separados por tabulações.

COPY tstdel FROM 'c://teste.txt';

2 - Sintaxe que lê e recupera um arquivo binário. Caso o arquivo lido não seja no formato esperado, é emitida mensagem de erro.

COPY tstdel FROM 'c://teste.txt' BINARY;

3 - Sintaxe que lê e recupera um arquivo de dados, trocando os valores '*' por nulos.

COPY tstdel FROM 'c://teste.txt' NULL '*';

4 - Exemplo que recupera apenas as colunas cod e nome.

COPY tst2 (cod, nome) FROM 'c://teste.txt';

5 - Definindo conjuntos de dados a serem copiados através de select. Uma cláusula VALUES também poderia ser empregada.

COPY (select * from tstdel where cod <= 500) FROM 'c://teste.txt';

6 - COPY Lendo arquivos CSV.

COPY tstdel FROM 'c://teste.txt' CSV;

7 - Lendo arquivos CSV com HEADER.

COPY tstdel FROM 'c://teste.txt' CSV HEADER;

Ferramenta de Modelagem - MicroOlap Database Designer for PostgreSQL - II

"Leandro Guimarães Faria Corcete DUTRA disse...
Ele suporta domínios, versionamento e vários SGBDs?"

Com relação ao questionamento do Leandro:

- A ferramenta Microolap apresenta um gerenciador/ construtor de domínios e tipos do usuário que facilita bastante a utilização destes recursos nos projetos. O que não encontrei que poderia ser adicionado foi uma biblioteca com alguns exemplos e tipos úteis. Fica como sugestão para eles.



- Não encontrei suporte a múltiplos bancos de dados. Pelo que constatei, cada diagrama pode conter apenas um database. É de certa forma uma limitação.

- O versionamento teria de ser feito através de ferrementa à parte, como Clearcase, CVL ou Source Safe, pois o MicroOlap mantém apenas uma versão dos modelos. O máximo que ele faz é criar uma "stamp", que apresentaria o versionamento do diagrama, mas neste caso a versão seria apenas um número ao qual pode ser acrescentado um auto incremento.

Por enquanto é só.

Ferramenta de Modelagem - MicroOlap Database Designer for PostgreSQL

Para quem gosta de trabahar organizadamente com modelagem de dados, o MicroOlap é um software com bons recursos. O "MicroOlap Database Designer for PostgreSQL" é um shareware baseado em java que pode ser utilizado por 30 dias (full version!).



A primeira impressão que tive foi muito positiva quanto ao seu uso:
- Facilidade de utilização e rica interface visual (auto layout, zoom, diagramas visuais)
- O Instalador é fácil de usar e não chega a ter 6 megabytes
- A Engenharia reversa intuitiva e funcional



- Geração de bancos de dados conforme o modelo criado de forma bastante prática.
- Interfaces para administração de índices, tabelas, colunas, domínios, tablespaces, visões, etc.
- Checagem automática de modelos, identificando warnings e errors relacionados aos objetos.
- Suporte às características específicas do PostgreSql com herança de tabelas.

O instalador pode ser baixado em:

MicroOlap

A versão single user não está tão cara: $124.95 dólares.

Alguém está utilizando esta ferramenta em seus projetos? Quais são as suas impressões?

PgCon Day 2007 em São Paulo!!!

Quem como eu perdeu a PgCon da Itália, tem uma nova chance de participar.

Uma versão Nacional do evento máximo do nosso banco de dados será realizada em São Paulo. Preparem os cobres e as licenças para ir à cidade que nunca dorme dias 07 e 08 de dezembro.
Mais informações no site do Telles (grato pela informação):

http://www.midstorm.org/~telles/2007/07/09/pgcon-e-outras-novidades/

O Comando DROP TABLESPACE

O Comando DROP TABLESPACE remove um tablespace. Uma vez que não é permitida a exclusão em cascata por meio deste comando, deve-se excluir tabelas e outros objetos para que o mesmo possa ser removido, do contrário é emitida mensagem de erro.

Um tablespace só pode ser removido pelo seu OWNER ou por um superusuário.

Sintaxe:

DROP TABLESPACE [ IF EXISTS ] tablespacename

A cláusula IF EXISTS impede a emissão de erro caso o tablespace a ser excluído não exista.

Exemplos:

1 - Exclusão de tablespace.

DROP TABLESPACE teste;

2 - Exclusão de tablespace com a cláusula IF EXISTS.

DROP TABLESPACE IF EXISTS teste4;

O Comando CREATE TABLESPACE

O comando CREATE TABLESPACE define uma área de mídia de armazenamento em que podem ser armazenados os conteúdos de um ou mais bancos de dados. Pode ser um disco mais rápido ou um servidor potente em uma rede, por exemplo.

Os comandos CREATE DATABASE, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT utilizam como parâmetro o tablespace em que devem ser armazenados. Desta forma é possível distribuir o armazenamento de dados com ganho de organização e desempenho.

No PostgreSQL o tablespace é uma pasta do sistema de arquivos que deve estar vazia no momento da criação. Do contrário, ocorre erro.

Em termos práticos, a criação de um tablespace ocorre em duas fases:
- Criação de pasta no sistema de arquivos para armazenamento físico do tablespace.
- Execução do comando CREATE TABLESPACE

Sintaxe:

CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'

Exemplos:

1 - Criação de tablespace para o usuário Postgres na pasta 'c:\temp\tsp2'. Observe que o caminho do diretório está com a barra invertida.

create tablespace teste3 owner postgres location 'c:/temp/tsp2';

2 - Criação de tablespace com o usuário omitido.

create tablespace teste4 location 'c:/temp/tsp3';

3 - Criação de tabela no tablespace teste.

CREATE TABLE letsgo (cod integer, nome varchar (200)) TABLESPACE teste;

Tablespaces no PostgreSQL

Enquanto os esquemas dividem um banco de dados em particionamentos lógicos, o tablespace divide um banco de dados fisicamente. Tablespace no PostgreSQL é uma pasta no sistema de arquivos utilizada para armazenamento físico dos dados e objetos de um banco de dados.

Ao se criar um banco de dados no PostgreSQL são criados dois tablespaces:
- PG_DEFAULT - por padrão, quando não é definido explicitamente um tablespace, os dados e objetos são armazenados no tablespace PG_DEFAULT.
- PG_GLOBAL - armazena objetos globais, visíveis a todos os bancos

Um tablespace pode armazenar dados de diversos bancos de dados, desde que se tenha permissão para acesso aos mesmos. Um banco de dados também pode dividir seus objetos e dados entre vários tablespaces.

Os tablespaces são utilizados para:
- Gerenciamento de espaço de armazenamento de dados - pode-se armazenar bancos grandes em mídia com tamanho apropriado que esteja disponível na rede.
- Melhor desempenho - objetos que sofrem mais acessos podem ser alocados em discos mais rápidos, por exemplo, com ganho substancial de desempenho.

Os principais comandos relacionados são:
- CREATE TABLESPACE - Criação de tablespaces
- DROP TABLESPACE - Exclusão de tablespaces sem conteúdo

Para visualizar a lista de tablespaces do seu servidor PostgreSQL, basta se fazer uma consulta na tabela pg_tablespace.

Exemplo:

1 - Recuperação do identificador de objeto e dos dados dos tablespaces do servidor.

SELECT oid, * FROM pg_tablespace;

2 - Recuperando o Tablespace e o usuário que tem permissão de owner do tablespace

SELECT T.oid, T.spcname, T.spcowner, U.usename
FROM pg_tablespace T, pg_user U
WHERE T.spcowner = U.usesysid;

PGDay - Evento já tem agenda definida...

O PGDay é um evento de grande magnitude que reunirá a comunidade do banco de dados na Itália dia 08 de julho, data do 21o aniversário desta ferramenta (Na verdade o PostgreSQL é mais recente, mas sua origem vem de outros projetos de banco de dados da década de 80).

O programa está disponível no endereço abaixo:

http://www.pgday.it/en/generale/programma

O autor deste blog está com inveja dos frequentadores do evento. Se você for, aproveite por você e por mim.

O Comando ALTER SCHEMA

Este comando permite a alteração do nome e usuário owner (proprietário) de um esquema. Ao renomear um esquema, todas as lógicas que fazem referência ao nome antigo devem ser revistas e para refletir a nova nomenclatura.

Sintaxe:

ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner

Exemplos:

1 - Alteração de nome de esquema.

ALTER SCHEMA TSTSCHEMA5 RENAME TO TESTE

2 - Alteração de owner de esquema.

ALTER SCHEMA TESTE OWNER TO ALFA

Criação de Esquemas com o Prefixo "PG_"

Curiosidade: ao se criar um esquema com o prefixo "PG_", ocorre erro. O PostgreSql reserva esse prefixo para os objetos de sistema. Isso não ocorre com tabelas, por exemplo.

1 - Tentativa de criação de esquema.

CREATE SCHEMA pg_clau

Erro retornado:

ERROR: unacceptable schema name "pg_clau"
SQL state: 42939
Detail: The prefix "pg_" is reserved for system schemas.

O Comando DROP SCHEMA

Este comando exclui um esquema do banco de dados. Opcionalmente pode ser feita exclusão dos seus objetos internos em cascata, o que pode trazer riscos à segurança.

Sintaxe:

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Exemplos:

1- Exclusão simples de esquema.

DROP SCHEMA TSTSCHEMA1;

2 - Exclusão apenas se o esquema existir utilizando a cláusula IF EXISTS.

DROP SCHEMA IF EXISTS TSTSCHEMA2;

3 - Exclusão de mais de um esquema ao mesmo tempo.

DROP SCHEMA TSTSCHEMA3, TSTSCHEMA4;

4 - Exclusão de esquema com cláusula RESTRICT retorna erro caso existam tabelas e/ ou outros objetos dentro do esquema. Mesmo omitida, a cláusula RESTRICT é utilizada como padrão pelo banco.

DROP SCHEMA TSTSCHEMA5 RESTRICT;

Abaixo, a mensagem de erro retornada.

NOTICE: table tstschema5.tst3 depends on schema tstschema5
NOTICE: table tstschema5.tst2 depends on schema tstschema5

ERROR: cannot drop schema tstschema5 because other objects depend on it
SQL state: 2BP01
Hint: Use DROP ... CASCADE to drop the dependent objects too.

5 - Exclusão de esquema com cláusula CASCADE elimina também qualquer tabelas ou outros objetos dentro do esquema automaticamente. Deve ser utilizada com parcimônia pois pode remove dados e objetos indiscriminadamente.

DROP SCHEMA TSTSCHEMA6 CASCADE;

O Comando CREATE SCHEMA

Este comando cria um novo esquema no banco de dados. Os objetos criados dentro de um esquema, como tabelas, triggers entre outros, devem ser referenciados pelo caminho esquema.objeto.

Ao se criar um esquema, o mesmo não deve ter o mesmo nome de um esquema já existente.

É possível também definir um usuário como "dono" do esquema no ato de sua criação, e inserir tabelas e outros objetos no esquema criado.

Sintaxe:

CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

O termo "schema_element" se refere aos objetos do banco de dados: tabelas, visões, trigers, etc.

Exemplos:

1 - Criação do esquema TSTSCHEMA1

CREATE SCHEMA TSTSCHEMA1;

2 - Criação do esquema TSTSCHEMA2, o qual fica de posse do usuário ALFA.

CREATE SCHEMA TSTSCHEMA2 AUTHORIZATION ALFA;

3 - Criação do esquema ALFA, o qual fica de posse do usuário ALFA. Observe que nesta sintaxe o nome do esquema fica omitido.

CREATE SCHEMA AUTHORIZATION ALFA;

4 - Criação do esquema TSTSCHEMA5, e da tabela TST1 dentro do esquema.

CREATE SCHEMA TSTSCHEMA5
CREATE TABLE TST1 (COD INTEGER, DESCRICA VARCHAR(10));

4 - Criação do esquema TSTSCHEMA6, e de duas tabelas dentro do esquema criado. Observe a ausência de ponto e vírgula na defininção das tabelas TST2 e TST3.

CREATE SCHEMA TSTSCHEMA6
CREATE TABLE TST2 (COD INTEGER, DESCRICA VARCHAR(10))
CREATE TABLE TST3 (COD INTEGER, DESCRICA VARCHAR(10));

5 - Criação do esquema TSTSCHEMA7, e de duas tabelas dentro do esquema criado, com posse do esquema para o usuário ALFA.

CREATE SCHEMA TSTSCHEMA7 AUTHORIZATION ALFA
CREATE TABLE TST4 (COD INTEGER, DESCRICA VARCHAR(10))
CREATE TABLE TST5 (COD INTEGER, DESCRICA VARCHAR(10));

6 - Criação de objeto tabela dentro de um esquema e referência ao mesmo.

CREATE TABLE TSTSCHEMA7.TST6 (COD INTEGER, DESCRICA VARCHAR(10));
SELECT * FROM TSTSCHEMA7.TST6;

Esquemas no PostgreSQL

Esquemas são partições lógicas de um banco de dados. São formas de se organizar logicamente um banco de dados em conjuntos de objetos com características em comum sem criar bancos de dados distintos. Por exemplo, podem ser criados esquemas diferentes para dados dos níveis operacional, tático e estratégico de uma empresa, ou ainda esquemas para dados de cada mês de um ano.

Podem ser utilizados também como meios de se estabelecer melhores procedimentos de segurança, com autorizações de acesso feitas por esquema ao invés de serem definidas por objeto do banco de dados.

O PostgreSQL possui um conjunto de esquemas padrão, sendo que a inclusão de objetos do usuário por padrão é feita no esquema PUBLIC. Ao se criar um banco de dados, o banco apresentará inicialmente os seguintes esquemas:

- information_schema – informações sobre funções suportadas pelo banco. Armazena informações sobre o suporte a SQL, linguagens suportadas e tamanho máximo de variáveis como nome de tabela, identificadores, nomes de colunas, etc.

- pg_catalog – possui centenas de funções e dezenas de tabelas com os metadados do sistema. Guarda informações sobre as tabelas, suas colunas, índices, estatísticas, tablespaces, triggers e demais objetos.

- pg_toast – Informações relativas ao uso de TOAST (The Oversized-Attribute Storage Technique).

- public – Esquema com as tabelas e objetos do usuário.

O desenvolvedor pode criar, alterar e excluir esquemas utilizando os comandos:

- CREATE SCHEMA
- ALTER SCHEMA
- DROP SCHEMA

Chega de transações!

Por enquanto!

Os Comandos SET TRANSACTION e SET SESSION

Estes comandos definem o chamado transaction mode, característica que influencia a execução de uma transação. Dependendo do que for definido, o sistema poderá por exemplo obter um melhor desempenho sacrificando a aderência às desejáveis características ACID (Atomicidade, Consistência, Isolamento e Durabilidade). São apresentados junto na ajuda do PostgreSQL por terem função similar.

O transaction_mode é composto pelo nível de isolamento da transação e das permissões de uma transação para fazer leitura e escrita em um banco de dados.

Outros comandos podem afetar o transaction mode: START TRANSACTION e BEGIN. Nestes comandos é afetado o transaction mode da transação corrente. O comando SET TRANSACTION permite a alteração desta característica tanto para a transação corrente quanto para as subseqüentes.

SINTAXE:

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

Onde o transaction_mode pode assumir os seguintes valores:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY

Principais transaction modes:

- SERIALIZABLE - Fiel às características ACID, apresenta menor desempenho pois praticamente simula a execução sequencial de transações. A transação poderá ver apenas os dados já efetivados no banco antes do início da execução da transação. Isto é, não poderá por exemplo ler dados inseridos após o seu início.

- REPEATABLE READ - Mais flexível, apresenta melhor desempenho em relação ao anterior. A transação poderá ler os mesmos dados várias vezes durante sua execução e o valor será o mesmo.

- READ COMMITTED - Mais flexível e com melhor desempenho em relação ao anterior. A transação poderá ler os mesmos dados várias vezes durante sua execução e o valor lido será diferente caso o dado tenha sido atualizado por outra transação que tenha feito COMMIT explícito ou implícito.

- READ UNCOMMITTED - Apresenta o melhor desempenho em relação às demais. No entanto não é fiel às características ACID. Neste caso a transação poderá ler os mesmos dados várias vezes durante sua execução e o valor lido será diferente caso o dado tenha sido atualizado por outra transação que tenha feito ou não COMMIT.

Exemplos:

1 - Altera a transação corrente para SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2 - Altera a transação corrente para READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3 - Altera a transação corrente para SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4 - Altera a transação corrente para READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

5 - Define que a transação corrente será SERIALIZABLE com permissão de leitura e escrita

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE

6 - Define que a transação corrente será SERIALIZABLE com permissão apenas para leitura de dados

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY

7 - Altera as transações subseqüentes para SERIALIZABLE

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

8 - Altera as transações subseqüentes para REPEATABLE READ

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ

9 - Altera as transações subseqüentes para READ COMMITTED

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

10 - Altera as transações subseqüentes para READ UNCOMMITTED

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

11 - Altera as transações subseqüentes para SERIALIZABLE com permissão para leitura e escrita

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE

12 - Altera as transações subseqüentes para REPEATABLE READ com permissão apenas para leitura

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY

Os Comandos SAVEPOINT, ROLLBACK TO SAVEPOINT e RELEASE SAVEPOINT

Savepoints são pontos dentro de uma transação que indicam que os comandos posteriores podem sofrer rollback, enquanto os comandos anteriores são mantidos no banco de dados mesmo que a transação tenha sido abortada. Uma transação pode ter mais de um Savepoint que é indicado por um nome fornecido pelo programador.

Normalmente este comando é empregado com o comando ROLLBACK TO SAVEPOINT, o que é ilustrado nos exemplos abaixo.

Sintaxe:

SAVEPOINT savepoint_name

A sintaxe do comando ROLLBACK TO SAVEPOINT é a seguinte:

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

Exemplo:

1 - No primeiro exemplo existem dois comandos de inserção. O Savepoint assegura que o primeiro insert seja mantido mesmo que haja um rollback da transação.

BEGIN;
insert into tstdel values (1000, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
insert into tstdel values (1001, 'LALALA', current_timestamp);
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

2 - Com três comandos de inserção, observa-se que o primeiro e o último são persistidos no banco de dados. O Savepoint assegura que o primeiro insert seja mantido mesmo que haja um rollback da transação e a inserção realizada após o comando rollback é efetivada e gravada no banco de dados.


BEGIN;
insert into tstdel values (1000, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
insert into tstdel values (1001, 'LALALA', current_timestamp);
ROLLBACK TO SAVEPOINT my_savepoint;
insert into tstdel values (1002, 'LALALA', current_timestamp);
COMMIT;

Transações grandes podem ter um grande número de Savepoints. Em virtude disto, pode ser necessário fazer a liberação (ou destruição) de savepoints quando não forem mais necessários em uma transação. O comando RELEASE SAVEPOINT faz essa eliminação de savepoint.

Sintaxe:

RELEASE [ SAVEPOINT ] savepoint_name

Exemplos:

1 - Exemplo de Release Savepoint.

BEGIN;
insert into tstdel values (1009, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
insert into tstdel values (1010, 'LALALA', current_timestamp);
RELEASE SAVEPOINT my_savepoint;
SAVEPOINT my_savepoint2;
insert into tstdel values (1011, 'LALALA', current_timestamp);
RELEASE SAVEPOINT my_savepoint2;
COMMIT;

2 - Exemplo de Release Savepoint com savepoint repetido.

BEGIN;
insert into tstdel values (1009, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
insert into tstdel values (1010, 'LALALA', current_timestamp);
RELEASE SAVEPOINT my_savepoint;
SAVEPOINT my_savepoint;
insert into tstdel values (1011, 'LALALA', current_timestamp);
RELEASE SAVEPOINT my_savepoint;
COMMIT;

3 - Exemplo de Release Savepoint com savepoints repetidos e em seqüência. Neste caso, o comando release libera o checkpoint mais próximo e o comando ROLLBACK TO CHECKPOINT passa a indicar um rollback até o primeiro savepoint. Apenas o primeiro comando insert é efetuado.

BEGIN;
insert into tstdel values (1009, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
insert into tstdel values (1010, 'LALALA', current_timestamp);
SAVEPOINT my_savepoint;
RELEASE SAVEPOINT my_savepoint;
insert into tstdel values (1011, 'LALALA', current_timestamp);
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

O Comando START TRANSACTION

Este comando tem a mesma função do BEGIN, servindo como ponto de delimitação do início de uma transação. O ponto de término da transação é assinalado com um comando COMMIT ou ROLLBACK.

Sintaxe:

START TRANSACTION [ transaction_mode [, ...] ]

Onde o transaction_mode assume um dos seguintes valores:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY

Obs.: os transaction modes serão discutidos em maior profundidade ao se detalhar o comando SET TRANSACTION.

Exemplo:

START TRANSACTION;
insert into tstdel values (1000, 'LALALA', current_timestamp);
COMMIT;

Os Comandos BEGIN, COMMIT e ROLLBACK

O comando BEGIN inicia um bloco de comandos SQL que fazem parte de uma transação. Os comandos realizados após o este comando só são persistidos em disco e seus resultados só são apresentados aos demais usuários do banco após a efetivação com o comando COMMIT.

A transação é finalizada pelo comando COMMIT, o qual dispara a efetivação da transação no banco de dados e torna visível para os demais usuários os resultados da execução dos seus comandos.

O comando ROLLBACK aborta a transação que está em andamento, impedindo que os as alterações nos dados nela realizadas sejam persistidos no banco de dados.

Sintaxe:

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

onde o transaction_mode é um dos listados abaixo:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY

Obs.: os transaction modes serão discutidos em maior profundidade ao se detalhar o comando SET TRANSACTION.

COMMIT [ WORK | TRANSACTION ]


ROLLBACK [ WORK | TRANSACTION ]

Exemplos:

1 - Sintaxe mais simples com BEGIN e COMMIT

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT;

2 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula WORK não tem efeito é é mantida por compatibilidade.

BEGIN WORK;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT WORK;

3 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula TRANSACTION não tem efeito é é mantida por compatibilidade.

BEGIN TRANSACTION;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT TRANSACTION;

4 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK;

5 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula WORK não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK WORK;

6 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula TRANSACTION não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK TRANSACTION;

Transações no PostgreSQL

Transação é cada execução de programa que realiza leitura e/ou escrita em um banco de dados. O PostgreSQL implementa este conceito resguardando as características ACID - atomicidade, consistência, isolamento e durabilidade.

Atomicidade - uma transação é totalmente executada ou totalmente revertida sem deixar efeitos no banco de dados

Consistência - os resultados são coerentes com as operações realizadas

Isolamento - a execução de uma transação não interfere ou sofre interferência em relação às demais transações em execução

Durabilidade - o resultado das transações deve ser persistido fisicamente no banco de dados.

Cada comando executado, seja de leitura ou escrita, gera uma transação implícita no PostgreSql, que gerencia a manutenção das características ACID.

Existem duas ressalvas a serem feitas:

- Em certas situações é interessante se agregar vários comandos como sendo integrantes de uma mesma transação, como por exemplo em uma transferência bancária que envolve a retirada de dinheiro de uma conta e o acréscimo em outra como se fosse apenas uma única operação lógica.
- Em outras situações se faz necessário sacrificar ou flexibilizar as características ACID em virtude da necessidade de maior desempenho.

Para executar estas atividades são disponibilizados comandos de controle de transação:

- BEGIN;
- COMMIT;
- ROLLBACK;
- START TRANSACTION;
- SAVEPOINT;
- SET TRANSACTION;

Nos próximos posts serão detalhados os comandos, suas sintaxes e funções, com exemplos.

O Comando Truncate

Este comando exclui todas as linhas de uma tabela ou de uma lista de tabelas de forma mais rápida que o comando DELETE tradicional. Apenas o owner de uma tabela pode eliminar seus dados com o comando TRUNCATE.

É recomendado o prudência no uso deste comando, especialmente com a cláusula CASCADE, pois informações relevantes podem ser eliminadas.

Sintaxe:

TRUNCATE [ TABLE ] name [, ...] [ CASCADE | RESTRICT ]

A cláusula CASCADE elimina tabelas referenciadas pela tabela que sofre o TRUNCATE. A cláusula RESTRICT é o padrão e faz o TRUNCATE apenas da tabela explicitamente citada no comando, retornando erro caso haja alguma violação de restrição de integridade. Não é permitido TRUNCATE se a tabela truncada é referenciada por uma tabela filha através de foreign key:

ERROR: cannot truncate a table referenced in a foreign key constraint
SQL state: 0A000
Detail: Table "tstdel3" references "tstdel".
Hint: Truncate table "tstdel3" at the same time, or use TRUNCATE ... CASCADE.

Alguns exemplos:

1 - Exclusão de registros de uma tabela com a cláusula TABLE

TRUNCATE TABLE tstdel;

2 - Exclusão de registros com referência a um esquema

TRUNCATE public.tstdel;

3 - Sintaxe smplificada

TRUNCATE tstdel;

4 - Exclusão de mais de uma tabela ao mesmo tempo

TRUNCATE tstdel, tstdel2;

5 - Exclusão com cláusula CASCADE

TRUNCATE tstdel CASCADE;

Obs.: O comando EXPLAIN não funciona com o comando TRUNCATE, gerando mensagem de erro.

Obs.2: A especificação padrão da linguagem SQL não apresenta o comando TRUNCATE.

O Comando Vacuum

É fortemente recomendável para melhor desempenho e aproveitamento do espaço de armazenamento secundário do seu banco de dados que se faça a execução periódica do comando VACUUM.

Este comando reaproveita espaços em branco deixados pelas atualizações e exclusões de registros (fragmentação interna) e pode ser utilizado para atualização das estatísticas para planejamento e otimização de consultas.

As alterações de registros produzem novas versões dos dados no banco em virtude do protocolo de controle de concorrência multiversão, as quais não são fisicamente excluídas após a execução das transações, causando aumento de espaço utilizado. A exclusão de registros também não elimina imediatamente os dados, proporcionando melhor desempenho para deleções. Em ambos os casos o espaço não utilizado é liberado com a execução deste comando.

Adicionalmente, o comando pode atualizar as estatísticas do banco de dados, permitindo planos de execução de consultas melhor otimizados e melhorando o desempenho do sistema como um todo.

Em ambientes com grande quantidade de dados submetidos a muitas alterações, exclusões e inclusões de dados, o comando Vacuum pode trazer resultados significativos.

Sintaxe:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

Exemplos:

- VACUUM

Sintaxe mais simples. Libera o espaço bloqueado para uso, mas não restringe as leituras e escritas ao banco durante o processo (não bloqueia tabelas durante a reorganização). Não compacta os dados eliminando a fragmentação interna. Apenas libera os espaços para utilização.

- VACUUM FULL

Libera o espaço bloqueado para uso, movendo registros e ocupando os espaços disponíveis dentro dos blocos. Restringe as leituras e escritas ao banco, pois bloqueia objetos durante a sua execução. Como resultado final, compacta os dados eliminando a fragmentação interna.

- VACUUM (FULL) VERBOSE

Efetua o VACUUM, tanto o normal quanto o FULL, apresentando um relatório detalhado das operações realizadas. A opção FULL não diminui índices, o que é feito pelo comando REINDEX.

- VACUUM (FULL) ANALYSE

Efetua o VACUUM, tanto o normal quanto o FULL, atualizando as estatísticas utilizadas na otimização das consultas.

- VACUUM (FULL) TABELA

Efetua o VACUUM, tanto o normal quanto o FULL, na tabela fornecida. Opção recomendada quando se demanda maior controle sobre o processo de reorganização do banco de dados.

Dica: Comando Explain Analyze

O comando Explain Analyze oferece informações mais acuradas pois executa de fato os comandos que são fornecidos. No entanto certas operações podem alterar os dados em disco, o que pode ser indesejável.

O uso dos comandos Begin Transaction e Rollback impedem que a base seja alterada e permitem a execução do comando sem alterações em Banco.

BEGIN;
EXPLAIN ANALYZE (seu comando);
ROLLBACK;

O exemplo abaixo mostra em termos práticos um teste de exclusão de registros em que se deseja testar o tempo para exclusão sem apagar de fato os dados de uma tabela:

BEGIN;
EXPLAIN ANALYZE delete from lugar2;
<== Recupere neste ponto os dados do comando EXPLAIN
ROLLBACK; <== Retorna ao estado anterior ao do comando DELETE utilizado

O Comando Explain III

A interpretação do comando EXPLAIN é importante e pode revelar surpresas.

No exemplo abaixo, comandos diferentes que retornam os mesmos dados apresentam resultado equivalente. Em termos práticos, qualquer das sintaxes traria os mesmos resultados e tomaria aproximadamante o mesmo tempo.

explain select nome, obs from continente where cod = 1 order by nome
Sort (cost=1.05..1.05 rows=1 width=634)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.04 rows=1 width=634)
Filter: (cod = 1)

explain select nome, obs from continente where cod in (1) order by nome
Sort (cost=1.05..1.05 rows=1 width=634)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.04 rows=1 width=634)
Filter: (cod = 1)

Já neste segundo exemplo, comandos diferentes que retornam os mesmos dados apresentam resultados diferentes. Em termos práticos, uma sintaxe poderia ser vantajosa em relação à outra, ainda que traga os mesmos dados. O ganho não chega a ser substancial, mas pode ser percebido.

explain select nome, obs from continente where cod = 1 or cod = 2 or cod = 3 order by nome
Sort (cost=1.06..1.07 rows=2 width=634)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.05 rows=2 width=634)
Filter: ((cod = 1) OR (cod = 2) OR (cod = 3))

explain select nome, obs from continente where cod in (1, 2, 3) order by nome
Sort (cost=1.05..1.06 rows=2 width=634)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.04 rows=2 width=634)
Filter: (cod = ANY ('{1,2,3}'::integer[]))

Alterações de sintaxe em comandos SQL baseadas na análise dos custos só são recomendadas se o custo for relevante e se o ganho de uma alternativa em relação a outra seja significativo.

Uma consulta SQL que seja executada muitas vezes ao dia é uma boa candidata a testes com o comando EXPLAIN.

O Comando Explain II

Como interpretar os resultados do comando EXPLAIN?

O exemplo abaixo é relativamente simples, mas no entanto é revelador.

explain select * from continente

Seq Scan on continente (cost=0.00..1.03 rows=3 width=646)

O resultado do comando apresenta quatro números para cada nó do plano de execução de uma consulta:

- Custo inicial - 0.00 - é o custo de execução da consulta até o inicio da saída dos resultados. No nosso exemplo, este custo é mínimo, pois a consulta ér relativamente simples. Em outros casos, pode ser que o maior custo de execução de consulta seja exatamente este custo inicial.

- Custo total - 1.03 - custo total estimado considerando que todas as linhas serão retornadas. O uso de LIMIT em consultas faz com que o custo total seja reduzido.

- Rows - estimativa de quantidade de registros retornados.

- Width - estimativa de quantidade de bytes retornados.

Um segundo exemplo torna ainda mais claro o funcionamento do comando.

explain select nome, obs from continente order by nome

Sort (cost=1.05..1.06 rows=3 width=634)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.03 rows=3 width=634)

Uma pequena mudança na consulta anteriori fez com que fossem necessárias várias operações antes do retorno dos resultados. Cada operação tem individualizados os custos inicial, total, em termos de linhas e em termos de bytes.

São informações que podem ser bastante úteis na otimização de consultas através do comando EXPLAIN.

Abaixo, duas consultas bastante semelhantes e seus custos. A simples utilização dos campos na cláusula select ao invés do * já causou uma importante redução no custo de execução da consulta no que tange à estimativa da quantidade dos bytes retornados:

explain select * from continente order by nome
Sort (cost=1.05..1.06 rows=3 width=646)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.03 rows=3 width=646)

explain select nome from continente order by nome
Sort (cost=1.05..1.06 rows=3 width=118)
Sort Key: nome
-> Seq Scan on continente (cost=0.00..1.03 rows=3 width=118)

Comando EXPLAIN

O comando EXPLAIN permite que se possa visualizar todas as etapas envolvidas no processamento de uma consulta.Esta visão do funcionamento interno do banco permite que se possa fazer melhorias em consultas que estejam tomando tempo excessivo, por exemplo.

Sintaxe:

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

O resultado do comando explain pode ser surpreendente. Consultas com várias linhas podem ser mais econômicas que uma operação descrita em uma linha de SQL, realizando a mesma tarefa.

A cláusula ANALYSE executa de fato o comando e retorna o tempo de execução. Pode tornar bem mais lento o resultado se a execução do comando for demorada. O uso de VERBOSE vai detalhar ainda mais o resultado do comando EXPLAIN, o que pode dificultar a leitura.



Abaixo, alguns exemplos comentados:

1- EXPLAIN em consulta sobre uma tabela simples.

explain select * from continente;

Seq Scan on continente (cost=0.00..1.03 rows=3 width=646)

2- EXPLAIN ANALYZE em consulta sobre uma tabela simples.

explain analize select * from continente;

Seq Scan on continente (cost=0.00..1.03 rows=3 width=646) (actual time=0.016..0.020 rows=3 loops=1)
Total runtime: 0.085 ms

3 - EXPLAIN ANALYZE VERBOSE em consulta sobre uma tabela simples. O Resultado é bastante detalhado.

explain analize select * from continente;

{SEQSCAN
:startup_cost 0.00
:total_cost 1.03
:plan_rows 3
:plan_width 646
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 1
:varoattno 1
}
:resno 1
:resname nome
:ressortgroupref 0
:resorigtbl 16734
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 1043
:vartypmod 254
:varlevelsup 0
:varnoold 1
:varoattno 2
}
:resno 2
:resname obs
:ressortgroupref 0
:resorigtbl 16734
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 700
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
:resno 3
:resname population
:ressortgroupref 0
:resorigtbl 16734
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
:resno 4
:resname cod
:ressortgroupref 0
:resorigtbl 16734
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 700
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
:resno 5
:resname tamanho
:ressortgroupref 0
:resorigtbl 16734
:resorigcol 5
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:scanrelid 1
}

Seq Scan on continente (cost=0.00..1.03 rows=3 width=646) (actual time=0.012..0.017 rows=3 loops=1)
Total runtime: 0.084 ms

3 - EXPLAIN em consulta sobre uma tabela que é herdada por outras tabelas. Apresenta subconsultas às tabelas filhas.

explain select * from lugar;

Result (cost=0.00..13.26 rows=126 width=638)
-> Append (cost=0.00..13.26 rows=126 width=638)
-> Seq Scan on lugar (cost=0.00..11.20 rows=120 width=638)
-> Seq Scan on continente lugar (cost=0.00..1.03 rows=3 width=638)
-> Seq Scan on pais_cont lugar (cost=0.00..1.03 rows=3 width=638)

4 - EXPLAIN em consulta sobre uma visão com metadados das tabelas. .

explain select * from pg_tables;

Hash Left Join (cost=2.16..18.09 rows=72 width=200)
Hash Cond: (c.relnamespace = n.oid)
-> Hash Left Join (cost=1.04..15.62 rows=72 width=140)
Hash Cond: (c.reltablespace = t.oid)
-> Seq Scan on pg_class c (cost=0.00..13.59 rows=72 width=80)
Filter: (relkind = 'r'::char)
-> Hash (cost=1.02..1.02 rows=2 width=68)
-> Seq Scan on pg_tablespace t (cost=0.00..1.02 rows=2 width=68)
-> Hash (cost=1.05..1.05 rows=5 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=5 width=68)

Obs.: Não existe comando EXPLAIN na especificação oficial do SQL.