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