quarta-feira, 30 de maio de 2007
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
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
terça-feira, 29 de maio de 2007
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;
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;
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;
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.
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.
segunda-feira, 28 de maio de 2007
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.
É 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.
quinta-feira, 24 de maio de 2007
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.
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 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
quinta-feira, 17 de maio de 2007
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.
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)
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)
quarta-feira, 16 de maio de 2007
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.
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.
Comando Reindex
Este comando utilíssimo reorganiza os índices de um banco de dados. Pode ser utilizado para indexar tabelas e seus índices, índices individualmente ou até um banco de dados inteiro.
A reorganização de índices está associada a ganhos de espaço livre em disco e a melhorias substanciais de desempenho nas aplicações de banco de dados.
Sintaxe:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE
Aconselha-se o uso de reindex em tabelas de bom tamanho que apresentem muitas alterações e/ou muitas consultas que demandem índices rápidos.
Exemplos:
1 - Reorganiza índice citado explicitamente
- reindex index nomeíndice;
2 - Reorganiza tabela citada explicitamente
- reindex table nometabela;
3 - Reorganiza banco de dados fornecido
- reindex database nomedatabase;
4 - Reorganiza tabelas de sistema do banco de dados
- reindex system nomedatabase;
Obs.: A cláusula FORCE é obsoleta e ignorada na execução.
A reorganização de índices está associada a ganhos de espaço livre em disco e a melhorias substanciais de desempenho nas aplicações de banco de dados.
Sintaxe:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE
Aconselha-se o uso de reindex em tabelas de bom tamanho que apresentem muitas alterações e/ou muitas consultas que demandem índices rápidos.
Exemplos:
1 - Reorganiza índice citado explicitamente
- reindex index nomeíndice;
2 - Reorganiza tabela citada explicitamente
- reindex table nometabela;
3 - Reorganiza banco de dados fornecido
- reindex database nomedatabase;
4 - Reorganiza tabelas de sistema do banco de dados
- reindex system nomedatabase;
Obs.: A cláusula FORCE é obsoleta e ignorada na execução.
terça-feira, 15 de maio de 2007
Criação de Usuários - II
Como saber se os usuários foram devidamente criados?
A maneira mais simples é tentar se conectar ao banco de dados utilizando o novo usuário.
Uma opção mais requintada seria uma consulta à tabela de sistema pg_authid.
A maneira mais simples é tentar se conectar ao banco de dados utilizando o novo usuário.
Uma opção mais requintada seria uma consulta à tabela de sistema pg_authid.
Criação de Usuários
O comando CREATE USER permite a criação de usuários e a definição de suas permissões de acesso no ato do cadastramento. O processo é relativamente simples.
Abaixo, a sua sintaxe do comando:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| IN GROUP rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| USER rolename [, ...]
| SYSID uid
Por padrão a opção LOGIN é automaticamente definida.
O exemplo abaixo cria um usuáreio com permissão de login e sem senha.
create user usr1;
O exemplo abaixo cria um usuáreio com senha. A cláusula WITH é opcional.
create user usr2 WITH PASSWORD 'lalala';
O exemplo abaixo cria um usuário com limite de conexão até um determinado timestamp.
create user usr3 VALID UNTIL '12/31/2007';
Criação de um superusuário com permissões de criação de objetos no banco de dados.
create user usr4 SUPERUSER CREATEDB INHERIT;
Usuário com password sem a cláusula WITH. Ao se cadastrar um usuário, a senha é por padrão criptografada.
create user usr5 PASSWORD 'lalala';
A sintaxe abaixo cadastra um usuário com senha não criptografada.
create user usr6 UNENCRYPTED PASSWORD 'lalala';
Abaixo, a sua sintaxe do comando:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| IN GROUP rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| USER rolename [, ...]
| SYSID uid
Por padrão a opção LOGIN é automaticamente definida.
O exemplo abaixo cria um usuáreio com permissão de login e sem senha.
create user usr1;
O exemplo abaixo cria um usuáreio com senha. A cláusula WITH é opcional.
create user usr2 WITH PASSWORD 'lalala';
O exemplo abaixo cria um usuário com limite de conexão até um determinado timestamp.
create user usr3 VALID UNTIL '12/31/2007';
Criação de um superusuário com permissões de criação de objetos no banco de dados.
create user usr4 SUPERUSER CREATEDB INHERIT;
Usuário com password sem a cláusula WITH. Ao se cadastrar um usuário, a senha é por padrão criptografada.
create user usr5 PASSWORD 'lalala';
A sintaxe abaixo cadastra um usuário com senha não criptografada.
create user usr6 UNENCRYPTED PASSWORD 'lalala';
sexta-feira, 11 de maio de 2007
Configurando o Acesso ao Banco - O Arquivo pg_hba
O arquivo pg_hba armazena a lista de endereços IP que possuem permissão de acesso ao s bancos de dados armazenados no PostgreSQL e ainda informações sobre que bancos de dados e usuários podem fazer acesso aos mesmos.
ao se estringir os endereços IP autorizados a conectar com o servidor, consegue-se um grande ganho de segurança. A configuração do arquivo é bastante fácio de utilizar.
No windows, se encontra no caminho:
- C:\Program Files\PostgreSQL\8.*\data\pg_hba.conf
No Linux, pode ser visto na pasta:
- /etc/postgresql/pg_hba.conf
Basicamente, o pg_hba é um arquivo texto que possui em cada linha um registro de dados de autorização com os seguintes campos:
- Tipo de conexão
- Banco de Dados Permitido
- Usuário autorizado a acesso ao banco
- Endereço de conexão
- Método de autenticação
- Opção - Opções. Campo opcional dependendo do valor do campo Método de Autenticação.
- Tipo de conexão - Pode ser local, host, hostssl, hostnossl. Local - conexão Unix via socket, Host - conexão TCP/ IP criptogrsfada ou não (com ou sem SSL), Hostssl - conexão TCP/ IP sem criptografia.
- Banco de Dados Permitido - Nome do banco ou termo "all", "sameuser", "samerole", um nome de banco de dados ou uma lista de bancos de dados separada por vírgula.
- Usuário autorizado a acesso ao banco - Pode ser "all", um nome de usuário, um nome de grupo com o prefixo "+", ou uma lista separada por vírgula.
Para os campos Banco de Dados e Usuário, pode ser utilizado um nome de arquivo com prefixo "@", o qual será lido para o prrenchimento dos dados. Desta forma, é possível se alterar bancos e usuários sem liberar o acesso ao pg_hba.
- Endereço de conexão - Também chamado de CIDR-ADDRESS, é um campo opcional para conexão tipo local, sendo obrigatíorio nos demais. Corresponde a um endereço IP e uma máscara CIDR que é um inteiro entre 0 e 32 (IPv4) ou 128 (IPv6) que especifica o número de bits significativos da máscara.
Ex: 192.168.12.10/32
- Método de autenticação - Indica se a autenticação terá um nível maior ou menor de segurança. Pode ser: trust (sem verificação de password ou qualquer restrição), reject (rejeita conexões dos usuários citados com os bancos que constam na lista), md5 (requer senha criptografada para conexões), crypt (requer senha criptografada para conexões. Indicada para suceder o md5, nas versões acima da 7.2 do PostgreSQL),
password (demanda senha não criptografada para conexão), krb5 (utiliza Kerberos V5 para autenticar os usuários), ident (utiliza para a autenticação o login de sistema do usuário para conexões locais), pam (autenticação usando Pluggable Authentication Modules (PAM) do sistema operacional) .
Abaixo, alguns exemplos da internet:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# se for a minha própria máquina, não precisa autenticar...
host all all 10.15.21.244 trust
# deixar todos da rede 10.* se conectarem sem senha (perigo!)
#host all all 10.0.0.0 255.0.0.0 trust
# todos da rede 10.* com autenticação MD5
#host all all 10.0.0.0/8 md5
# o mesmo que acima, só que na rede 10.15.*
host all all 10.15.0.0/16 md5
# localmente, permitir tudo a todos sem exigir senha
local all all trust
Obs.: As alterações na configuração só são ser efetivadas ao se fazer o reload do pg_hba ou o restart do banco de dados.
ao se estringir os endereços IP autorizados a conectar com o servidor, consegue-se um grande ganho de segurança. A configuração do arquivo é bastante fácio de utilizar.
No windows, se encontra no caminho:
- C:\Program Files\PostgreSQL\8.*\data\pg_hba.conf
No Linux, pode ser visto na pasta:
- /etc/postgresql/pg_hba.conf
Basicamente, o pg_hba é um arquivo texto que possui em cada linha um registro de dados de autorização com os seguintes campos:
- Tipo de conexão
- Banco de Dados Permitido
- Usuário autorizado a acesso ao banco
- Endereço de conexão
- Método de autenticação
- Opção - Opções. Campo opcional dependendo do valor do campo Método de Autenticação.
- Tipo de conexão - Pode ser local, host, hostssl, hostnossl. Local - conexão Unix via socket, Host - conexão TCP/ IP criptogrsfada ou não (com ou sem SSL), Hostssl - conexão TCP/ IP sem criptografia.
- Banco de Dados Permitido - Nome do banco ou termo "all", "sameuser", "samerole", um nome de banco de dados ou uma lista de bancos de dados separada por vírgula.
- Usuário autorizado a acesso ao banco - Pode ser "all", um nome de usuário, um nome de grupo com o prefixo "+", ou uma lista separada por vírgula.
Para os campos Banco de Dados e Usuário, pode ser utilizado um nome de arquivo com prefixo "@", o qual será lido para o prrenchimento dos dados. Desta forma, é possível se alterar bancos e usuários sem liberar o acesso ao pg_hba.
- Endereço de conexão - Também chamado de CIDR-ADDRESS, é um campo opcional para conexão tipo local, sendo obrigatíorio nos demais. Corresponde a um endereço IP e uma máscara CIDR que é um inteiro entre 0 e 32 (IPv4) ou 128 (IPv6) que especifica o número de bits significativos da máscara.
Ex: 192.168.12.10/32
- Método de autenticação - Indica se a autenticação terá um nível maior ou menor de segurança. Pode ser: trust (sem verificação de password ou qualquer restrição), reject (rejeita conexões dos usuários citados com os bancos que constam na lista), md5 (requer senha criptografada para conexões), crypt (requer senha criptografada para conexões. Indicada para suceder o md5, nas versões acima da 7.2 do PostgreSQL),
password (demanda senha não criptografada para conexão), krb5 (utiliza Kerberos V5 para autenticar os usuários), ident (utiliza para a autenticação o login de sistema do usuário para conexões locais), pam (autenticação usando Pluggable Authentication Modules (PAM) do sistema operacional) .
Abaixo, alguns exemplos da internet:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# se for a minha própria máquina, não precisa autenticar...
host all all 10.15.21.244 trust
# deixar todos da rede 10.* se conectarem sem senha (perigo!)
#host all all 10.0.0.0 255.0.0.0 trust
# todos da rede 10.* com autenticação MD5
#host all all 10.0.0.0/8 md5
# o mesmo que acima, só que na rede 10.15.*
host all all 10.15.0.0/16 md5
# localmente, permitir tudo a todos sem exigir senha
local all all trust
Obs.: As alterações na configuração só são ser efetivadas ao se fazer o reload do pg_hba ou o restart do banco de dados.
quinta-feira, 10 de maio de 2007
Restaurando Backups...
Ao se fazer um backup com pg_dump e pg_dumpall, geralmente o resultado é armazenado em arquivos texto. Neste caso, não há problemas em se restaurar o backup. Basta se colar o texto do arquivo em algum programa e submetê-lo ao PostgreSQL.
No entanto, ao se fazer o backup, podem ter sido escolhidas opções de compactação que gerem um backup binário.
Nestas situações, pode ser utilizado o utilitário pg_restore para se recuperar os dados de sua cópia de segurança.
Caso o nome do banco de dados seja especificado, o utilitário recupera os dados dentro do banco. Do contrário, o mesmo transforma os dados recuperados em texto, gravando em um arquivo que pode ser utilizado para fazer uma restauração manual.
A sintaxe abaixo restaura um arquivo para o banco de dados:
pg_restore (caminho do arquivo de backup) -u -d (nome do banco de dados)
-u - faz com que o sistema solicite um usuário com permissões para realizar o restore (criação de objetos, por exemplo)
-d - indica o nome do banco de dados que vai receber o restore
Agora, um exemplo de restore para um arquivo texto:
pg_restore (caminho do arquivo de backup) -u -f (nome do arquivo de destino do restore)
-f - indica o caminho do arquivo texto que vai receber o restore na forma de script SQL
A geração para arquivo texto pode ser aconselhável quando se quer ter maior controle sobre as operações realizadas. No entanto, a descompactação dos dados pode exigir uma boa quantidade de espaço em disco. Meus testes revelaram que o arquivo restaurado chega facilmente a 20 vezes o tamanho do arquivo de backup original.
No entanto, ao se fazer o backup, podem ter sido escolhidas opções de compactação que gerem um backup binário.
Nestas situações, pode ser utilizado o utilitário pg_restore para se recuperar os dados de sua cópia de segurança.
Caso o nome do banco de dados seja especificado, o utilitário recupera os dados dentro do banco. Do contrário, o mesmo transforma os dados recuperados em texto, gravando em um arquivo que pode ser utilizado para fazer uma restauração manual.
A sintaxe abaixo restaura um arquivo para o banco de dados:
pg_restore (caminho do arquivo de backup) -u -d (nome do banco de dados)
-u - faz com que o sistema solicite um usuário com permissões para realizar o restore (criação de objetos, por exemplo)
-d - indica o nome do banco de dados que vai receber o restore
Agora, um exemplo de restore para um arquivo texto:
pg_restore (caminho do arquivo de backup) -u -f (nome do arquivo de destino do restore)
-f - indica o caminho do arquivo texto que vai receber o restore na forma de script SQL
A geração para arquivo texto pode ser aconselhável quando se quer ter maior controle sobre as operações realizadas. No entanto, a descompactação dos dados pode exigir uma boa quantidade de espaço em disco. Meus testes revelaram que o arquivo restaurado chega facilmente a 20 vezes o tamanho do arquivo de backup original.
segunda-feira, 7 de maio de 2007
Recuperando subconjuntos de linhas...
Em certas situações precisamos recuperar os dados em grupos de registros, por exemplo em pesquisas apresentadas ao usuário. Quem faz sites para a web se depara freqüentemente com esta situação. O PostgreSQL apresenta variações do comando select que permitem que se selecione exatamente os dados a serem buscados, com ganho de desempenho.
A sintaxe abaixo, recupera os primeiros 10 registros, utilizando a cláusula LIMIT:
select *
from TABELA
order by evento
limit 10 <==== Ao invés do número 10, pode ser atribuído qualquer número inteiro positivo.
A sintaxe abaixo, recupera os primeiros 10 registros, a partir do décimo quinto, utilizando as cláusulas LIMIT e OFFSET:
select *
from TABELA
order by evento
limit 10 offset 15
O emprego destas sinmtaxes economiza muito tempo de desenvolvimento e o desempenho das aplicações. No entanto, deve ser evitado caso sua aplicação seja portável, pois outros bancos de dados não suportam esta funcionalidade, ou a implementam utilizando outra sintaxe.
Obs: Scripts testados no PostgreSQL 8.2.
A sintaxe abaixo, recupera os primeiros 10 registros, utilizando a cláusula LIMIT:
select *
from TABELA
order by evento
limit 10 <==== Ao invés do número 10, pode ser atribuído qualquer número inteiro positivo.
A sintaxe abaixo, recupera os primeiros 10 registros, a partir do décimo quinto, utilizando as cláusulas LIMIT e OFFSET:
select *
from TABELA
order by evento
limit 10 offset 15
O emprego destas sinmtaxes economiza muito tempo de desenvolvimento e o desempenho das aplicações. No entanto, deve ser evitado caso sua aplicação seja portável, pois outros bancos de dados não suportam esta funcionalidade, ou a implementam utilizando outra sintaxe.
Obs: Scripts testados no PostgreSQL 8.2.
Backups no PostgreSQL - III
O utilitário pg_dumpall é o modo mais fácil para se fazer o backup de um banco todo, incluindo dados e metadados.
A sintaxe que mais utilizo é:
- pg_dumpall -U super_usuario_do_BD > nome_do_arquivo_de_backup_gerado
Simples assim.
O utilitário pedirá a senha do uusário digitado para realizar o backup e mais nada. Para melhor desempenho, sugiro que se crie o backup em um outro disco. Desta forma a segurança do banco também será aumentada.
Existem ainda várias opções adicionais que podem ser empregadas. Para conhecer todas, tecle:
- pg_dumpall --help
Chega de backups por enquanto!
A sintaxe que mais utilizo é:
- pg_dumpall -U super_usuario_do_BD > nome_do_arquivo_de_backup_gerado
Simples assim.
O utilitário pedirá a senha do uusário digitado para realizar o backup e mais nada. Para melhor desempenho, sugiro que se crie o backup em um outro disco. Desta forma a segurança do banco também será aumentada.
Existem ainda várias opções adicionais que podem ser empregadas. Para conhecer todas, tecle:
- pg_dumpall --help
Chega de backups por enquanto!
Assinar:
Postagens (Atom)