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;