Instituto Superior de Engenharia

do Porto
Departamento de Engenharia Informática

Autor: Miguel João Vieira Carvalho

Versão: 1.0
Data: Julho de 2003
 Agradecimentos

Agradecimentos

Gostava de agradecer de forma muito especial ao meu orientador, o Eng. Jorge

Coelho, pelo empenho que colocou neste projecto e pelas críticas e correcções que ao
longo das treze versões do documento foi sempre fazendo.
Gostava também de agradecer ao Eng. Alexandre Bragança pelo facto de ter-me
ajudado logo desde o início a seleccionar os temas que deveriam ser abordados e
também por ter sido um dos meus professores de bases de dados.
Quero também agradecer ao Eng. Aníbal Oliveira, por me ter feito entender na
cadeira de Bases de Dados I, de forma tão simples e eficaz, muitos dos conceitos que
foram apresentados ao longo deste documento.
Para finalizar queria agradecer ao meu irmão, João Carvalho, pelo apoio prestado na
elaboração de alguns elementos gráficos.

Página ii
 Índice

Índice

1. Conceitos Básicos 16

1.1. Breve História do Aparecimento das Bases de Dados 16

1.2. Requisitos Fundamentais de uma Base de Dados 17

1.2.1. Segurança 17

1.2.2. Integridade 18

1.2.3. Controlo da Concorrência 19

1.2.4. Recuperação/Tolerância a Falhas 19

1.3. SGBD Vs Sistema de Gestão de Ficheiros 20

1.3.1. Abstracção dos Dados 20

1.3.2. Independência Programa/Dados 20

1.3.3. Partilha dos Dados 21

1.3.4. Diminuição da Redundância 21

1.3.5. Desenvolvimento e Manutenção 22

1.3.6. Integridade dos Dados 22

1.4. Base de dados Relacionais 22

1.5. SQL 25

1.5.1. Aspectos do SQL 25

1.5.2. Linguagem de Definição de Dados 25

1.5.3. Linguagem de Manipulação de Dados 26

1.5.4. Triggers 26

1.5.5. Gestão de Transacções 27

2. SQL – Introdução 29

2.1. SQL no PostgreSQL 31

3. Instalação Linux 39

Página iii
 Índice

3.1. Instalação Linux Através de RPM 39

3.2. Instalação Linux Através de Source Code 40

4. Como utilizar 45

4.1. Clientes 45

4.1.1. psql 45

4.1.2. pgAccess 47

4.1.3. pgAdmin/II 48

4.1.4. winSQL 49

4.2. Como Utilizar – Exemplo Prático 50

4.2.1. Criação da Conta de Acesso 51

4.2.2. Criação da Base de Dados 52

4.2.3. Autorizar o Utilizador 53

4.2.4. Activar a Nova Configuração 56

4.3. Instalação do Driver ODBC do PostgreSQL 57

4.3.1. Criar o DSN 57

5. Programar com o PostgreSQL 61

5.1. A Interface de Programação libpq 61

5.1.1. Gestão de Ligações 62

[Link]. A Função PQconnectdb 63

[Link]. A função PQsetdbLogin 64

5.1.2. Funções Para Processamento Síncrono de Queries 64

[Link]. A Função PQexec 64

[Link]. A Função PQresultStatus 65

[Link]. A Função PQclear 65

5.1.3. Funções Para Obtenção de Informação do Resultado 65

5.1.4. Funções Para Obtenção de Valores do Resultado 66

5.1.5. Funções Para Processamento Assíncrono de Queries 67

Página iv
 Índice

6. Funcionalidades Avançadas 69

6.1. Índices 69

6.1.1. Aparecimento dos Índices 69

6.1.2. Os Índices nos SGDBs 73

[Link]. Representação por Árvores B+ 73

[Link]. Representação por Hashing 75

6.1.3. Dicas para Uso de Índices 78

6.1.4. Índices no PostgreSQL 81

[Link]. Índices Mono coluna 82

[Link]. Índices Multi coluna 82

[Link]. Índices Únicos 84

[Link]. Índices Funcionais 85

[Link]. Índices Parciais 85

6.1.5. Avaliar o Uso dos Índices 86

6.2. Stored Procedures 88

6.2.1. Vantagens/Desvantagens 89

6.2.2. A linguagem PL/pgSQL 89

[Link]. A linguagem PL/pgSQL 89

6.3. Triggers 95

6.3.1. Triggers no PostgreSQL 98

6.4. Controlo da Concorrência 98

6.4.1. Transacções 100

[Link]. Transacções no PostgreSQL 102

[Link]. Níveis de Isolamento 105

7. Campos Binários (BLOBs) 107

7.1. BLOBs no PostgreSQL 107

8. Usar o PostgreSQL com o PHP na Web 111

Página v
 Índice

8.1. Arquitectura Tecnológica 111

8.2. Acesso a Campos Binários em PHP 115

9. Comparação Entre SGBDs 119

9.1. Comparação MySQL com PostgreSQL 123

10. Conclusão 130

11. Bibliografia 135

12. Exemplo de execução síncrona usando libpq 138

12.1. Exemplo de execução assíncrona usando libpq 140

12.2. Modelo de dados de referência ao documento 143

Página vi
 Índice de Figuras

Índice de Figuras
Figura 1 – Resultado da execução do comando \d clientes 47
Figura 2 – Ecrã pgAccess (Linux) que mostra o conteúdo de uma tabela 48
Figura 3 – Ecrã principal do pgAdminII no Windows XP 49
Figura 4 – Ecrã do winSQL que mostra a parte do dicionário de dados 50
Figura 5 – Exemplo de um ficheiro pg_hba.conf 53
Figura 6 – Exemplo de um ficheiro pg_hba.conf com métodos de
autenticação password e md5 54
Figura 7 – Secção (tab) UserDSN 58
Figura 8 – Janela de criação de um DSN 58
Figura 9 – Configuração de um DSN para o PostgreSQL 59
Figura 10 – Caminho simplificado a percorrer desde um query até atingir
os dados em disco 70
Figura 11 – Camadas a percorrer para aceder aos dados através de um
índice 72
Figura 12 – Exemplo de uma árvore B+ de ordem dois 73
Figura 13 – 1º passo na inserção dum nó cheio 74
Figura 14 – Divisão da raiz causada pelo novo nó 74
Figura 15 - Versão final da árvore após inserção do elemento 8* 75
Figura 16 – Exemplo de uma tabela de hash 76
Figura 17 – Função de hash, após a inserção das chaves 20 e 40 76
Figura 18 – Tabela de hash com overflow no bucket 3 77
Figura 19 – Estrutura da tabela clientes 82
Figura 20 – Exemplo de um resultado do planner a usar o índice
ndx_cl_nome_idade_cp 83
Figura 21 - Exemplo de um resultado do planner que não pode usar o
índice ndx_cl_nome_idade_cp 84
Figura 22 - Esquema simplificado da arquitectura tecnológica usando
o PHP e PostgreSQL 111
Figura 23 - Estrutura da tabela imagens 115
Figura 24 – Resultados do teste de concorrência Read/Update.
Fonte: [33] 124
Figura 25 – Resultados da página “Bug Tracker”. Fonte: [33] 125
Figura 26 – Resultado do teste “Forum”. Fonte: [33] 126

Página vii
 Índice de Tabelas

Índice de Tabelas
Tabela 1 – Exemplos de domínios 23

Tabela 2 – Relação entre tributes e tuplos 23

Tabela 3 – Lista dos comandos internos do psql 47

Tabela 4 – Informação necessária para a criação de um DSN 59

Tabela 5 – As interfaces de programação (API) do PostgreSQL 61

Tabela 6 – Alguns valores possíveis para os estados da execução de

um query 65

Tabela 7 – Funções para obtenção de informação sobre o resultado 66

Tabela 8 – Funções para obtenção de valores do resultado 67

Tabela 9 – Dicas para uso de índices 80

Tabela 10 – Relação entre o tipo de índice, operadores suportados e

aplicação prática 81

Tabela 11 – Género de índices suportados 81

Tabela 12 – Relação entre o nível de isolamento e os três fenómenos

ligados às transacções 105

Tabela 13 – Bytes que necessitam de tratmento especial na inserção 107

Tabela 14 – Funções para tratamento anterior e posterior de BLOBs 108

Tabela 15 – Exemplo código PHP para extrair bytes de um campo bytea 114

Página viii
 Índice de Exemplos

Índice de Exemplos
Exemplo 1 - Query SELECT simples 34

Exemplo 2 - Query SELECT com filtragem 34

Exemplo 3 - Query SELECT com filtragem de duas expressões lógicas

e a cláusula BETWEEN 34

Exemplo 4 - Uso da função agregadora count num query SELECT 34

Exemplo 5 — Uso da função agregadora count juntamente com a

cláusula WHERE 34

Exemplo 6 - Uso das funções agregadoras min, max juntamente com a

cláusula GROUP BY 35

Exemplo 7 - Uso de sub queries 35

Exemplo 8 - Uso da cláusula AS juntamente com um join 36

Exemplo 9 - Uso de um join através da cláusula WHERE 36

Exemplo 10 - Uso de um join através da cláusula WHERE 37

Exemplo 11 - Uso da cláusula NOT IN e EXCEPT 37

Exemplo 12 – Parâmetros mais frequentes usados na compilação e seu

significado 41

Página ix
 Introdução

Introdução

Este documento pretende ser um tutorial sobre uma base de dados relacional e open
source que se chama PostgreSQL.
O público-alvo deste documento visa em primeiro lugar os principiantes na área das
bases de dados, sem nunca esquecer os utilizadores mais avançados.
No que respeita aos principiantes as abordagens dos assuntos, são feitas usando
situações do dia a dia, seguidas de exemplos de utilização prática e de alguns
conselhos para um melhor desempenho.
Em relação aos utilizadores com mais experiência, estes podem sem dúvida tirar
partido deste documento, uma vez que são abordados tópicos avançados.
Para dar suporte aos exemplos apresentados, usar-se um modelo de dados que foi
criado propositadamente no decurso deste trabalho. Este modelo diz respeito a um
cinema. Este foi o modelo de dados que me pareceu mais apropriado tendo em conta o
público alvo.

No primeiro capítulo e a pensar nos utilizadores novatos, começa-se por explicar

como é que apareceram bases de dados. São referidas características tais como:
segurança, integridade, acessos concorrentes, recuperação e tolerância a falhas. Para
explicar porque é que as bases de dados são importantes, faz-se uma analogia entre
um sistema de gestão de ficheiros e um SGBD – Sistema de Gestão de Bases de
Dados a vários níveis: abstracção dos dados; independência entre aplicação e dados;
partilha de dados; redução da redundância; integridade dos dados. Após vistas as
diferenças entre os sistemas anteriores, é feita a definição de SQL — Structured
Query Language e abordadas as sub linguagens de definição e manipulação dos
dados. São abordados mecanismos adicionais de manutenção de integridade, triggers
e transacções.

No segundo capítulo é feita uma introdução ao SQL em que são explicadas as

instruções de definição e manipulação de dados. Relativamente às de definição de
dados é explicada cada uma das instruções e significado de cada uma das várias
cláusulas, usando-se como base a criação do modelo de dados de referência. Em
relação às de manipulação, são abordadas as quatro instruções SQL (SELECT, INSERT,

UPDATE e DELETE). Em relação à instrução de criação de registos é explicada cada uma

Página 10
 Introdução

das várias formas que esta apresenta, juntamente com conselhos de utilização e
exemplos. Uma vez completa a introdução ao SQL, são apresentadas as características
da implementação do SQL a nível do PostgreSQL. A seguir dá-se início à
apresentação de perguntas em linguagem natural e o equivalente em termos de SQL
usando o modelo de referência. Ao longo das várias perguntas, vão sendo
introduzidos lentamente conceitos relacionados com forma de escrita dos queries.
Sempre que possível são apresentadas várias alternativas e respectivos prós e contras
para o uso de cada uma, abordando-se cláusulas desde as mais simples até às mais
complexas (ousa seja: INTERSECT, EXCEPT, etc.).

No terceiro capítulo é explicado todo o processo de instalação passo a passo do

PostgreSQL (acompanhado sempre que necessário de comentários) no Linux, quer
através de packages binários (RPMs) quer através de código fonte.

No quarto capítulo começa-se por enumerar alguns dos clientes Linux e Windows que
se podem usar para ligar ao PostgreSQL. Para cada um destes são mostradas as
características mais relevantes e ecrãs. Ainda relativamente aos clientes é explicado
como usar o cliente psql o qual é instalado por omissão. Uma vez terminada a
apresentação dos clientes, avança-se para um exemplo prático do que se deve fazer
para se ter acesso ao PostgreSQL. Em concreto, explica-se a criação de utilizadores;
criação de bases de dados; autorização de utilizadores usando os vários métodos de
autenticação. A pensar nos utilizadores que queiram aceder ou que tenham instalado o
PostgreSQL no Windows, é explicado passo a passo como é que se cria um Data
Source Name para ser usado pelos clientes que suportem ODBC - Open Database
Connectivity.

No quinto capítulo abordam-se assuntos relacionados com a utilização de linguagens

de programação com o PostgreSQL. Em concreto são identificadas todas as
linguagens em que é possível usar o PostgreSQL. É explicado e demonstrado através
de exemplos em linguagem C, o modo de funcionamento da interface de
programação. Nos últimos destacam-se: funcionamento do estabelecimento de
ligações quer assíncronas que síncronas; execução assíncrona e síncrona de queries;
obtenção de resultados e informações relativas aos resultados.

Página 11
 Introdução

No sexto capítulo são abordados todos os temas que julgo serem de uso mais
avançado, entre o qual se destacam: os índices; stored procedures; triggers; controlo
da concorrência e transacções. Em relação aos índices explicam-se quais os motivos
que levaram ao seu aparecimento. Apresentam-se as camadas a percorrer desde o
índice até aos dados para que o leitor fique com uma ideia de como estes funcionam.
Demonstra-se de seguida dois métodos de indexação, recorrendo a árvores B+ e
funções de hash. Relativamente a cada um dos métodos são explicados os
mecanismos usados na implementação e enumeradas as vantagens e as desvantagens
de cada um deles. Após a explicação dos métodos de indexação, fornecem-se dicas
relativamente a situações em que é oportuna a criação de índices e em que situações
não se devem sequer criar índices. Seguidamente são apresentados os tipos e géneros
de índices suportados pelo PostgreSQL. Durante a apresentação de cada um dos
géneros dos índices suportados, são mostrados exemplos de comandos para criação de
índices dos respectivos tipos e géneros. São ainda demonstrados e explicados quais os
factores que levam a que certos queries usem índices e outros não o usem. A
demonstração do uso e não uso dos índices é feita através da análise de planos de
execução de queries executados. No final da secção destinada aos índices são
apresentadas formas de como se deve verificar e avaliar o uso dos índices.
Em relação aos stored procedures é explicado o que são; quais as linguagens em que
podem ser escritos; enumeradas as vantagens do uso destes. Após a apresentação dos
stored procedures explica-se a linguagem procedimental PL/pgSQL recorrendo a
analogias e a exemplos práticos simples. Após introduzida a linguagem e apresentada
a estrutura base de um stored procedure em PL/pgSQL, são apresentados e explicados
alguns tipos de dados especiais, passando de seguida para a definição e uso de
cursores.
No que se refere aos triggers, explica-se o que é, para que servem e como devem ser
usados. Explicam-se os vários tipos de triggers suportados e os eventos que podem
ser usados e relação que existe entre o eventos e certas variáveis especiais criadas
pelo PostgreSQL. Após a definição dos eventos, avança-se para uma explicação
prática de como se define um trigger numa tabela. Com a informação geral sobre
triggers apresentada, são enumeradas algumas das funcionalidades que não são
implementadas ou são implementadas parcialmente pelo PostgreSQL a nível dos
triggers.

Página 12
 Introdução

No que respeita ao controlo da concorrência, é explicado no início o que se entende

por concorrência no âmbito dos SGBD. Seguidamente são apresentados os vários
mecanismos de bloqueio (tabelas, data pages, registos) usando um exemplo do dia a
dia como suporte. Ao longo da apresentação de cada um dos mecanismos de bloqueio
são apresentadas vantagens e desvantagens e respectivas consequências do uso.
Na abordagem das transacções é explicado o que é e para que servem, usando o
contexto do exemplo usado nos mecanismos de controlo da concorrência. São
apresentadas e explicadas as quatro características principais de uma transacção, mais
concretamente o princípio ACID — Atomicity, Consistency, Isolation and Durability.
Seguidamente será explicado qual o mecanismo usado pelo PostgreSQL para que as
transacções possam existir. Neste ponto é introduzido o conceito WAL — Write
Ahead Logging. De seguida é apresentada e explicada a forma como o PostgreSQL e
outros SGBDs conseguem manter a consistência sem que seja necessário usar
bloqueios quer de “leitores” quer “escritores” e vice-versa. Depois serão apresentados
os dois níveis de isolamento que o PostgreSQL suporta.

No sétimo capítulo e a pensar naqueles utilizadores que necessitem de armazenar

conteúdos binários (BLOBs) dentro das bases de dados, é explicada a forma standard
de o fazer e uma forma específica do PostgreSQL.

No oitavo capítulo fala-se sobre a arquitectura tecnológica necessária à interligação

entre PHP e PostgreSQL. Durante a apresentação, são desmistificados alguns erros
frequentes relacionados com as ligações às bases de dados por parte dos utilizadores
PHP. Seguidamente é explicado através de código PHP (comentado) e usando o
exemplo de referência do documento, como é que se insere uma imagem num BLOB e
como se extrai a mesma a partir da base de dados.

No nono capítulo faz-se uma comparação entre SGBDs open source e entre o
PostgreSQL e as implementações comerciais. Analisa-se alguns resultados de
benchmark realizados entre o MySQL e o PostgreSQL. Mostram-se algumas opiniões
de utilizadores que usam quer o MySQL quer o PostgreSQL.
No décimo capítulo será apresentada a conclusão do documento. Uma vez o
PostgreSQL é open source, será também abordado alguns dos receios dos utilizadores
em usar software open source.

Página 13
 Introdução

No décimo primeiro capítulo, são apresentadas as referências bibliográficas e links

que são mencionados ao longo do documento.

No décimo segundo capítulo são apresentados dois exemplos em linguagem C que

usam quer ligações síncronas que assíncronas. São ainda apresentados nesses mesmos
exemplos funções que permitem efectuar processamento síncrono e assíncrono de
queries. No final deste encontram-se as instruções DDL usadas para criar o modelo de
dados de suporte aos exemplos do documento.

Página 14
 01
1.1. Breve História do Aparecimento das Bases de Dados 16

1.2. Requisitos Fundamentais de uma Base de Dados 17

1.2.1. Segurança 17

1.2.2. Integridade 18

1.2.3. Controlo da Concorrência 19

1.2.4. Recuperação/Tolerância a Falhas 19

1.3. SGBD Vs Sistema de Gestão de Ficheiros 20

1.3.1. Abstracção dos Dados 20

1.3.2. Independência Programa/Dados 20

1.3.3. Partilha dos Dados 21

1.3.4. Diminuição da Redundância 21

1.3.5. Desenvolvimento e Manutenção 22

1.3.6. Integridade dos Dados 22

1.4. Base de dados Relacionais 22

1.5. SQL 25

1.5.1. Linguagem de Definição de Dados 25

1.5.3. Linguagem de Manipulação de Dados 26

1.5.4. Triggers 26

1.5.5. Gestão de Transacções 27

 Conceitos Básicos - Capítulo 1

1. Conceitos Básicos

Pretende-se com este capítulo abordar algumas das fases pelas quais o processamento
da informação foi passando ao longo dos tempos. Uma vez mencionados os factores
históricos, explica-se qual a finalidade de uma base de dados e quais os motivos pelos
quais estas devem ser usadas. Por fim explica-se como é que se pode tirar o devido
partido de uma base de dados.

Os conceitos expostos neste capítulo serão amplamente usados ao longo de todo o

documento, daí que é aconselhável que o leitor os compreenda.

1.1. Breve História do Aparecimento das Bases de Dados

Desde sempre as pessoas tiveram necessidade de registar e alterar os dados inerentes

às suas actividades. O meio empresarial foi aquele que mais sentiu a necessidade de
guardar os dados relativos às actividades empresariais uma vez que os negócios
dependiam desses mesmos dados armazenados. No início a organização era feita
através de arquivos que no seu interior guardavam fichas de papel (organizadas de
forma alfabética) que continham os dados relativos a cada transacção efectuada. Caso
fosse necessário, por exemplo, saber qual o total da facturação para um dado
momento da vida da empresa, era necessário que alguém somasse o valor de cada
transacção para cada um dos clientes. De regra geral, cada departamento da empresa
armazenava informação duplicada (ou seja: dados do cliente como morada, contactos,
etc.). Todo este processo por mais organizado que o arquivo estivesse não apresentava
um elevado grau de coerência e era como seria de esperar um processo bastante lento.

Com o aparecimento dos primeiros computadores, apareceram aplicações que

passaram a possibilitar a consulta da mesma informação (anteriormente armazenada
em arquivo e pastas de papel) mas de forma mais rápida. Desta forma havia várias
aplicações por departamento para fins específicos (facturar, consultar stocks, etc.).
Estas aplicações tinham como base uma série de ficheiros que eram independentes
entre cada uma das aplicações e que geralmente só podiam ser usados (salvo raras
excepções) por um único programa.

Página 16
 Conceitos Básicos - Capítulo 1

Contudo, com o uso das novas aplicações o processo embora fosse mais simples e
rápido continuava a haver a mesma informação (Por exemplo: morada, contactos dos
clientes, etc.) em vários ficheiros diferentes. Caso um cliente tivesse por algum
motivo que alterar a sua ficha de cliente, comunicava a alteração a alguém da
empresa, mas essa informação actualizada, raramente era fornecida aos outros
departamentos, causando desta forma incoerências.

Embora o processo estivesse bastante melhor, sempre que era necessário criar uma
listagem que ainda não tivesse sido criada anteriormente, era inevitável que alguém
criasse um programa que através da consulta directa dos ficheiros fosse capaz de
calcular os dados. Escusado será dizer que os recursos informáticos da altura não
tinham as mesmas capacidades de processamento dos que existem actualmente. Por
isso, mesmo que o processo fosse efectuado com o auxílio do computador este
continuava a demorar algum tempo e nestas coisas de negócios tempo é dinheiro.

Para combater os problemas mencionados até ao momento, as empresas viram

necessidade de agrupar todos os dados relativos a cada departamento da empresa (Por
exemplo: armazém, contabilidade, recursos humanos, etc.) num único sítio. Desta
forma todo o processo passou a ser mais rápido e mais coerente.

Uma base de dados não é mais do que um colecção de dados relativa a várias
actividades de uma ou mais instituições.

1.2. Requisitos Fundamentais de uma Base de Dados

Nesta secção serão abordadas características e conceitos que permitem compreender e

tirar o máximo partido das bases de dados.

1.2.1. Segurança

Da mesma forma que existe controlo de acessos aos recursos num sistema operativo
multiutilizador, geralmente suportado por um login/password, os SGBD também
usam esta forma de controlo de acessos para garantir um nível de segurança básico.
Uma vez que este mecanismo de segurança apresenta um grau de controlo muito

Página 17
 Conceitos Básicos - Capítulo 1

baixo e por isso é pouco flexível, é necessário utilizar mecanismos mais sofisticados.
Para que o grau de segurança seja aceitável é necessário que o SGBD permita definir
para cada um dos utilizadores autorizados quais as regras de acesso:

o Quem pode aceder – utilizadores aos quais se vai dar acesso à informação.
Por exemplo: o pessoal administrativo pode ter acesso, o pessoal do
armazém também, mas os operários não.

o Aceder a quê – dependendo do grau de sensibilidade da informação

armazenada na base de dados, poderá ser necessário autorizar e/ou negar
acesso a certo tipo de informações para alguns utilizadores da base de
dados. Por exemplo: o pessoal da contabilidade pode ver o salário de cada
funcionário, mas os operários já não.

o Aceder de que modo – tendo em conta as características de cada utilizador,

poderá ser necessário permitir um tipo de operações a uns e só alguns tipos
de operações a outros. Por exemplo: os gestores de uma empresa só
puderam consultar os dados, os funcionários administrativos só podem
criar novos registos e o administrador da base de dados pode executar todo
o tipo de operações.

1.2.2. Integridade

Diz-se que uma base de dados está num estado de integridade quando a base de dados
contem apenas dados que não contradigam a realidade que estão a representar. Por
exemplo: uma empresa tem uma base de dados com dados relativos ao processo
produtivo. Essa empresa tem três máquinas, a máquina A,B e C. Um dos operadores
registou uma encomenda que será realizada na máquina X.

A manutenção da integridade pressupõe proteger a base de dados de executar

operações que tentem comprometer a realidade dos próprios dados. Trata-se por isso
de um conjunto de medidas muito especial.

Página 18
 Conceitos Básicos - Capítulo 1

Desta forma todas as operações que ponham em causa a integridade da base de dados
são regidas por um conjunto de regras a que se chamam restrições de integridade.
Exemplos de algumas restrições de integridade:
o Saldo de uma conta bancária superior ou igual a zero.
o Na matrícula de um aluno, o nome não pode estar em branco.
o Autorizar um aluno a fazer um exame de recurso se tiver efectuado o
pagamento do pedido de exame.

1.2.3. Controlo da Concorrência

É um dos factores mais importantes a ter em conta no acesso por vários utilizadores
(em simultâneo) a uma base de dados. Este controlo permite que o utilizador não
pressinta a presença de outros utilizadores sobre a mesma base de dados. Assim sendo
cada utilizador tem a percepção de estar sozinho a trabalhar na base de dados, quando
na realidade podem estar vários utilizadores a executar operações muito diferentes
(consultas, actualizações a dados já existentes, criação de novos registos, etc.).
No caso de uma base de dados não suportar acessos concorrentes e se os utilizadores
acederem em simultâneo à base de dados, é muito provável que a integridade de toda
a base de dados possa ficar comprometida. Desta forma torna-se necessário que uma
base de dados multiutilizador tenha obrigatoriamente mecanismos para gerir o
controlo da concorrência.

1.2.4. Recuperação/Tolerância a Falhas

Para que seja possível ter a base de dados num estado de integridade válido é
necessário que haja a possibilidade de a recuperar de falhas através de uma forma
simples e rápida.
Para tal é aconselhada a realização de cópias de segurança de toda a base de dados de
uma forma regular. As cópias devem ser feitas em suportes amovíveis (ou seja: tape)
e armazenadas em locais fisicamente distantes do local onde se encontra o servidor.
No caso de ocorrer uma falha (crítica ou não) é sempre possível usar as cópias de
segurança para repor a base de dados num estado de integridade anterior.
Para que seja possível garantir todas estas características previamente referidas é
necessário que haja uma entidade que assegure tais funcionalidades.

Página 19
 Conceitos Básicos - Capítulo 1

À entidade que gere e controla o funcionamento das bases de dados dá-se o nome de
Sistema Gestor de Bases de Dados (SGBD) que provém do Inglês Database
Management System (DBMS).

1.3. SGBD Vs Sistema de Gestão de Ficheiros

Muito antes de aparecerem as bases de dados as aplicações armazenavam os dados em

ficheiros do próprio sistema, usando por isso as funcionalidades disponibilizadas pelo
sistema de ficheiros do sistema operativo. Algumas das operações disponibilizadas
são: criar ficheiros; deslocar para o byte n do ficheiro; deslocar para o início do
ficheiro; escrever no ficheiro, etc.

Estas aplicações de forma geral estavam associadas à estrutura de um ou mais

ficheiros. Estrutura de dados essa que geralmente consiste num conjuntos de dados de
comprimento fixo. A seguir encontram-se algumas das características mais
importantes dos SGBDs.

1.3.1. Abstracção dos Dados

Através da tecnologia das bases de dados não é necessário que as aplicações

conheçam pormenores dos dados tais como: comprimentos dos registos criados pelo
SGBD ou a localização física dos ficheiros no disco, uma vez que o
utilizador/programador trabalha sobre um modelo abstracto de dados.

1.3.2. Independência Programa/Dados

Usando um sistema de gestão de ficheiros como suporte de armazenamento uma

aplicação, leva a que os ficheiros fiquem ligados à própria aplicação. No caso de ser
necessário alterar a estrutura do ficheiro, torna-se também necessário alterar as
aplicações que usem esses mesmos ficheiros.

Ao usar-se um SGBD as aplicações passam a ser escritas de forma independente dos

métodos de armazenamento usados pelo próprio SGBD. Por exemplo: uma escola tem
uma aplicação para gerir a informação relativa aos dados pessoais dos seus alunos.

Página 20
 Conceitos Básicos - Capítulo 1

Uma vez que na altura em que a aplicação foi desenvolvida não havia telemóveis, não
foi reservado espaço no registo do aluno para esse efeito. Por decisão da direcção da
escola as funcionárias administrativas vão passar a armazenar os telemóveis dos
alunos juntamente com o registo do aluno. Como o ficheiro com a informação dos
alunos é necessário em quase todas as aplicações, então essas mesmas aplicações
terão que ser também alteradas, porque o comprimento dos registos também foi
alterado.
No exemplo anterior, se estivesse a ser usado um SGBD, era somente necessário
reservar espaço para o número de telemóvel e alterar a parte da aplicação que regista a
parte do número de telemóvel. Todas as restantes aplicações não teriam que ser
alteradas (se não necessitassem do número de telemóvel).

1.3.3. Partilha dos Dados

Numa abordagem baseada no Sistema de Gestão de Ficheiros, requisitos como

segurança e acessos concorrentes terão que ser fornecidos pela própria aplicação.
No caso de se usar um SGBD tais funcionalidades passam a ser fornecidas pelo
próprio SGBD, libertando assim a aplicação para a realização dos objectivos para que
foi criada.

1.3.4. Diminuição da Redundância

Nos SGBDs é possível conceber modelos de dados de forma a obter-se um grau de

redundância quase nulo. Desta forma torna-se mais simples manter a coerência dos
dados, bem como obter um grau de desempenho desejável. Há situações em que é
necessário adicionar alguma redundância (processo conhecido como
desnormalização), nomeadamente devido a motivos relacionados com o desempenho.
No entanto estes casos têm que ser pensados com muito cuidado, porque uma
desnormalização implica obrigatoriamente redundância. Na maioria das vezes é
possível melhorar o grau de desempenho sem ter que adicionar redundância.

Para mais informações consulte a secção 6.1.

Página 21
 Conceitos Básicos - Capítulo 1

1.3.5. Desenvolvimento e Manutenção

Uma vez que ao usar-se um SGBD estamos a trabalhar com um grau de abstracção
elevado, logo os programadores não têm que “perder” tempo com certos pormenores
irrelevantes à solução dos problemas (Por exemplo: concorrência, etc.) permitindo
assim que estes se dediquem à implementação das funcionalidades necessárias. Desta
forma os programadores tem mais tempo para melhorar a parte funcional da
aplicação, deixando responsabilidades tais como: gestão da concorrência e integridade
para o SGBD.

1.3.6. Integridade dos Dados

Através da definição de restrições de integridade e outros mecanismos é cada vez

mais, possível e aconselhável retirar certas “regras de negócio” das aplicações. Desta
forma a integridade da base de dados fica cada vez menos dependente das aplicações.
Ou seja em vez de se ter as regras de negócio dispersas por várias aplicações estas
passam a residir num único local.

1.4. Base de dados Relacionais

Dentro da tecnologia das bases de dados há modelos variadíssimos, entre os quais se

salientam os seguintes (ordenado cronologicamente)
• Modelo Hierárquico.
• Modelo de Rede.
• Modelo Relacional (RBDMS – Relational Database Management Systems).
• Modelo Relacional Orientado Objectos (OORDBMS – Object Oriented
Relational Database Management Systems).
• Modelo Orientado Objecto (OODBMS – Object Oriented Database
Management Systems).
• Outros modelos.

Neste documento será abordado somente os modelos Relacional e Relacional

Orientado a Objectos uma vez que são os modelos suportados pelo PostgreSQL.
O Modelo Relacional foi sem dúvida o que melhor se adaptou à realidade.

Página 22
 Conceitos Básicos - Capítulo 1

Esta adaptação bem sucedida deve-se ao facto deste ter nascido com base nas teorias
de conjuntos, em vez das técnicas de processamento de ficheiros como era o caso dos
seus antecessores.

Antes de se abordar o Modelo Relacional é necessário definir alguns conceitos

directamente relacionados:

Tabela – Consiste numa estrutura bidimensional constituída por linhas e

colunas.
As colunas definem os atributos, ou seja a parte fixa da estrutura da tabela. A
cada atributo está sempre associado um domínio ou seja um conjunto de
valores possíveis que o atributo pode ter. O domínio na prática é especificado
pelo tipo de dados que for atribuído ao atributo. A gama de valores a serem
aceites por parte do domínio pode ser encurtada através do uso de restrições de
integridade. Na Tabela 1 podem-se observar alguns exemplos de domínios
possíveis.

Atributo Domínio
Idade de uma pessoa Números inteiros
Sexo de uma pessoa Carácter
Data de nascimento Data

Tabela 1 – Exemplos de domínios

As linhas ou registos das tabelas também conhecidas como tuplos definem a

ocorrência dos dados a armazenar, ou seja, as linhas são a parte dinâmica da
tabela.
Atributos

distrito_codigo distrito_nome
Tuplo 4000 Porto
1000 Lisboa
4405 Valadares

Tabela 2 – Relação entre tributes e tuplos

Página 23
 Conceitos Básicos - Capítulo 1

Vista – Tabela sem existência física, cujo conteúdo é derivado de uma ou mais
tabelas.

Super chave – Conjunto de um ou mais atributos cujos valores quando

agrupados permitam identificar de forma única cada tuplo da tabela.

Chave primária – Corresponde à chave que é seleccionada a partir de uma

das super chaves.

Relação – Dependência lógica que existe entre uma ou mais tabelas.

Chave estrangeira – Corresponde a um ou mais atributos que formam uma

chave primária numa outra tabela. As chaves estrangeiras permitem criar o
conceito de relação entre tabelas.

Para que seja possível obter as características referidas em 1.3 é necessário que o
modelo de dados esteja normalizado.
Um modelo de dados diz-se normalizado quando o modelo resultante apresenta
redundância mínima. É claro que vai sempre haver alguma redundância mesmo que o
modelo seja considerado normalizado, uma vez que vai haver repetição de alguns
valores, nomeadamente os das chaves estrangeiras.
Para que se possa normalizar um modelos de dados é necessário ter conhecimento de
carácter semântico relativamente à informação a tratar.

O processo de normalização dos dados é uma fase muito importante da criação de um

modelo de dados. Uma vez que este assunto está fora do âmbito deste documento,
aconselha-se o leitor a consultar [2] ou outra bibliografia específica sobre o assunto.

Página 24
 Conceitos Básicos - Capítulo 1

1.5. SQL

Para que seja possível obter as características previamente abordadas em 1.3, é

necessário criar uma linguagem de alto nível que seja independente da implementação
do SGBD e que permita manipular a base de dados — a linguagem SQL – Structured
Query Language, a qual encontra-se definida em [25] e [26].
Nos dias de hoje o SQL é a linguagem standard para acesso a bases dados relacionais
e suas sucessoras (OORDBMS e OODBMS). Os standards SQL de referência são:
ANSI/ISO SQL também conhecido por SQL92 e o SQL 1999 o qual introduziu
algumas extensões importantes, as quais podem ser consultadas em [28] e [27].
O SQL foi criado com o intuito de ser uma linguagem simples de usar por parte do
utilizador, daí que o desempenho da execução dependa da forma como o SGBD está
implementado e não do modo como o query é escrito. O SGBD pode decidir por
razões de eficiência que o query a executar seja diferente do que foi escrito, mas cujo
resultado seja o mesmo.

1.5.1. Aspectos do SQL

Para que seja possível interagir com o SGBD é necessário que este disponibilize
mecanismos de alto nível. Para tal no âmbito do SQL, foram criadas as linguagens e
funcionalidades a seguir descritas.

1.5.2. Linguagem de Definição de Dados

A linguagem de definição de dados, conhecida na área das bases de dados como

DDL - Data Definition Language consiste num sub conjunto de instruções do SQL.
As instruções DDL permitem:
o Criar, alterar e remover as definições das tabelas e vistas.
o Criar restrições de integridade quer durante a fase de criação quer após a
criação.
o Manutenção de privilégios (permissões) nas tabelas, vistas e outros objectos da
base de dados.
o Criação, remoção e manutenção de outros objectos da base de dados.

Página 25
 Conceitos Básicos - Capítulo 1

1.5.3. Linguagem de Manipulação de Dados

A linguagem de manipulação de dados, conhecida na área das bases de dados sob a

sigla DML -Data Manipulation Language é um outro sub conjunto da linguagem
SQL.
As instruções DML usam-se com a finalidade de:
o Adicionar registos às tabelas.
o Alterar registos das tabelas.
o Remover registos das tabelas.
o Consultar informação presente em uma ou mais tabelas.

1.5.4. Triggers

Estes apareceram no standard SQL 1999 e são uma funcionalidade muito útil.
Um trigger consiste num pedaço de código SQL e/ou procedimental que é escrito
numa linguagem suportada pelo SGBD, armazenado dentro do SGBD e que é
executado quando ocorrem certos eventos na base de dados.

Exemplo de um trigger em pseudo código:

QUANDO INSERIR REGISTO EM CLIENTES FAZER:

Se cliente_idade <= 5 então
Mostra_erro(‘Idade inválida’)
Fim Se

Para mais informações sobre triggers por favor consulte a secção 6.3.

Página 26
 Conceitos Básicos - Capítulo 1

1.5.5. Gestão de Transacções

Uma transacção consiste numa sequência de instruções SQL que se querem que sejam
executadas de forma atómica, quer isto dizer que as instruções SQL dentro de uma
transacção são executadas de forma indivisível, ou seja como se de uma única
instrução se tratasse.
Os SGBDs possuem instruções que permitem alterar o nível de isolamento
pretendido para uma transacção.

Para mais informação sobre transacções por favor consulte a secção 6.4.1.

Página 27
 02
2. SQL – Introdução 29

2.1. SQL no PostgreSQL 31

 SQL Introdução - Capítulo 2

2. SQL – Introdução

Com esta secção pretende-se introduzir o leitor a alguns aspectos básicos, mas
importantes do SQL. Não é objectivo desta secção ensinar ao leitor todas as instruções
e/ou cláusulas possíveis do SQL. Para tal aconselha-se que siga [1] e [2].
No fim deste documento é apresentado o esquema e instruções DDL usadas para criar
a base de dados usada como referência ao longo deste documento. Aconselha-se o
leitor a consultar as relações existentes entre tabelas que se encontra no fim do
documento, com vista a entender o significado de cada pergunta colocada à base de
dados.

A forma mais simples de uma questão (query) em SQL tem a seguinte forma:

SELECT <atributo (s)>

FROM <tabela(s)>
WHERE <condição>

A instrução SELECT permite indicar ao SGBD que queremos consultar a informação

que esteja armazenada em uma ou mais tabelas. A directiva FROM serve para indicar
qual ou quais tabelas a consultar. A directiva WHERE é opcional e funciona como um
filtro. A condição pode envolver campos de uma ou mais tabelas especificadas na
cláusula FROM ou em alternativa pode ser o resultado de um outro SELECT.

Um outro tipo de query é o que permite adicionar novos registos, este apresenta a
seguinte forma:

INSERT INTO <tabela>(<campo(s)>) VALUES( <valor(es)>)

Exemplo: INSERT INTO DISTRITOS( distrito_codigo, distrito_nome ) VALUES( 4000, ‘Porto’)

Ou
INSERT INTO <tabela> VALUES( <valor(es)>)

Ou
INSERT INTO <tabela>(<campo(s)>) <query SELECT>

A instrução INSERT permite criar novos registos numa tabela, usando para o efeito os
valores fornecidos que é o caso da 1ª forma acima mencionada. Nesta forma a ordem
dos campos não interfere, uma vez que são especificados os seus nomes.

Página 29
 SQL Introdução - Capítulo 2

A segunda forma permite fazer o mesmo que a 1ª mas omitindo os nomes dos
campos. Nesta 2ª forma, a ordem dos campos é importante, por isso, a sequência de
valores especificados tem que estar ordenada da mesma forma como foram
especificados os campos na criação da tabela. Embora a 1ª forma seja mais difícil de
escrever, esta é a forma preferível, uma vez que se houverem alteração à estrutura da
tabela, os queries que existiam antes da alteração continuaram a ser executados com
sucesso. No caso da 2ª forma, tal já poderá não acontecer, ficando por isso dependente
da ordem atribuída pela implementação do SGBD. A 3ª forma permite que sejam
inseridos na tabela o conjunto de valores retornados pelo query SQL. Utilizando esta
forma, tem que se garantir que a ordem dos campos e respectivos domínios na tabela
estejam de acordo com a ordem e domínios dos campos retornados pelo query SQL.

Uma outra instrução SQL que existe para manipular as tabela é o UPDATE.
Esta como o próprio nome indica permite fazer actualizações aos registos existentes
de uma tabela. A forma é a seguinte:

UPDATE <tabela>
SET <campo1> = <valor1>, SET <campo2> = <valor2>
WHERE <condição>

A base de dados ao executar um query UPDATE vai alterar os valores do campos

especificados (os campos não especificados não são alterados) para os valores
fornecidos, para os registos que satisfação a condição fornecida na cláusula WHERE. A
cláusula WHERE pode ser uma condição que envolva somente os campos da tabela onde
está a ser feito o UPDATE ou podem ser o resultado de um SELECT qualquer.

Há ainda um outro tipo de query que é o que permite remover registos de uma tabela.
A forma deste é a seguinte:

DELETE FROM <tabela>

WHERE <condição>

O que o SGBD faz ao executar um query DELETE é remover da tabela especificada os

registos que satisfação a condição presente na cláusula WHERE. Caso não seja
especificada a cláusula WHERE, o SGBD removerá todos os registos da referida tabela.

Página 30
 SQL Introdução - Capítulo 2

A cláusula WHERE pode ser uma condição que envolva somente os campos da tabela
onde está a ser feito o DELETE ou pode ser o resultado de um query SELECT.

As instruções referidas até agora pertencem ao grupo das instruções de manipulação

dos dados, no fim do documento encontram-se instruções de definição das entidades
do exemplo de referência (tabelas, restrições de integridade, etc.) juntamente com
alguns comentários e/ou explicações.

2.1. SQL no PostgreSQL

Esta secção foi criada com vista a fornecer de forma rápida e simples aos utilizadores
uma visão global de todas as características mais importantes do PostgreSQL. Desta
forma, muitos dos termos usados só serão entendidos por utilizadores com alguma
experiência. As pessoas com menos experiência podem ver este ponto, mas não
devem ficar preocupadas se não entenderem alguns dos assuntos abordados uma vez
que alguns destes serão abordados ao longo do documento.

No que respeita à implementação SQL usada no PostgreSQL este suporta:

o Chaves Estrangeiras – Suporta a directiva standard CREATE TABLE ... FOREIGN

KEY. Possibilita a excussão de diferentes acções nos queries UPDATE e DELETE,

entre as quais: CASCADE, RESTRICT, SET NULL, SET DEFAULT.

o Joins – Implementa todos os tipos de joins referidos no standard SQL99:

inner join, left, right, full outer join, natural join. O optimizador tem à
disposição vários algoritmos disponíveis para os joins.
o Vistas – As vistas são queries SELECT que se encontram armazenados. Estas
são geralmente usadas para encapsular queries complexos ao nível do
servidor. O PostgreSQL também suporta vistas actualizáveis (as actualizações
realizadas sobre a view são efectuadas nas respectivas tabelas).
o Transacções – As transacções permitem que um conjunto de queries seja
executado pelo SGDB como se de um único de tratasse. A implementação das
transacções no PostgreSQL garante as seguintes propriedades standard:
atómicas, consistentes, isoláveis e duráveis.
Suporta ROLLBACK e SAVEPOINTS (previsto a partir da versão 7.4),

níveis de isolamento diferentes (READ COMMITTED e SERIALIZABLE).

Página 31
 SQL Introdução - Capítulo 2

o Controlo de concorrência – Usa uma técnica de nome MVCC – Multi-Version

Concurrency Control que é usada pela maioria dos SGBDs comerciais, como
é o caso do Oracle. O MVCC permite a criação de aplicações altamente
escaláveis, uma vez que as operações de leitura e escrita não bloqueiam quer
os leitores quer os escritores e também porque é uma técnica muito melhor do
que o bloqueio de registos, uma vez que não é necessário bloquear acessos a
um registo que esteja a ser actualizado, porque os valores que as transacções
vão ver é um snapshot (fotografia) do estado da base de dados antes de a
transacção ter sido iniciada.
o Triggers – Os triggers são procedimentos que são invocados pela base de
dados quando certas acções ocorrem. O PostgreSQL suporta triggers BEFORE

e/ou AFTER. Estes podem ser escritos em C ou numa linguagem procedimental.

Para mais informações consulte 6.2 e 6.3.
o Stored Procedures – O PostgreSQL permite a criação destes através das
seguintes linguagens: C; PL/pgSQL (muito parecida ao PL/SQL da Oracle);
Tcl; Perl; Python (em fase alpha) e Ruby. A partir da versão 7.3.x passou a
suportar o retorno de registos.
o Tipo de dados – O PostgreSQL suporta a maioria dos tipos de dados descritos
nas normas SQL92 e SQL99, dos quais se destacam: INTEGER, NUMERIC,

BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP. Estão disponíveis outros

tipos de dados, tais como: geométricos (linhas, pontos, etc.), endereços
TCP/IP, endereços MAC para placas Ethernet, ISBN/ISSN entre outros.
O PostgreSQL possibilita a criação de outros tipos de dados, bem como as
funções e operadores necessários ao seu uso.
o Suporta também BLOBs – Binary Large Objects. Estes podem ser lidos
totalmente ou parcialmente pela aplicação cliente.
o Internacionalização – Contém suporte para character sets internacionais,
codificações multi-byte e Unicode.
o Permite o uso das condições standard do SQL, entre as quais: CASE...WHEN,

COALESCE e NULLIF.
o Sub queries – Os sub queries permitem que o SGBD possa responder a
perguntas complexas. O uso destes, pode melhorar o desempenho das
aplicações, bem como tornar a ideia intrínseca ao sub query mais legível e
fácil de compreender.
Página 32
 SQL Introdução - Capítulo 2

o Contém suporte para SELECT DISTINCT e SELECT DISTINCT ON( coluna ).

o Total suporte para GROUP BY e funções agregadoras, entre as quais: COUNT, SUM,

AVG, MIN, MAX, STDDEV e VARIANCE. Novas funções agregadoras podem ser
criadas em qualquer uma das linguagens suportadas pelo PostgreSQL.
Para mais informações consulte 6.2.
o Suporta sub selects usados na cláusula FROM tal como: SELECT COUNT(x),

AVG(x), SUM(x) FROM (SELECT SUM(weight) AS x FROM Items GROUP BY

manufacturer) AS Items;
o Suporta as cláusulas UNION e UNION ALL, INTERSECT e EXCEPT.
o Contém extensões para a cláusula LIMIT e OFFSET de forma a permitir a
selecção de um número arbitrário de registos. (Por exemplo: SELECT * FROM

Items ORDER BY cost LIMIT 5).

o Contém funções internas que permitem a manipulação e conversão de tipos de
dados tais como: matemáticos, date/time e strings.
o Suporta indexação total de texto.
o Suporte para a cláusula SQL LIKE e todo o tipo de expressões regulares
definidas pelo standard POSIX.
o Suporte para carregamento de funções externas tais como: soundex, funções
hash ( SHA1 e MD5).
o Índices – Os índices podem ser do tipo: árvores B, árvores R e Gist (usados
geralmente em sistemas de informação geográficos). Os métodos de indexação
podem ser definidos pelo utilizador, com a possibilidade de adicionar e
remover índices de qualquer tipo a qualquer momento.
o Replicação – É uma das funcionalidades que serão certamente adicionadas,
uma vez que têm grande urgência na lista de funcionalidades a implementar. A
partir da versão 7.4 (actualmente estamos na 7.3.3) está previsto replicação do
tipo master/slave, multi-master/slave.

Neste tópico encontram-se alguns exemplos comentados e explicados de queries SQL

que podem ser executados (na realidade foram executados) pelo PostgreSQL, não
querendo isto significar que o PostgreSQL esteja limitado às funcionalidades
abordadas nestes exemplos, muito pelo contrário.

Página 33
 SQL Introdução - Capítulo 2

A bold será assinalada a diferença entre o query anterior e o query actual, sempre que
seja necessário.

Questão Qual a idade, sexo e nome de todos os clientes?

Query SELECT cliente_idade, cliente_nome, cliente_sexo
FROM clientes

Exemplo 1 - Query SELECT simples

Questão Qual a idade, sexo e nome dos clientes com idade superior a 18 anos?
Query SELECT cliente_idade, cliente_nome,cliente_sexo
FROM clientes
WHERE cliente_idade >= 18

Exemplo 2 - Query SELECT com filtragem

Questão Qual a idade, sexo e nome dos clientes com idade entre 18 e 30 anos?
Query SELECT cliente_idade, cliente_nome, cliente_sexo
FROM clientes
WHERE cliente_idade >= 18 AND cliente_idade <= 30

Uma outra forma de escrever este mesmo query seria:

SELECT cliente_idade, cliente_nome, cliente_sexo
FROM clientes
WHERE cliente_idade BETWEEN 18 AND 30

Exemplo 3 - Query SELECT com filtragem de duas expressões lógicas e a cláusula BETWEEN

Questão Quantos clientes existem no total?

Query SELECT count(*)
FROM clients

Notas O * na cláusula count significa que a contagem diz respeito a qualquer

atributo da tabela.

Exemplo 4 - Uso da função agregadora count num query SELECT

Questão Qual o total de clientes do sexo masculino?

Query SELECT count( cliente_sexo )
FROM clientes
WHERE cliente_sexo = ‘M’

Exemplo 5 — Uso da função agregadora count juntamente com a cláusula WHERE

Página 34
 SQL Introdução - Capítulo 2

Questão Qual a idade do cliente mais velho e mais novo de cada um dos sexos?
Query SELECT cliente_sexo, max( cliente_idade ),
min(cliente_idade)
FROM clientes
GROUP BY cliente_sexo

Notas O query resultante deve ter dois tuplos (1 por cada sexo). Para que tal
seja possível temos que indicar ao SGBD como é que este vai fazer o
agrupamento dos resultados. Uma vez que queremos saber o sexo para
além do máximo e mínimo da idade, então temos que indicar ao SGBD
que o atributo a usar para o agrupamento dos resultados é o campo
cliente_sexo. Para tal usa-se a directiva GROUP BY.

Exemplo 6 - Uso das funções agregadoras min, max juntamente com a cláusula GROUP BY

Questão Qual o nome, morada do cliente mais velho de todos?

Query SELECT CL.cliente_nome, CL.cliente_morada
FROM clientes as CL
WHERE CL.cliente_idade = ( SELECT max( cliente_idade )
FROM clientes
)

Notas O query anterior é composto por um sub query.

A execução deste começa sempre pelo último query na cadeia, ou seja,
no caso acima vai começar pelo query que calcula o cliente mais
velho. Após o cálculo filtra o resultado através da idade.

Exemplo 7 - Uso de sub queries

Umas das operações mais importantes em SQL são os chamados Joins (junções). Um
join consiste em obter informação através da junção de várias tabelas.

Página 35
 SQL Introdução - Capítulo 2

Questão Qual o nome, morada e localidade dos clientes?

Query SELECT clientes.cliente_nome, clientes.cliente_morada,
codigo_postal.localidade_nome
FROM clientes, codigo_postal
WHERE clientes.codigo_postal_id =
codigo_postal.codigopostal_id

Há uma outra forma de ser escrever o mesmo query anterior.

SELECT CL.cliente_nome, CL.cliente_morada,

CP.localidade_nome
FROM clientes as CL, codigo_postal as CP
WHERE CL.codigo_postal_id = CP.codigopostal_id

Notas Para que seja possível mostrar a informação da localidade, é necessário

juntar os dados residentes em duas tabelas: clientes e codigo_postal.
Repare-se que no 1º query está o nome da tabela precedido do nome do
campo.
O segundo query é mais simples de se escrever e de se ler. A única
alteração que se fez neste último em relação ao primeiro foi fornecer
um sinónimo para as tabelas, através da cláusula AS.
É boa prática preceder os nomes dos campos com os nomes das tabelas
ou sinónimos.

Exemplo 8 - Uso da cláusula AS juntamente com um join

Questão Qual o nome, morada dos clientes que vivem no distrito cujo código é
4000?
Query SELECT CL.cliente_nome, CL.cliente_morada
FROM clientes as CL, codigo_postal as CP
WHERE CP.distrito_codigo = 4000 AND
CL.codigo_postal_id = CP.codigopostal_id

Notas Repare-se que a tabela codigo_postal foi incluída no query somente para
se efectuar a filtragem, não aparecendo por isso nenhum atributo desta
na cláusula SELECT

Exemplo 9 - Uso de um join através da cláusula WHERE

Página 36
 SQL Introdução - Capítulo 2

Questão Qual o nome, morada dos clientes que vivem no distrito do Porto?
Query SELECT CL.cliente_nome, CL.cliente_morada
FROM clientes as CL, codigo_postal as CP,distritos D
WHERE D.distrito_nome=’Porto’ AND
CP.distrito_codigo = D.distrito_codigo AND
CL.codigo_postal_id = CP.codigopostal_id

Notas Repare-se que a tabela distritos foi incluída no query somente

para se efectuar a filtragem, não aparecendo por isso nenhum
atributo desta na cláusula SELECT.

Exemplo 10 - Uso de um join através da cláusula WHERE

Questão Qual o código dos distritos que existem que não são usados no
código postal?
Query SELECT distrito_codigo
FROM distritos
WHERE distrito_codigo NOT IN( SELECT distrito_codigo
FROM codigo_postal
)

Outra forma de resolver o query anterior será através do uso de

operadores de tuplos (Set Operators), neste caso a cláusula EXCEPT.

SELECT distrito_codigo FROM distritos

EXCEPT
SELECT distrito_codigo FROM codigo_postal

Exemplo 11 - Uso da cláusula NOT IN e EXCEPT

Página 37
 03
3.1. Instalação Linux Através de RPM 39

3.2. Instalação Linux Através de Source Code 40

 Instalação Linux - Capítulo 3

3. Instalação Linux

Há duas formas de se instalar o PostgreSQL no Linux:

• Através da compilação do código fonte (source)
• Através de ficheiros binários pré-compilados (ficheiros .rpm)

3.1. Instalação Linux Através de RPM

Antes de mais é necessário que verifique se o PostgreSQL não está já instalado no

sistema. Para isso com a conta de administração (root), corra o seguinte comando:
rpm –qa | grep –i “postgres”

Caso o package ainda não esteja instalado, por favor continue a ler.

Antes de mais é necessário ter acesso aos ficheiros rpm.

Geralmente todas as distribuições (ou seja: RedHat, Suse, Mandrake, etc.) trazem os
ficheiros rpm necessários quer do servidor quer dos utilitários e bibliotecas de suporte.
Caso a distribuição que usa não traga o PostgreSQL, é possível descarregar os
ficheiros binários em [9] ou em [10].

A sequência de RPMs a instalar deve ser a seguida abaixo e usando os nomes

fornecidos. Nos nomes dos ficheiros .rpm, x.x deve ser substituído pelos respectivos
valores das versões. Os nomes dos RPMs a instalar são os seguintes:
• [Link]
• [Link]
• [Link]

Para instalar o PostgreSQL basta que corra para cada um dos RPMs o seguinte
comando como root:
rpm –ivh <nome do package>
Por exemplo: rpm –ivh [Link]

Página 39
 Instalação Linux - Capítulo 3

3.2. Instalação Linux Através de Source Code

Mudar para o directório onde se vai compilar (não é o directório de destino do

PostgreSQL).
Por exemplo: cd /home/miguel

Ir buscar o source. Este pode ser descarregado através de HTTP em [9] ou através de
FTP em [10]. Após ter o ficheiro do source, tem que o descomprimir, usando para o
efeito o comando:
tar –zxvf [Link].

Para quem estiver familiarizado com a compilação de software via source, os passos a
seguir são:

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb teste
/usr/local/pgsql/bin/psql teste

Para aqueles que necessitam de compilar o PostgreSQL via source e que nunca
tenham compilado qualquer software, devem seguir os passos que se seguem.
Nota: é necessário ter acesso à conta de administração (root).

1º Passo: Configuração da instalação

Comando: ./configure
Notas: Nesta fase da instalação é onde se fazem a maioria das decisões ao nível das
funcionalidades necessárias, nomeadamente a localização por defeito no sistema de
ficheiros dos ficheiros das bases de dados e o local onde os ficheiros do PostgreSQL
serão instalados; porta TCP a usar para o caso dos acessos remotos ao servidor;

Página 40
 Instalação Linux - Capítulo 3

métodos de autenticação a usar; interfaces a suportar (Perl, Tcl, etc.). Abaixo seguem-
se alguns parâmetros úteis.

Para obter uma listagem completa de todos os parâmetros consulte [11] ou execute o
comando ./configure --help.

Parâmetros:
--prefix Permite especificar qual a localização dos ficheiros e sub directórios
necessário no sistema de ficheiros. Por defeito, o PostgreSQL é
instalado com --prefix=/usr/local/pgsql, quer isto dizer que todos os
ficheiros e sub directórios serão criados hierarquicamente abaixo
deste directório. Este parâmetro é bastante útil uma vez que permite
que o servidor seja instalado por utilizadores sem permissões de
administração.
--with- Indica em tempo de compilação qual a porta TCP a usar para
pgport=X
estabelecer ligações com o servidor. Esta porta é por defeito a 5432.
O número da porta especificado tem que ser obrigatoriamente
superior a 1024, uma vez que as portas entre 0 e 1024 são portas
privilegiadas, o que significa que só o utilizador é root que pode ter
processos à escuta nestas. O servidor não pode ser iniciado por um
utilizador com privilégios de administração por motivos de
segurança.
Este parâmetro é de grande utilidade quando é necessário ter várias
versões do PostgreSQL a correr na mesma máquina. O parâmetro
que controla a porta a usar também pode ser alterado no ficheiro de
configuração.
--with- Indica em tempo de compilação qual o número máximo de conexões
maxbackends=X
que podem existir em simultâneo no servidor.
Por defeito este valor é de 32. Este parâmetro pode ser alterado
através do ficheiro de configuração.

Exemplo 12 – Parâmetros mais frequentes usados na compilação e seu significado

Página 41
 Instalação Linux - Capítulo 3

2º Passo: compilar o software

Comando: gmake
Notas: O processo de compilação pode demorar entre 5 minutos até ½ hora
dependendo do hardware usado.

3º Passo: testar o servidor antes de instalar

Comando: gmake check
Notas: Para executar este teste é necessário que o utilizador que lhe deu início não
seja nenhum administrador (root). Se assim não for o teste falham, devido ao facto de
o servidor por motivos de segurança não ter iniciado devidamente.
Este comando vai correr os chamados testes de regressão. Estes testes permitem
verificar se o servidor irá correr tal como previsto.

4º Passo: instalar o servidor, copiar directórios e ficheiros de suporte para o directório

destino
Comando: gmake install
Notas: Antes de executar o comando acima, certifique-se que tem as devidas
permissões no directório de destino.

5º Passo: criar conta para o utilizador que inicia o servidor

Comando: adduser postgres
Notas: Para executar este comando, tem de ter permissões de administração (root).

6º Passo: criar o directório onde os ficheiros das bases de dados serão criados
Comando: mkdir /usr/local/pgsql/data
Notas: Para executar este comando, tem de ter permissões de administração (root).

7º Passo: trocar o owner do directório onde vão residir as bases de dados

Comando: chown postgres /usr/local/pgsql/data
Notas: Troca o owner do directório /usr/local/pgsql/data para postgres.

8º Passo: entrar na conta do utilizador postgres

Comando: su - postgres
Notas: Para executar este comando, tem de ter permissões de administração (root).

Página 42
 Instalação Linux - Capítulo 3

9º Passo: criar as tabelas de suporte ao SGBD

Comando: /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Notas: Este comando irá criar todos os ficheiros e directórios de suporte ao SGBD, no
directório especificado pelo parâmetro –D. Internamente este comando vai criar as
chamadas tabelas de sistema também conhecidas por catalog tables.
Este passo só deve ser executado uma única vez e durante a instalação.

10º Passo: iniciar o servidor

Comando: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
Notas: Este comando vai inicializar o servidor, indicando que o directório onde o
servidor deve guardar os dados é o directório especificado pelo parâmetro –D e
redireccionando todo o output quer o stderr quer o stdout para o ficheiro logfile.

11º Passo: criar uma base de dados

Comando: /usr/local/pgsql/bin/createdb teste
Notas: Este comando vai criar uma base de dados de nome teste.
Ao ser executado o comando acima, não será pedida qualquer autenticação, uma vez
que todos os utilizadores (incluindo o utilizador postgres) têm permissão de se
ligarem a qualquer base de dados a partir da máquina local quer via sockets Unix quer
via TCP. É aconselhado que as configurações do ficheiro pg_hba.conf sejam
alteradas.

12º Passo: estabelecer ligação à base de dados criada no passo anterior

Comando: /usr/local/pgsql/bin/psql teste
Notas: Usando o cliente que é instalado por defeito (o psql), vai entrar na base de
dados teste. Tal como já foi explicado no passo anterior, não será pedida qualquer
autenticação.

Página 43
 04
4 Como utilizar 45

4.1. Clientes 45

4.1.1. pgsql 45

4.1.2. pgAccess 47

4.1.3. pgAdmin/II 48

4.1.4. winSQL 49

4.2. Como Utilizar – Exemplo Prático 50

4.2.1. Criação da Conta de Acesso 51

4.2.2. Criação da Base de Dados 52

4.2.3. Autorizar o Utilizador 53

4.2.4. Activar a Nova Configuração 56

4.3. Instalação do Driver ODBC do PostgreSQL 57

4.3.1. Criar o DSN 57

 Como Utilizar - Capítulo 4

4. Como utilizar

Pretende-se com este capítulo demonstrar como se deve usar o PostgreSQL e como
aceder a este. Entre as aplicações possíveis serão apresentadas aplicações para
plataformas Linux/Unix e Windows. Em relação às aplicações Windows para além da
apresentação das mesmas, será também explicado como se deve instalar e configurar
o driver de ODBC do PostgreSQL.
Para se poder utilizar o PostgreSQL é necessário em 1º lugar termos o servidor
instalado e a correr.

Para informações sobre a instalação consulte a secção 3.

A partir deste ponto presume-se que já existe uma base de dados e uma conta de
acesso criada.

Para informações sobre os procedimentos a utilizar para a criação da base de dados

e criação da conta, consulte a secção 4.2.

Uma vez que já temos uma base de dados e uma conta de acesso, resta-nos escolher
um cliente (programa que serve de interface entre o utilizador e o servidor).

4.1. Clientes

Alguns dos clientes mais utilizados são:

• Psql (Linux/Unix).
• PgAccess (Linux/Unix).
• PgAdmin/II (Windows).
• Winsql (Windows).

4.1.1. psql

O psql é um cliente “modo texto” que é instalado pelo PostgreSQL. Este permite
digitar os queries e ver os resultados de forma interactiva.

Página 45
 Como Utilizar - Capítulo 4

Este é o cliente de eleição para se tirar o máximo partido do PostgreSQL uma vez que
suporta várias opções e operações. O pgsql é geralmente usado para tarefas tais como:
• Automatização de operações de manutenção.
• Importação/exportação de dados.
• Visualização de informações variadas sobre as bases de dados e outros
objectos.

Para que o pgsql fosse uma ferramenta fácil e de rápido uso, a equipa do PostgreSQL
decidiu criar uma série de comandos internos ao próprio pgsql tal como se pode
observar na tabela seguinte. Quer isto dizer que estes comandos só existem no pgsql e
por isso não podem ser utilizados através de outros clientes. Na realidade alguns dos
comandos internos não passam de meras consultas ao dicionário de dados do
PostgreSQL.

Comando Descrição
\d Mostra todas as relations (tabelas, sequências, índices, etc.) da base de
dados actual.
\dt É uma especialização do comando \d. O \dt permite mostrar o nome de
cada uma das tabelas existentes e o nome do utilizador que as criou.
\d nome O comando \d quando seguindo de um nome de uma tabela, índice,
sequência ou vista, mostra a informação inerente ao objecto, tal como
se pode ver através da Figura 1 da página 47.
Este comando é o equivalente ao comando DESC utilizado por exemplo
pelo Oracle.
\l Lista o nome de todas as bases de dados PostgreSQL existentes.
Este comando é o equivalente ao comando SHOW DATABASES utilizado
por algumas bases de dados.
\i ficheiro Permite a execução dos queries SQL e/ou comandos internos que
estejam armazenados no ficheiro indicado.
\o ficheiro Permite o envio do resultado de um query para o ficheiro indicado ou
para um pipe. O resultado será afectado pelas opções activas no
momento. Ou seja: se por exemplo a opção de gerar os resultados em
HTML estiver activa, o resultado será código HTML.

Página 46
 Como Utilizar - Capítulo 4

\? Permite obter uma lista de todos os comandos internos possíveis

acompanhados de uma explicação breve. Muitos destes não foram aqui
abordados.

Tabela 3 – Lista dos comandos internos do psql

Na Figura 1 está presente toda a informação relativa à estrutura da tabela. A zona a

amarelo (a primeira) representa a informação base da tabela. Na coluna um encontra-
se os nomes dos campos; na coluna dois o tipo de dados do campo; na coluna três esta
presente outras opções.
A informação presente na zona verde (a segunda) diz respeito às restrições de
integridade aplicadas aos campos.
Na zona de cor magenta (a terceira) está presente informação relativa à integridade
referencial.

Figura 1 – Resultado da execução do comando \d clientes

4.1.2. pgAccess

O pgAccess em relação ao psql não é tão poderoso, mas mesmo assim tem as suas
vantagens. O pgAccess é um cliente “modo gráfico”, multi-plataforma
(Unix/Windows) escrito em Tcl/Tk e tem as seguintes características:
• Possibilidade de criação, modificação, visualização de tabelas. views,
queries, sequências, funções, formulários, diagramas, etc.
• Possibilidade de importação/exportação de tabelas/ficheiros.
• Criação de base de dados.
• Inserção, alteração e remoção de dados nas tabelas.

Página 47
 Como Utilizar - Capítulo 4

A Figura 2 mostra um exemplo da interface do pgAccess.

Figura 2 – Ecrã pgAccess (Linux) que mostra o conteúdo de uma tabela

Para mais informações sobre o pgAccess, consulte [6].

4.1.3. pgAdmin/II

O pgAdminII é um cliente com interface gráfica para o Windows. O acesso à base de

dados é efectuado através de ODBC.
As características mais relevantes são:
• Interface tipo explorer para visualizar o servidor PostgreSQL. Veja a
Figura 3 da página 49.
• Criação de edição de utilizadores, grupos, bases de dados, funções
agregadoras, funções, índices, linguagens, operadores, regras, sequências,
tabelas, triggers, tipos de dados e vistas.
• Wizard para criação de queries SQL.
• Wizard para migração da base de dados.
• Wizard para tratar os aspectos da segurança.
• Outras.

Página 48
 Como Utilizar - Capítulo 4

Figura 3 – Ecrã principal do pgAdminII no Windows XP

Para mais informações sobre o pgAdminII, consulte [7].

4.1.4. winSQL

O winSQL embora não seja um cliente específico para o PostgreSQL, este pode
considerar-se um bom cliente uma vez que possibilita o acesso a qualquer SGBD,
desde que o respectivo driver ODBC esteja instalado.

As principais características do winSQL são as seguintes:

• Enviar queries para qualquer base de dados que suporte ODBC.
• Obter meta dados para as tabelas, vistas, triggers, stored procedures, etc.
• Criar queries SQL através de wizard.
• Apresentação de resultados sob a forma de uma grelha.
• Reverse engineer a uma base de dados.

Tal como se pode observar na Figura 4 da página 50, a informação disponível vai
desde: os nomes das tabelas, vistas e funções, até informação mais detalhada de cada
uma das tabelas, nomeadamente: os atributos, índices, constraints, tipos de dados, etc.

Página 49
 Como Utilizar - Capítulo 4

Figura 4 – Ecrã do winSQL que mostra a parte do dicionário de dados

Para mais informações sobre o winSQL, consulte [8].

Para mais informações sobre outros clientes e projectos relacionados com o

PostgreSQL consulte [12].

4.2. Como Utilizar – Exemplo Prático

Partindo do princípio que se acabou de instalar o PostgreSQL e que o servidor está a

correr, são necessários vários passos até se poder aceder a uma base de dados.
Para tal são os passos seguintes são necessários:
• Criar conta de acesso.
• Criar base de dados.
• Autorizar o utilizador.
• Activar a nova configuração.

Página 50
 Como Utilizar - Capítulo 4

4.2.1. Criação da Conta de Acesso

Partindo do princípio que temos uma conta (Unix) aberta e cujo utilizador não é o
administrador (root).
Entrar no sistema como root executando o comando su – root. Após ter-se entrado
com sucesso é necessário entrar com a conta (Unix) de administração do PostgreSQL
(postgres) criada durante a fase de instalação. Para tal deve-se executar o seguinte
comando: su – postgres. Com a conta de administração do PostgreSQL activa,
executa-se o comando: createuser nome_utilizador. Este comando geralmente
encontra-se em /usr/local/pgsql/bin/ ou em /usr/bin/.

Ao se executar o comando: createuser teste, serão feitas várias perguntas de forma

interactiva ao utilizador, respeitantes aos privilégios do novo utilizador. Isto acontece
uma vez que este comando não é mais do que uma interface para o comando CREATE

USER que é executado pelo psql.

O comando createuser -A -d -P teste significa:

• Criar um utilizador com o login teste.
• Sem permissões para criar contas de novos utilizadores (parâmetro –A).
• Com permissões para criar novas bases de dados (parâmetro –d).
• Com password a ser fornecida de forma interactiva.

De salientar que é aconselhável por motivos de segurança que os novos utilizadores

não possam criar novos utilizadores. Esta tarefa deve ser executada somente pelo
administrador da base de dados.

É possível criar utilizadores sem o auxílio do comando createuser. Para tal é

necessário entrar no psql e digitar o comando CREATE USER nome_utilizador.

Para mais informações sobre este comando digite \h CREATE USER dentro do
psql ou consulte [30] .

Página 51
 Como Utilizar - Capítulo 4

4.2.2. Criação da Base de Dados

Uma vez que o utilizador já se encontra criado, chegamos então à fase de criação da
base de dados. Para iniciar esta fase o utilizador já não necessita de ser o
administrador da base de dados. Isto porque durante a criação foi especificado que o
utilizador tinha privilégios para criar bases de dados. Se tal não tivesse sido
especificado, então teria que se usar obrigatoriamente uma conta que tivesse tais
permissões ou a conta de administração (geralmente postgres).

Tal como aconteceu com a criação da conta, também há duas formas de criar uma
base de dados. A primeira forma consiste em usar o comando createdb e a segunda
consiste no uso de um query a ser digitado no psql.

Ao executar-se o comando createdb –U teste –W teste_bd está-se a indicar que se

pretende criar uma base de dados de nome teste_db (último parâmetro), utilizando a
conta do utilizador teste (parâmetro –U) e cuja password seja pedida de forma
interactiva (parâmetro –W).

Para criar a base de dados pelo 2º método é necessário entrar no psql com uma conta
de utilizador e executar o comando: CREATE DATABASE nome_base_dados. Através deste
último será criada uma base de dados com o nome de nome_base_dados que terá
associada aos parâmetros de criação todos os valores por omissão adoptados pela
implementação.
Algumas das opções extra que se podem escolher são:
• Login do dono (owner).
• Localização dos ficheiros no sistema de ficheiros.
• Codificação.
• Outros.

Para mais informações sobre este comando digite \h CREATE DATABASE dentro
do psql ou consulte [29].

Página 52
 Como Utilizar - Capítulo 4

4.2.3. Autorizar o Utilizador

Nesta fase será dada permissão ao utilizador criado anteriormente para que este se
possa ligar à base de dados.
Existe um ficheiro cujo nome é pg_hba.conf (PostgreSQL host base authentication)
residente geralmente em /usr/local/pgsql/data que serve para gerir o processo de
autenticação. Na Figura 5 encontram-se as configurações por omissão para o ficheiro
pg_hba.conf.

Figura 5 – Exemplo de um ficheiro pg_hba.conf

Por omissão todos os utilizadores que se liguem localmente, entenda-se a partir da

máquina onde reside o PostgreSQL, tem acesso imediato ao servidor sem que seja
necessário password, tal como se pode observar na Figura 5.
Desta forma é aconselhável que este comportamento seja alterado, uma vez que
qualquer utilizador que tenha uma conta na máquina onde reside o servidor e que
conheça uma conta de acesso ao PostgreSQL, poderá ligar-se a qualquer base de
dados sem que lhe seja perguntada qualquer password.
A alteração a efectuar resume-se a trocar o modo de autenticação de trust para
password (passwords não cifradas) ou md5 (passwords cifradas), tal como se pode ver
através da Figura 6 da página 54.

Página 53
 Como Utilizar - Capítulo 4

Figura 6 - Exemplo de um ficheiro pg_hba.conf com métodos de autenticação password e md5

Uma vez que por omissão só os utilizadores locais podem ligar-se, é necessário
adicionar uma nova linha que permita que o novo utilizador possa aceder através de
uma máquina remota. Para tal deve-se adicionar uma linha semelhante à que se segue:

Tipo Base Dados Utilizador Endereço IP Máscara IP Método

Autenticação
host teste teste [Link] [Link] password

A primeira coluna define qual a forma como a informação das restantes colunas será
interpretada. Os valores possíveis para o tipo são:
• local – Define que o tipo de acesso será através de sockets Unix.
• host - Define que o tipo de ligação será via TCP/IP.
• hostssl – Define que o tipo de acesso será TCP/IP seguro, protegido
através de SSL – Secure Socks Layer.

A 2ª coluna define quais os nomes das bases de dados (separados por vírgulas) a que a
linha respeita. Existe um valor especial all que diz respeito a todas as base de dados.
Há no entanto outro valores especiais que não são mencionados aqui.

A 3ª coluna define quais os logins dos utilizadores (separados por vírgulas) a que a
linha respeita. Existe um valor especial all que diz respeito a todos os utilizadores.
Há no entanto outros valores especiais que não são mencionados aqui.

Para mais informações sobre os outros valores especiais que o campo pode tomar,
consulte [14].

Página 54
 Como Utilizar - Capítulo 4

A 4ª e 5ª colunas definem o endereço IP e respectiva máscara a usar. Através destes

dois parâmetros é possível definir uma máquina ou uma gama de endereços. Estas
duas colunas só são aplicadas a registos do tipo host ou hostssl.

A 6ª coluna define o tipo de autenticação. Os valores possíveis são:

• trust – A ligação é permitida incondicionalmente. Este método permite
que qualquer utilizador consiga ligar-se à base de dados em questão, como
sendo qualquer um dos utilizadores previamente criados (mesmo
administrador) sem que seja pedido qualquer password.
• reject – Este método permite que a ligação seja rejeitada através da
análise do IP e da máscara. Este é útil para situações do tipo: não permitir
a ligação dos hosts do departamento de Recursos Humanos.
• md5 – Este método obriga que o cliente use uma password cifrada com
MD5 – Message Digest 5.
• crypt – Parecido com o método md5, mas usa um algoritmo de cifragem
antigo o DES — Data Encryption Standard. Este é necessário para clientes
anteriores à versão 7.2.
• password – a password é enviada de forma não cifrada (clear-text). Este
método não é aconselhável em redes não confiáveis entre as quais se
destaca a Internet.
• krb4 – usa Kerberos V4 para autenticar o utilizador. É obrigatório usar
TCP/IP.
• krb5 – usa Kerberos V5 para autenticar o utilizador. É obrigatório usar
TCP/IP.
• ident – Só em ligações TCP/IP. Usa um servidor ident do lado do cliente
para obter o login usado no sistema operativo. Esta forma não é nada
segura e só deve ser usada caso o cliente seja de confiança. No caso de
ligações locais o login é obtido directamente a partir do sistema operativo.
Este método é bastante útil para automatização de tarefas de manutenção
como é o caso da criação de backups.

Página 55
 Como Utilizar - Capítulo 4

• pam – Autentica o utilizador através de PM - Pluggable Authentication

Modules. O PAM consiste num método de autenticação flexível
geralmente usado em Unix/Linux.
Para mais informações sobre o processo de autenticação, consulte [14].

4.2.4. Activar a Nova Configuração

Uma vez feitas alterações no ficheiro de autenticação é necessário indicar ao

PostgreSQL que este foi alterado. Para tal é necessário executar o seguinte comando:
pg_ctl reload -D DATADIR. Em que DATADIR consiste no directório onde o ficheiro
pg_hba.conf reside, geralmente em /usr/local/pgsql/data/.

Página 56
 Como Utilizar - Capítulo 4

4.3. Instalação do Driver ODBC do PostgreSQL

Antes de se poder instalar o driver é necessário descarregar o mesmo através de [38].

Caso a versão do Windows seja o Windows 2000/XP então pode usar os ficheiros
com a extensão .msi, uma vez que estas plataformas já trazem instalado o Microsoft
Installer. Para as restantes versões é necessário descarregar o ficheiro com a extensão
.exe.

Caso já tenha instalado e configurado algum driver de ODBC - Open Database

Connectivity não necessita de ler a secção que se segue. Caso contrário, por favor
continue a ler.

Após a instalação do driver é necessário criar um DSN – Data Source Name para que
seja possível aceder ao servidor onde reside o PostgreSQL. Um DSN consiste num
nome atribuído pelo utilizador pelo qual será possível aceder à base de dados através
de uma aplicação que use ODBC, como é o caso do winSQL.

4.3.1. Criar o DSN

Para se criar o DSN deverá abrir o Control Panel.

Para o Windows2000/XP deve entrar em:

Administrative Tools -> Data Sources (ODBC)

Para o Windows 95/98/NT deve entrar em:

ODBC –Data Sources (32bit)

De seguida deverá aparecer uma janela semelhante à figura abaixo.

Página 57
 Como Utilizar - Capítulo 4

Figura 7 – Secção (tab) UserDSN

Nessa janela certifique-se que a secção (tab) User-DSN está visível.

Clique sobre o botão Adicionar (Add).

Vai aparecer uma janela semelhante à da figura abaixo.

Figura 8 – Janela de criação de um DSN

Desloque-se para o fim da lista e seleccione a linha cujo nome é PostgreSQL e

pressione Fim (Finish).

Página 58
 Como Utilizar - Capítulo 4

Vai-lhe aparecer uma janela igual à que se encontra abaixo.

Figura 9 – Configuração de um DSN para o PostgreSQL

Para cada um dos campos digite:

Campo Descrição
Data Source Nome do DSN a ser fornecido às aplicações ODBC que usem o
PostgreSQL.
Database Nome da base de dados à qual se pretende ligar.
Server Nome DNS ou endereço IP do servidor PostgreSQL.
User Name Nome do utilizador a usar para estabelecer a ligação.
Port Porta TCP/IP a ser usada. Por defeito é a porta 5432.
Password Password do utilizador.

Tabela 4 – Informação necessária para a criação de um DSN

De seguida pressione o botão Save.

Nota: O botão Datasource só deve ser usado pelos utilizadores que já tenham alguma
prática com o PostgreSQL, uma vez que permite configurar opções avançadas.

Embora existam vários clientes para usar com o PostgreSQL, é possível programar
com este através de várias API’s – Application Programming Interface.

Para mais informações consulte a secção 5.

Página 59
 05
5.1. A Interface de Programação libpq 61

5.1.1. Gestão de Ligações 62

[Link].A Função PQconnectdb 63

[Link].A função PQsetdbLogin 64

5.1.2. Funções Para Processamento Síncrono de Queries 64

[Link].A Função PQexec 64

[Link].A Função PQresultStatus 65

[Link].A Função PQclear 65

5.1.3. Funções Para Obtenção de Informação do Resultado 65

5.1.4. Funções Para Obtenção de Valores do Resultado 66

5.1.5. Funções Para Processamento Assíncrono de Queries 67

 Programar Com o PostgreSQL - Capítulo 5

5. Programar com o PostgreSQL

Pretende-se com este capítulo demonstrar quais as capacidades do PostgreSQL no que
respeita às API’s – Application Programming Interface suportadas por este.

As interfaces de programação (API) suportadas pelo PostgreSQL são:

Interface de Descrição
programação
libpq Interface de programação para aplicações escritas em C.
libpq++ Interface de programação para aplicações escritas em C++.
libperl Interface de programação para aplicações escritas em Perl.
Php Interface de programação para aplicações escritas em PHP.
Pgtcl Interface de programação para aplicações escritas em TCL.
Ecpg Interface de programação para aplicações escritas C com código SQL
embebido, parecido ao PRO C do Oracle.
Jdbc Interface de programação para aplicações escritas Java.
pygresql Interface de programação para aplicações escritas em Python.

Tabela 5 – As interfaces de programação (API) do PostgreSQL

Embora o PostgreSQL suporte todas as API mencionadas acima, neste documento

serão somente abordadas as interfaces de programação: libpq e php. A primeira terá
obrigatoriamente que ser abordada em maior pormenor, uma vez que a grande
maiorias das restantes interfaces a usam.
A interface PHP será abordada porque é um dos grandes focos de uso nos sites Web.

5.1. A Interface de Programação libpq

A libpq é a API do PostgreSQL para a linguagem C. A libpq consiste num conjunto

de rotinas que permite aos programas cliente enviar queries SQL para o servidor e
receber o resultado desses mesmos. Esta API é também a base de outras API’s como é
o caso da libpq++ (C++), libpgtcl (TCL), Perl e a ecpg (C com SQL embebido) e do
PHP, por isso esta será abordada em maior pormenor do que as restantes.

Página 61
 Programar Com o PostgreSQL - Capítulo 5

Na secção dos anexos, nas páginas 138 - 144 encontram-se dois exemplos de código
comentado em linguagem C que ilustra o uso das funcionalidades mais frequentes da
API.

Esta API disponibiliza as seguintes funcionalidades:

• Funções para gestão de ligações.
• Funções para processamento síncrono de queries.
• Funções para processamento assíncrono de queries.
• Funções para debugging da própria API.

5.1.1. Gestão de Ligações

As funções que se seguem são as funções responsáveis pelo estabelecimento de

ligações a uma base de dados PostgreSQL.

PGconn *
PQconnectdb(const char *conninfo)

PGconn *
PQsetdbLogin(char *host, char *port, char *ops, char *tty, char *dd,
char *login, char *pwd
)

Estas funções permitem a abertura de uma ligação à base de dados usando a

informação presente no parâmetros e devolvem um descritor para a ligação
estabelecida em caso de sucesso. Como se pode ver pela assinatura da primeira
função, esta é a função flexível.
Há outras funções e/ou macros que asseguram a mesma funcionalidade, mas são
pequenas variações da primeira.

Para mais informações sobre esta e outras funções, consulte [15].

Página 62
 Programar Com o PostgreSQL - Capítulo 5

[Link]. A Função PQconnectdb

PGconn *
PQconnectdb(const char *conninfo)

O parâmetro conninfo deve ser formado por pares de valores do tipo chave = valor

em que as constantes possíveis para chave são:

host

Nome do host ao qual se quer estabelecer a ligação. Caso o nome comece por
\, então o valor representa o nome do directório para um socket Unix, caso
contrário representa o FQHN (Full Qualified Host Name) do host.
hostaddr

Endereço IP do host ao qual se quer estabelecer a ligação. O endereço deve

estar representado segundo a forma decimal separada por pontos ( ou seja:
com o formato [Link]).
Esta chave permite que a ligação possa ser estabelecida sem que seja
necessário conhecer o FQHN do host, o que para algumas aplicações com
restrições temporais elevadas é crucial (o caso dos sites na Internet).
port

Número da porta TCP a usar para o estabelecimento da ligação ou o nome da

extensão para o ficheiro do socket Unix.
dbname

Nome da base de dados à qual é para estabelecer ligação.

user

Login do utilizador
password

Password do utilizador.
connect_timeout

Valor em segundos a usar como time-out para o estabelecimento da ligação.

options

Opções de debug a serem enviadas para o servidor.

tty

Nome de um ficheiro ou de um terminal (tty) para o qual a informação de

debug deve ser enviada.

Página 63
 Programar Com o PostgreSQL - Capítulo 5

requiressl

Se o valor da chave for 1, o servidor é obrigado a estabelecer uma ligação SSL

ao servidor. Caso a chave seja 0 (por omissão), o tipo de ligação é negociado
com o servidor. Esta opção só esta disponível caso o PostgreSQL tenha sido
compilado com suporte para SSL.
Caso alguma chave não seja especificada, a correspondente variável de ambiente será
usada. Caso não esteja disponível a variável de ambiente, o valor hard coded gerado
durante a compilação será usado.

[Link]. A função PQsetdbLogin

PGconn *
PQsetdbLogin(char *host, char *port, char *ops, char *tty, char *dd,
char *login, char *pwd
)

Esta função é a antecessora da pqconnectdb e por isso apresenta as mesmas

funcionalidades.

5.1.2. Funções Para Processamento Síncrono de Queries

Neste ponto serão abordadas as funções que asseguram a execução de queries, bem
como as funções necessárias à extracção de informação sobre o resultado.

[Link]. A Função PQexec

PGresult *PQexec(PGconn *conn, const char *query);

Esta função envia o query SQL para o servidor e aguarda pela resposta.
O valor retornado encapsula o resultado recebido a partir do servidor. Aconselha-se os
programadores a manterem as propriedades ADT - Abstract Data Type do resultado,
ou seja não usar os campos da estrutura directamente, mas sim usarem as funções e/ou
macros existentes, uma vez que é muito provável que a estrutura dos campos seja
alterada em versões futuras.

Página 64
 Programar Com o PostgreSQL - Capítulo 5

[Link]. A Função PQresultStatus

ExecStatusType PQresultStatus(const PGresult *res)

Esta função permite obter o estado em que o descritor do resultado se encontra após a
execução. É através desta função que se pode saber se ocorreu um erro no parsing
e/ou execução do query. O valor retornado consiste num tipo enumerado em que os
valores mais frequentes são os que se encontram na tabela abaixo.

Valor devolvido Descrição

PGRES_COMMAND_OK Sucesso na execução de um query que não devolve resultados
( ou seja: INSERT, UPDATE, DELETE, etc.).
PGRES_TUPLES_OK Sucesso na execução de um query que devolve resultados (ou
seja: SELECT), mesmo que não hajam linhas no resultado.
PGRES_FATAL_ERROR Indica que o parsing ou mesma a execução do query falhou.

Tabela 6 – Alguns valores possíveis para os estados da execução de um query

[Link]. A Função PQclear

void PQclear(PQresult *res);

Esta função permite que os recursos do descritor associados à execução de um query

sejam libertados. Esta função deve ser invocada quando o resultado do query não for
necessário. Em caso de erro na execução, esta função tem que ser invocada para que a
memória usada pelo descritor seja devolvida ao sistema, desta forma evitando fugas
de memória (memory leak).

5.1.3. Funções Para Obtenção de Informação do Resultado

int PQntuples(const PGresult *res);
int PQnfields(const PGresult *res);
char *PQfname(const PGresult *res, int field_index);
int PQfnumber(const PGresult *res, const char *field_name);
int PQbinaryTuples(const PGresult *res);

Página 65
 Programar Com o PostgreSQL - Capítulo 5

As funções apresentadas acima e na tabela abaixo permitem obter informação relativa

ao resultado obtido. Não confundir a finalidade desta função como sendo a função
responsável pelo obtenção dos resultados propriamente ditos.

Função Descrição
PQntuples Devolve o número de tuplos (registos) presentes no resultado.
PQnfields Devolve o número de campos (colunas) presentes em cada linha do
resultado.
Pqfname Devolve o nome do campo que está associado ao índice indicado
pelo parâmetro field_index. Os índices começam em 0.
PQfnumber Devolve o índice do campo que está associado ao nome do campo
indicado pelo parâmetro field_name. Os índices começam em 0.

Tabela 7 – Funções para obtenção de informação sobre o resultado

5.1.4. Funções Para Obtenção de Valores do Resultado

char* PQgetvalue(const PGresult *res, int tup_num, int field_num);

int PQgetisnull(const PGresult *res, int tup_num, int field_num);

int PQgetlength(const PGresult *res, int tup_num, int field_num);

As funções apresentadas acima e na tabela abaixo permitem obter os valores relativos

ao resultado. Não confundir a finalidade desta função como sendo a função
responsável pela obtenção de informação relativa ao resultado.

Função Descrição
PQgetvalue Obtém o valor do registo identificado pelo índice de tuplo tup_num

e da coluna cujo índice é identificado por field_num. Os índices

quer dos tuplos quer das colunas começam em 0.
PQgetisnull Verifica se o valor do registo identificado pelo índice de tuplo
tup_num e da coluna cujo índice é identificado por field_num é NULL
(contexto do SQL).

Página 66
 Programar Com o PostgreSQL - Capítulo 5

PQgetlength Devolve o tamanho em bytes do valor do registo identificado pelo

índice de tuplo tup_num e da coluna cujo índice é identificado por
field_num. Os índices quer dos tuplos quer das colunas começam
em 0.

Tabela 8 – Funções para obtenção de valores do resultado

5.1.5. Funções Para Processamento Assíncrono de Queries

Embora o PostgreSQL suporte a execução assíncrona de query e estabelecimento

assíncrono de ligações, este tópico não será abordado neste documento uma vez que é
um mecanismo usado em situações pouco frequentes.

Na secção dos anexos, encontra-se um exemplo de código em linguagem C

comentado que ilustra o uso desta funcionalidade.

Para mais informações sobre este assunto, consulte [15] e [16].

Página 67
 06
6.1. Índices 69

6.1.1. Aparecimento dos Índices 69

6.1.2. Os Índices nos SGDB’s 73

[Link].Representação por Árvores B+ 73

[Link].Representação por Hashing 75

6.1.3. Dicas para Uso de Índices 78

6.1.4. Índices no PostgreSQL 81

[Link].Índices Monocoluna 82

[Link].Índices Multicoluna 82

[Link].Índices Únicos 84

[Link].Índices Parciais 85

6.1.5. Avaliar o Uso dos Índices 86

6.2. Stored Procedures 88

6.2.2. A linguagem PL/pgSQL 89

6.3. Triggers 95

6.3.1. Triggers no PostgreSQL 98

6.4. Controlo da Concorrência 98

6.4.1. Transacções 100

[Link].Transacções no PostgreSQL 102

[Link].Níveis de Isolamento 105

 Funcionalidades Avançadas - Capítulo 6

6. Funcionalidades Avançadas

Nesta secção serão abordados alguns dos mecanismos que existem no PostgreSQL
mas que geralmente não são de uso frequente. Não que isto dizer que não sejam
importantes, senão vejamos os assuntos a abordar: índices; stored procedures;
triggers; concorrência; transacções e níveis de isolamento.

Foi feito um enorme esforço para que os assuntos abordados nas próximas secções
fossem acessíveis quer para as pessoas principiantes no meio, quer para aquelas que já
têm alguns conhecimentos. Para tal e a pensar nos utilizadores menos experientes, é
dada uma introdução sobre o tema, acompanhando sempre que oportuno com
exemplos do quotidiano. Para os utilizadores mais experientes há assuntos que são
abordados de forma mais aprofundada, chegando em algumas situações a haver
referências à implementação.

6.1. Índices

Nesta secção começa-se por explicar o aparecimento dos índices, de seguida é dada
uma perspectiva mais de implementação destes. Aborda-se os vários tipos de índices
suportados, quais as vantagens e desvantagens. Explica-se com comandos DDL como
é que se podem criar os vários tipos de índices. De seguida são fornecidas várias dicas
sobre o que se deve e não se deve indexar e de que forma indexar. Na parte de
avaliação de uso de índices são mostrados e analisados alguns exemplos de planos
efectuados pelo planner (motor de planeamento do SGBD) do PostgreSQL.

6.1.1. Aparecimento dos Índices

Como certamente será do conhecimento do leitor, o grande problema com que os

SGBDs se debatem desde à vários anos está relacionado com a quantidade de I/O
(Input/Output) a nível de acesso à memória secundária (ou seja: discos rígidos).

Se tivermos um ficheiro que está organizado sob a forma de registos e se quiserem

procurar um registo com alguma informação específica que está algures armazenada
no ficheiro, a solução é ler o ficheiro bloco a bloco, sequencialmente desde o inicio

Página 69
 Funcionalidades Avançadas - Capítulo 6

até ao fim. A esta operação dá-se o nome de varrimento sequencial, também

conhecido na área das bases de dados como sequencial scan.
Como se pode constatar através da descrição acima, esta operação de pesquisa é muito
ineficiente e dispendiosa a nível de recursos (quer de CPU que de acessos a disco).

Para minorar estas questões os SGBDs como é o caso do Oracle e o PostgreSQL usam
na sua arquitectura interna uma série de zonas de memória partilhada que
desempenham a função de cache. Este nível de cache encontra-se entre o SGBD e o
Sistema Operativo como se pode observar na Figura 10.

Figura 10 – Caminho simplificado a percorrer desde um query até atingir os dados em disco

Analisando a figura acima pode-se ver que a aplicação faz chegar o query ao SGBD
sob a forma de uma string que contem um comando SQL. Esse mesmo comando ao
ser passado ao motor de processamento de queries, é transformado numa
representação interna ao SGBD. Uma vez obtida a representação interna, o motor,
pede os respectivos registos das tabelas ao gestor de buffers, o qual primeiro verifica
se esses mesmos se encontram em algum blocos de dados (data page) que exista nos
buffers partilhados. Se existirem nos últimos, é percorrido o caminho inverso. Se não,
é feito o pedido ao gestor de armazenamento, o qual fica encarregue de devolver os
blocos de dados necessários (data pages). Este no limite por sua vez pode ter que
consultar vários ficheiros (se a tabela for muito grande). O gestor de armazenamento,
por sua vez, usa1 os serviços do sistema operativo para ter acesso aos dados que foram

1
Por exemplo no caso do Oracle o gestor de armazenamento não interage com a implementação de
ficheiros “normais” do próprio sistema, em vez destes são usados os chamados raw files. Isto acontece

Página 70
 Funcionalidades Avançadas - Capítulo 6

pedidos. O sistema operativo em si, transforma o pedido do gestor numa localização

em termos físicos para o disco. Uma vez obtidos a localização em disco do respectivo
bloco (não é bloco de dados), o sistema operativo dá a instrução ao controlador de
disco para que este satisfaça o seu pedido. Assim que os dados tiverem sido
lidos/escritos, é necessário percorrer o caminho inverso, onde no limite, é enviada o
resultado do query para o cliente.

Embora a técnica de caching dos blocos seja um mecanismo útil, esta não consegue
resolver todos os problemas de desempenho inerentes aos acessos. Isto passa-se
porque à medida que vão sendo pedidos novos blocos que não existam na cache do
SGDB, este armazena esses novos blocos na memória.
Uma vez que a memória é um recurso escasso (relação às capacidades dos discos
rígidos), o SGDB vai ter que substituir uns blocos da cache por novos blocos. Embora
estas substituições sejam regidas pelo uso de algoritmos com esse fim (Por exemplo:
LRU-Least Recently Used e MRU-Most Recently Used) e também recorrendo a
estatísticas de acessos anteriormente efectuados, este método de caching não resolve
os problemas de desempenho, mas alivia.

Para evitar que os blocos tenham que ser lidos sequencialmente, os criadores de
SGBDs tiveram que arranjar forma de adivinhar quais os blocos a que era necessário
aceder para que fosse possível fornecer uma resposta em tempo útil. Desta forma os
SGDBs passaram a possibilitar a criação de índices.
Como será simples de compreender o índice vai permitir que o acesso à informação
possa ser muito mais rápido.
Por exemplo, se quisermos procurar sobre um assunto qualquer num livro, o que
fazemos é procurar no índice pelo tema e de seguida localizar o assunto através do
número da página. Penso que não haverá ninguém que comece a ler um livro desde o
início até ao fim com o objectivo de procurar um certo assunto.

A estrutura de um índice numa base de dados é semelhante à estrutura de um índice

num livro. Por isso, para que seja possível criar um índice é necessário escolhermos

porque a implementação tem que correr em várias arquitecturas e plataformas e nem todos os sistemas
operativos disponibilizam as mesmas características a nível de manipulação avançada de ficheiros.
Actualmente o PostgreSQL usa a camada do sistema operativo.

Página 71
 Funcionalidades Avançadas - Capítulo 6

qual a informação a indexar. Esse informação (campo(s)) vai constituir o que se

chama de chave de pesquisa.

Se se quiser saber o nome de todos os clientes da zona Norte e que sabemos que esses
clientes estão todos no bloco 2 da figura abaixo, o único bloco que teria que ser lido e
processado seria esse mesmo. Através deste acesso directo ao bloco, evitou-se o
acesso e processamento dos restantes blocos (1,3,4), evitando assim I/O
desnecessário.
A forma de se aceder aos dados através de um índice é geralmente algo similar à
Figura 11 da página 72. A ideia subjacente à figura é que para cada entrada no índice,
existe um par [chave,bloco]. Cada bloco no par [chave,bloco] tem obrigatoriamente
correspondência com um bloco que tem que existir no pior dos no disco. A cada bloco
de dados estão associados vários registos (o dados). Como se pode observar há uma
dependência entre a entrada do índice e o bloco associado, o que quer dizer que
ambos têm que estar em síncronia. Há ainda uma outra dependência em relação ao
valor da chave no índice e o valor armazenado nos registos, ambos têm que existir
quer no índice quer no ficheiro de dados. Em relação aos dados que residem nos
ficheiros de dados estes não têm qualquer ordem2 associada.

Figura 11 – Camadas a percorrer para aceder aos dados através de um índice

2
Por razões de desempenho, as implementações colocam os registos novos e/ou alterados no fim do
ficheiro de dados. Esta característica não deve ser levada em conta na criação de queries. Sempre que
se queira um resultado ordenado, a intenção tem que aparecer explicitamente no query.

Página 72
 Funcionalidades Avançadas - Capítulo 6

6.1.2. Os Índices nos SGDBs

Como no caso de acesso a índices estamos perante uma pesquisa, logo a solução será
representar o índice em memória e sob a forma de árvore. Como se pode observar na
figura anterior, os índices ocupam em termos de memória e disco necessariamente
menos espaço que os dados.
Tal como acontece em variadíssimas situações que envolvem pesquisas, o método
preferencial consiste em representar o índice em memória sob a forma de árvore.

As formas mais usuais de representação de índices em memória são:

• Em forma de árvore ( árvore B, árvore B+, árvore R, etc.).
• Em forma de acesso directo (função de hash).

[Link]. Representação por Árvores B+

Uma árvore B+ é uma estrutura de dados em forma de árvore. Os nós internos (não
folha) servem para direccionar a pesquisa, uma vez que guardam a chave e
apontadores para os respectivos nós filho. Os nós folha contêm informação relativa à
localização no bloco de dados. Nestas implementações os nós folha estão geralmente
ligados através de uma lista duplamente ligada, possibilitando por isso pesquisas mais
eficientes.

Figura 12 – Exemplo de uma árvore B+ de ordem dois

Na Figura 12 encontra-se um exemplo de uma árvore B+, cuja chave de pesquisa são
números inteiros (presentes na raiz) e os dados do índice são representados por
inteiros seguidos de * (Por exemplo: 2*). A árvore é de ordem quatro, o que significa
que cada nodo pode ter no máximo quatro elementos. A ordem associada à árvore

Página 73
 Funcionalidades Avançadas - Capítulo 6

corresponde geralmente a número de blocos por sector do disco rígido. Esta

associação acontece, devido a factores relacionados com o desempenho. Embora as
árvores B+ aumentem a velocidade das pesquisas, a operação de inserção num nó
cheio, penaliza o desempenho da árvore, como se pode ver através da Figura 13. Isto
acontece porque o nó que atingiu o limite máximo, tem que ser dividido.

Para ilustrar as operações necessárias sobre a árvore, suponhamos que se queria

adicionar o elemento 8* à árvore da Figura 12. O nodo onde o elemento 8* será
colocado, corresponde ao ramo da esquerda porque que 8 é inferior a 13. Uma vez que
esse ramo está cheio, vai então ser necessário criar um novo nó folha e passar os
elementos 5*, 7* para o novo nodo. Esta última operação leva a que um nó interno (5)
seja criado. Esta situação é ilustrada pela Figura 13.

Figura 13 – 1º passo na inserção dum nó cheio

Uma vez que a inserção do nó 5, no nó pai (neste caso a raiz) vai levar a que este
fique completamente ocupado, é necessário dividir o mesmo. Uma vez que tal
acontece torna-se necessário dividir o respectivo nó. Após todas as operações
descritas anteriormente, a sub árvore esquerda resultante é a que esta presente na
Figura 14.

Raiz

Figura 14 – Divisão da raiz causada pelo novo nó

Página 74
 Funcionalidades Avançadas - Capítulo 6

Como durante o processo houveram divisões nos nós então a sub árvore que reflectir a
divisão executada, resultando assim na árvore presente na Figura 15.

Raiz

Figura 15 - Versão final da árvore após inserção do elemento 8*

Vantagens/Desvantagens

Permite pesquisas rápidas quer a chave seja simples ou composta.

Único método que permite pesquisas com gamas de valores. Por
exemplo: Idade >=18 E idade <=30.
- Inserção e remoção na árvore pode causar o balanceamento da árvore.

[Link]. Representação por Hashing

O uso de um índice sob a forma de uma tabela de hashing é uma outra maneira
eficiente de aceder aos dados. A ideia da tabela de hashing consiste em distribuir as
entradas do índice ao longo de vários grupos (conhecidos também por buckets, em
português significa balde), que são compostos por elementos semelhantes. A
semelhança entre os elementos é conseguida através de uma regra a que se chama
função de hash.
A ideia subjacente a esta função consiste em descobrir através de uma chave de
pesquisa qual o grupo correspondente. Depois de ser encontrado o grupo, a pesquisa
será depois restrita a esse mesmo grupo. Após a chave de pesquisa ter sido
encontrada, obtem-se a posição relativa (offset) da localização do bloco no disco. Para
se obter a posição exacta é necessário somar à posição encontrada a posição do
primeiro elemento da tabela de hashing.

Página 75
 Funcionalidades Avançadas - Capítulo 6

A Figura 16 mostra uma tabela de hash com capacidade para quatro buckets. A função
de hash a usar será: f(h) = Chave Pesquisa / 10. Desta forma a tabela de hash está
limitada a números entre 10 e 40.

Figura 16 – Exemplo de uma tabela de hash

Suponhamos que queríamos adicionar a chave 20. Através da função de hash,

obtemos uma posição de 2. Uma vez que o bucket da posição 2 está livre, logo o
elemento será inserido nessa mesma posição. O mesmo raciocínio seria aplicado para
a chave 40. A Figura 17 retrata esta situação.

Figura 17 – Função de hash, após a inserção das chaves 20 e 40

Suponhamos agora que queríamos adicionar a chave 34, a qual seria inserida na
posição 3. Tal como se pode observar na figura acima, não existe mais espaço livre na
posição 3. Esta situação leva à ocorrência de uma colisão. Este é um dos problemas
das funções de hash.

A forma que há para se resolver este problema resume-se a adicionar a chave que
entrou em conflito à lista ligada da entrada. Este tipo de situações leva a que as
funções de hash sejam usadas em situações muito especiais (ver secção seguinte). A
Figura 18 retrata uma situação em que a adição dos elementos 34 e 31 à tabela de
hash vão originar colisões com a chave da posição 3. Tal como representada na

Página 76
 Funcionalidades Avançadas - Capítulo 6

figura, esta situação é resolvida através da colocação das chaves 34 e 31 na lista

ligada do bucket 3.

Figura 18 – Tabela de hash com overflow no bucket 3

O método de hashing descrito até agora foi o Static Hashing com páginas de overflow.
Este método de hashing não é usado nos SGBDs porque é um método estático. Em
vez do método descrito acima, os SGBDs geralmente usam o método Extensible
Hashing o qual é dinâmico e por isso flexível. O último método é muito complexo e
por isso não recomendado para explicar o funcionamento das tabelas de hash. Devido
a essa razão, não foi o método seleccionado para a abordagem da indexação através de
tabelas de hash.

Para mais informação sobre índices baseados em hashing, consulte [31].

Vantagens/Desvantagens

Método muito rápido para comparações que usem o operador =. Geralmente é

usado em situações de joins que envolvam o método Index Nested Loop.
- Não permite pesquisas usando qualquer outro operador que não o =, nem
mesmo gamas de valores. Por exemplo: idade >=18 and idade <=30.
- Há medida que se vão gerado novas entradas que entram em conflito com
entradas já existentes, o desempenho do índice vai-se degradando
significativamente.

Como facilmente se depreenderá pela análise dos parágrafos precedentes, saber o que
indexar (os campos), como indexar (tipo de índice) e quando indexar é uma tarefa
importante para a extracção de informação em tempo útil.
Quando um query está a ser analisado pelo motor de planeamento (planner), um dos
factores que pode influenciar a decisão na escolha de um plano em relação a outro

Página 77
 Funcionalidades Avançadas - Capítulo 6

poderá ser a existência ou não de índices apropriados nos campos apropriados. Só

desta forma é que o planner poderá ser capaz de fazer a escolha acertada.

Nota: Os índices são a primeira ferramenta a que se deve recorrer para melhorar o
desempenho de um SGBD. São também contudo um recurso cujo uso tem que ser
pensado. Há pelo menos duas situações em que o planner não tira qualquer proveito
destes.
A primeira situação diz respeito às tabelas cujo número de registos seja reduzido.
Nestas condições um varrimento à tabela será certamente menos dispendioso do que o
uso de um índice. O descrito acontece se essa mesma tabela ocupar um ou dois blocos
de dados e se esses blocos se encontrarem na cache do SGDB. Relativamente à
segunda, no caso de tabelas onde são constantemente adicionados e/ou modificados
registos, o desempenho é obrigatoriamente prejudicado pela existência dos índices.
Esta quebra de desempenho ocorre porque há a necessidade de manter quer o
“ficheiro” de dados quer o do índice em perfeita síncronia para que o índice possa ser
útil.

6.1.3. Dicas para Uso de Índices

As melhores dicas a ter em conta na optimização do uso dos índices são sem dúvida
baseadas na experimentação. Tal acontece porque não há regras rígidas no que
respeita ao uso de índices, uma vez que o uso destes depende da estrutura dos dados e
dos dados em si.
No entanto há algumas dicas que podem ser tidas em contas que geralmente resultam
em melhor uso dos índices.

Na Tabela 9 encontram-se algumas dicas a ter em conta no uso de índices.

Página 78
 Funcionalidades Avançadas - Capítulo 6

Dica nº 1 Usar sempre o comando EXPLAIN e as estatísticas para avaliar o uso dos
índices. As estatísticas recolhidas são um dos factores que podem
melhorar ou piorar (se não reflectirem a verdade) a “qualidade” dos
planos de execução gerados pelo planner. Algumas das estatísticas que o
PostgreSQL recolhe:
• Total de inserts, updates, deletes por tabela
• Total de registos por tabela
• Total de bytes lidos e escritos
• Total de bytes que tiveram hit (não foi necessário ler directamente
a partir do disco porque estava na cache do SGBD)
• Distribuição dos valores ao longo da tabela
• Outros
Para mais informações sobre os dados recolhidas para as
estatísticas e mecanismos associados, consulte [17].

Dica nº 2 Usar índices de forma regrada. Numa base de dados o desempenho pode
ser mais penalizada pela ausência de índices do que pela presença destes.
Dependendo do tipo de acessos em causa à base de dados, geralmente é
melhor ter índices do que não ter. Esta dica parece uma contradição do
até agora foi dito, mas só alguém que conheça as tabelas e processos de
negócio é que pode decidir qual o tipo de acessos mais frequentes.
Dica nº 3 Criar o tipo de índice apropriado. Se na estrutura de uma tabela há um
campo que tem que ter valores obrigatoriamente únicos para além da
chave primária, então esse campo deve usar uma cláusula UNIQUE.
Se não se proceder desta forma, sempre que o campo seja usado numa
cláusula WHERE o SGBD vai continuar a procurar novas ocorrências, uma
vez que na definição da tabela não foi especificado que os valores desse
campo eram únicos.
Dica nº 4 Indexar os campos que se pensem que são usados frequentemente como
condições da cláusula WHERE.
Deve-se indexar os campos pelos quais se procura informação é não os
valores por que se procuram. Por exemplo: uma aplicação que permita
obter os contactos de clientes através de pesquisas por nome, cidade,
região deve indexar o nome, cidade e região e não os contactos em si.

Página 79
 Funcionalidades Avançadas - Capítulo 6

Dica nº 5 Os campos usados nas cláusulas ORDER BY, GROUP BY e HAVING são fortes
candidatos a serem indexados.
Dica nº 6 Indexar sempre todos os campos que sejam usados para fazer Joins, em
ambos os lados do Join.
Dica nº 7 Indexar sempre os campos que sejam usados como chave estrangeira.
Dica nº 8 Os índices têm um preço. Estes necessitam de usar memória e tempo de
processamento para serem mantidos e espaço em disco para serem
armazenados. Os índices que não sejam usados (podem ser detectados
através das estatísticas) podem ser removidos.
Dica nº 9 Não indexar grandes campos de texto. Se tivermos um campo de texto do
tipo VARCHAR(500) e se as pesquisas sobre esse campo não forem do tipo
“começa por X” e forem antes do tipo “contém X”, um índice nesta
situação é inútil. Os queries do tipo “começa por” são algo semelhante a
SELECT campo FROM tabela WHERE campo LIKE ‘valor%’; Por sua vez um
query do tipo o campo “contém X” é algo do género: SELECT campo FROM
tabela WHERE campo LIKE ‘%valor%’.
Dica nº10 Esta dica parece ridícula, mas há relatos relacionadas com esta. Não
indexar campos binários (BLOBs). No PostgreSQL estes campos são do
tipo bytea.
Dica nº11 Não indexar colunas com muitos valores mas de gamas reduzidas. Por
exemplo colunas do tipo BOOLEAN.
Dica nº12 Não indexar tabelas que são escritas muitas vezes e que muito raramente
são consultadas. Neste género de tabelas encontram-se as que geralmente
são usadas para registos de actividade (logs).
Dica nº13 Não indexar colunas cujos valores estejam constantemente a serem
alterados. Por exemplo: uma tabela de utilizadores que guarde um
timestamp do utilizador sempre que este visita uma página. Nesta
situação a única coluna que faz sentido indexar é a coluna que identifica o
utilizador. A coluna que armazena o timestamp não deve ser indexada.

Tabela 9 – Dicas para uso de índices

Página 80
 Funcionalidades Avançadas - Capítulo 6

6.1.4. Índices no PostgreSQL

Através da observação da Tabela 10, podem-se ver quais os tipos de índices e em que
situações e que estes devem ser usados. O planner do SGDB irá usar um tipo de
índice em vez de outro, dependendo do tipo de query e dos campos envolvidos. O uso
ou não dos índices, está directamente ligado com o género de algoritmo que usam
internamente.

Tipo de índice Usar c/ operadores Aplicação

Árvore B <, <=, =, >=, > Generalidade dos casos.
Árvore R <<, &<, &>, >>, @, Dados esparsos (ou seja: OLAP, cubos
~=, && OLAP, etc.).
Hash = Pesquisas que envolvam joins que usem o
método Index Nested Loop.
Gist - <, <=, =, >=, > Sistemas de Informação Geográficos.
Generalized
Search Trees

Tabela 10 – Relação entre o tipo de índice, operadores suportados e aplicação prática

Para mais informações sobre os índices Gist, consulte [5].

Na generalidade o PostgreSQL suporta os géneros de índices presentes na Tabela 11

que se encontra na página 81.

Género do Descrição
índice
Mono coluna São os índices mais usuais. Usam uma coluna.
Multi coluna Usa um conjunto fixo de colunas.
Únicos Usa uma ou várias colunas, mas o par dos valores nunca pode ser
repetido.
Funcionais São criados com base no resultado de uma função.
Parciais Criado com base numa parte dos dados.

Tabela 11 – Género de índices suportados

Página 81
 Funcionalidades Avançadas - Capítulo 6

[Link]. Índices Mono coluna

Este género de índice é suportado por todos os tipos de índices.

CREATE TABLE CLIENTES(

cliente_id SERIAL NOT NULL,
cliente_nome VARCHAR(80) NOT NULL,
cliente_morada VARCHAR(60) NOT NULL,
cliente_idade SMALLINT NOT NULL,
codigo_postal_id INTEGER NOT NULL,
cliente_sexo CHAR NOT NULL,
CONSTRAINT PK_CLIENTES PRIMARY KEY( cliente_id ),
CONSTRAINT FK_codigopostal FOREIGN KEY( codigo_postal_id )
REFERENCES CODIGO_POSTAL( codigopostal_id ),
CONSTRAINT CK_cliente_idade CHECK( cliente_idade >=2 ),
CONSTRAINT CK_cliente_sexo CHECK( cliente_sexo IN( 'M', 'F' ) )
);

Figura 19 – Estrutura da tabela clientes

Usando como referência a tabela clientes ilustrada na Figura 19, para se criar um
índice mono coluna o comando DDL a usar seria:

CREATE INDEX nome_indice ON clientes( cliente_nome );

O índice criado usaria uma árvore B como algoritmo de suporte e seria um índice que
possibilitava a existência de valores repetidos. Este género de índice pode também
não permitir valores repetidos, os índices únicos.

Para mais informações sobre índices únicos, consulte a secção [Link].

Se quiséssemos criar um índice nas condições anteriores, mas cujo algoritmo de

suporte fosse uma função de hash, o comando a usar seria:

CREATE INDEX nome_indice ON clientes USING HASH( cliente_nome );

[Link]. Índices Multi coluna

Até à data de elaboração deste documento o PostgreSQL só suportava índices multi

coluna em índices do tipo árvore B e GiSt.

O processo de criação de um índice multi coluna é praticamente igual aos métodos

usados anteriormente.

Página 82
 Funcionalidades Avançadas - Capítulo 6

Usando como referência a tabela clientes apresentada na Figura 19 acima, para se

criar um índice multi coluna nos campos cliente_nome, cliente_idade e
codigo_postal, o comando DDL a usar seria:

CREATE INDEX nome_indice ON clientes( cliente_nome, cliente_idade,

codigo_postal_id);

Embora o índice criado seja multi coluna o planner do SGDB só o pode usar se os
campos forem usados no query seguindo a mesma ordem que foi usada na criação.

Para o caso do índice anterior, este seria usado por exemplo no seguinte query:

SELECT cliente_nome
FROM clientes
WHERE cliente_nome = ‘JOAO ANTONIO’ AND
cliente_idade > 20 AND
codigo_postal_id = 1

Figura 20 – Exemplo de um resultado do planner a usar o índice ndx_cl_nome_idade_cp

Como se pode ver na Figura 20 o índice está a ser usado. O uso deste é confirmado
através do tipo de varrimento que está a ser usado, neste caso é Index Scan o que
revela o uso do índice.

Se o query fosse:

SELECT cliente_nome
FROM clientes
WHERE cliente_idade = 21 AND
codigo_postal_id = 1

Página 83
 Funcionalidades Avançadas - Capítulo 6

Figura 21 - Exemplo de um resultado do planner que não pode usar o índice ndx_cl_nome_idade_cp

Neste último exemplo o índice não poderia ser usado, uma vez que se avançou3 o
primeiro campo do índice. Perante este query o planner não pode usar o índice,
optando por isso por um varrimento à tabela, tal como se pode observar na Figura 21.
O não uso do índice é confirmado através do tipo de varrimento que está a ser usado,
neste caso é Seq Scan (Sequencial Scan) o que mostra que o índice não está a ser
usado.

Pode-se concluir o seguinte:

• Os campos indexados têm que ser usados consecutivamente.
• São usados em: joins de tabelas; ordenação de resultados (cláusula ORDER BY);

na presença das cláusulas GROUP BY e HAVING desde que respeitem o que é

referido no 1º ponto anterior.

[Link]. Índices Únicos

Este género de índice pode ser mono ou multi coluna.

Actualmente no PostgreSQL somente os índices árvore B podem ser únicos.

O PostgreSQL tal como acontece com outros SGBDs como é o caso do Oracle, cria
um índice único automaticamente sempre que encontra uma constraint UNIQUE ou uma
constraint PRIMARY KEY nas respectivas colunas.

3
Em termos do algoritmo de inserção na árvore, isto corresponde a que este deixe de conseguir saber
qual o ramo a seguir, dai que o índice não seja usado.

Página 84
 Funcionalidades Avançadas - Capítulo 6

Esta característica poderá ser vista como um detalhe de implementação, uma vez que
o standard SQL99 não faz qualquer referência a índices, nem como é que os SGBDs
devem implementar a unicidade de valores nas tabelas.
Este género de índice pode ser adicionado ou removido a qualquer momento.

[Link]. Índices Funcionais

Um índice diz-se funcional quando os valores a indexar correspondem ao

processamento dos campos a indexar por parte de uma função, quer seja uma função
do sistema, quer seja um stored procedure do utilizador.

Suponhamos que tínhamos uma aplicação que permitia fazer pesquisas de informação
dos clientes através do nome. Suponhamos ainda que os nomes são inseridos na tabela
quer com minúsculas quer com maiúsculas (Por exemplo: ‘João’).
Uma forma de tornar a pesquisa mais eficiente seria através da criação de um índice
funcional sobre o campo cliente_nome, mas cujo nome do cliente no índice fosse
armazenado em minúsculas.
Para se converter uma string para minúsculas usa-se a função lower.

O índice funcional a criar seria:

CREATE INDEX nome_index ON clientes( lower(cliente_nome) );

Desta forma queries do género:

SELECT * FROM clientes WHERE lower(cliente_nome) = ’joao’

seriam muito mais eficientes

[Link]. Índices Parciais

Um índice diz-se parcial quando os valores a indexar correspondem a parte dos dados.
Suponhamos que numa aplicação que permite a pesquisa de informação relativa a
filmes, o tipo de filme mais procurado eram os de acção.
Sendo este o tipo de filme mais procurado, seria bom termos um índice só para estes.

Página 85
 Funcionalidades Avançadas - Capítulo 6

Para se criar um índice parcial sobre o campo filme_tipo na tabela filmes, a instrução
a usar seria:

CREATE INDEX nome_indice ON Filmes( filme_tipo ) WHERE filme_tipo =’A’;

Embora na instrução acima a condição seja simples, a condição pode envolver queries
desde os mais simples até aos mais complexos, os quais geralmente usam joins, sub
queries, execução de stored procedures, etc.

6.1.5. Avaliar o Uso dos Índices

Embora os índices no PostgreSQL não necessitem de manutenção nem ajustes

(porque as implementações são todas dinâmicas), em ambientes de produção é
importante verificar quais os índices que estão a ser usados e quais que não estão.

A detecção de uso dos índices é feita com o comando EXPLAIN. Sendo o uso dos
índices condicionado por factores variadíssimos, é necessário ter em conta os pontos
que se seguem ao se avaliar o uso destes:

• Executar sempre o comando ANALYZE. Este comando vai recolher informação

estatística relativa à distribuição dos valores ao longo da tabela. Esta
informação é necessária porque é com base na mesma que o planner prevê o
número de registos a devolver. Caso essa informação não esteja disponível,
são usados valores por omissão. Ao se usarem valores por omissão, certamente
que os resultados obtidos não serão os melhores.
Examinar o uso de índices sem se ter executado o comando ANALYZE é perda de
tempo!
• Usar dados reais e não de teste. Entende-se neste contexto por dados de teste,
aqueles dados que são inventados com o propósito de criar registos e não
partes de dados reais. Ao se usarem dados de teste para maximizar o uso dos
índices, o resultado será pura e simplesmente o padrão de uso dos índices para
esses mesmos dados de teste e nada mais. Por isso a optimização do uso dos
índices deve ser feita em ambientes reais.

Página 86
 Funcionalidades Avançadas - Capítulo 6

É fatal usar conjuntos pequenos de dados para testes. Principalmente se o

número de registos for muito pequeno. Nestas situações o mais provável é que
os registos caibam num bloco de dados (data page). Nessa situações não há
qualquer mecanismo de índices que consiga ser mais eficiente do que ler o
bloco directamente do disco, havendo também a possibilidade de esse mesmo
bloco de dados encontrar-se disponível na cache do SGBD.
• Quando os índices não estiverem a ser utilizados, pode ser útil (para testar)
forçar o uso destes. Para tal há pelo menos dois parâmetros que podem ser
activados/desactivados. Os parâmetros são: enable_seqscan e
enable_nestloop, os quais tomam efeito somente sobre a sessão onde foram
executados. Ambos os parâmetros servem para configurar o comportamento
do planner.
O primeiro serve para que o varrimento sequencial das tabelas seja activado ou
não. O segundo serve para activar ou desactivar o uso de nested loops ao se
fazerem joins.
Para modificar estes parâmetros usa-se o comando:

SET nome_parâmetro = true;

ou
SET nome_parâmetro = false;

• Se ao se forçar o uso de algum dos parâmetros acima der melhor resultado, então
às duas situações que podem estar a ocorrer:
1) Os parâmetros por omissão para os custos ( CPU, I/O, locks, etc.) não
são apropriados.

Para mais informações sobre a configuração dos parâmetros de custos,

consulte [18].

2) A informação estatística disponível não é suficiente. Neste último caso

aconselha-se a aplicar os parâmetros de recolha de estatísticas.

Para mais informações sobre os parâmetros de recolha de estatísticas,

consulte [19]

Página 87
 Funcionalidades Avançadas - Capítulo 6

• Se após executar todas as dicas anteriores o PostgreSQL continuar a recusar o

uso dos índices, então o melhor é contactar a equipa de programadores do
PostgreSQL. Para obter assistência consulte [4].

6.2. Stored Procedures

Um stored procedure consiste num pedaço de código que fica armazenado dentro do
SGBD. Estes assemelham-se às funções e/ou procedimentos que existem na grande
maioria das linguagens. Não sendo regra, geralmente os stored procedures têm lógica
misturada com execução de comandos SQL. Um stored procedure pode ser executado
quando o nível aplicacional assim o decidir ou quando um trigger disparar.
Os stored procedure no PostgreSQL podem ser escritos em nas seguintes linguagens:
• C
• PL/pgSQL
• Tcl
• Perl
• Python (em fase alpha)
• Ruby
• Outras linguagens ( a criar pelo próprio utilizador )

Para que o PostgreSQL possa executar os stored procedure, este tem que conhecer a
linguagem em que o stored procedure foi escrito. Para isso o PostgreSQL possui o
que se chama uma language handler (suporte a linguagens) que tem como finalidade
permitir a execução de diferentes stored procedures nas linguagens mencionadas.
Os stored procedures podem ser escritos com o objectivo de serem usados como
código de triggers, como funções para a criação de índices funcionais e também como
funções/procedimentos.
É possível criar handlers para outras linguagens, desde que os respectivos language
handlers sejam escritos. Estes consistem em módulos escritos em C que são
automaticamente carregados pelo PostgreSQL quando são necessários.
Neste documento a única linguagem a usar será o PL/pgSQL uma vez que é uma
linguagem acessível e por isso de fácil compreensão.

Página 88
 Funcionalidades Avançadas - Capítulo 6

6.2.1. Vantagens/Desvantagens

Armazenados dentro do SGBD.

Maior desempenho, uma vez que guarda o plano de execução em formato
interno ao SGBD em vez de usar interpretação de instruções linha a linha.
Possibilidade de armazenar as regras de negócio directamente no SGBD.

6.2.2. A linguagem PL/pgSQL

O PL/pgSQL é uma das linguagens usadas no PostgreSQL.

No que respeita à sua estrutura e características é muito semelhante à linguagem
PL/SQL do Oracle, o que leva a que a migração de/para o Oracle seja fácil.

[Link]. A linguagem PL/pgSQL

Todo o stored procedure em PL/pgSQL tem obrigatoriamente a estrutura que se

segue.

CREATE FUNCTION nome_funcao(tipo_parâmetros) RETURNS tipo_retorno AS ‘

BEGIN
<Corpo da função>
END;
' LANGUAGE 'plpgsql';

Na primeira linha informa-se o SGBD que queremos criar uma função de nome
nome_funcao a qual tem como tipo de parâmetros (separados por vírgulas) os
parâmetros especificados por tipo_parâmetros e cujo tipo de retorno é tipo_retorno.
As palavras chave BEGIN e END indicam respectivamente que se vai dar inicio ou fim
ao corpo da função. A palavra chave LANGUAGE específica qual a linguagem em que o
stored procedure foi escrito. A linguagem fornecida em LANGUAGE será
posteriormente usada pelo SGBD quando este for para chamar a função.

Página 89
 Funcionalidades Avançadas - Capítulo 6

Exemplo:

CREATE FUNCTION soma_1_mais_1() RETURNS INTEGER AS ‘

BEGIN
-- Isto é um comentário
RETURN 1+1;
END;
' LANGUAGE 'plpgsql';

Como é sabido a potencialidade das linguagens de programação e também das

linguagens funcionais advém do facto que estas poderem fazer cálculos e outras
operações com valores não estáticos – as variáveis.

Para se declarar variáveis na PL/pgSQL é necessário faze-lo dentro de uma secção

especial de nome DECLARE.
Os tipos de variáveis suportadas vão desde aos tipos definidos no SQL, tipos criados
pelo próprio utilizador e os tipos %ROWTYPE, %TYPE, RECORD e outros.
O tipo %ROWTYPE permite criar uma variável cujos campos e respectivos tipos sejam os
associados a uma tabela.
O tipo %TYPE permite criar uma variável cujo tipo esta ligado à definição do campo na
tabela.
O tipo RECORD é semelhante ao tipo %ROWTYPE, exceptuando o facto do primeiro não
estar ligado à definição de registos de qualquer tabela.

Exemplo:

CREATE FUNCTION soma_1_mais_1() RETURNS INTEGER AS ‘

BEGIN
operando_a INTEGER;
operando_b INTEGER;

operando_a := 1;
operando_b := 1;

RETURN operando_a + operando_b;

END;
' LANGUAGE 'plpgsql';

Página 90
 Funcionalidades Avançadas - Capítulo 6

A função acima declara duas variáveis: operando_a, operando_b como sendo do tipo
INTEGER. De seguida atribui o valor 1 respectivamente a cada uma das variáveis,
retornando a soma das duas variáveis.

Uma das vantagens das funções consiste no facto de estas poderem receber
parâmetros.

O PostgreSQL em relação a outros SGDBs ainda só suporta parâmetros de entrada, ao

contrário de outros SGDBs com o caso do Oracle que suporta tanto parâmetros de
entra, entrada/saída e saída. No PostgreSQL para se definir uma função que tenha
parâmetros é necessário indicar quais os tipos de dados dos parâmetros em vez dos
nomes dos parâmetros, o que é mais usual nos SGBDs.

CREATE FUNCTION soma_n_mais_m( INTEGER, INTEGER) RETURNS INTEGER AS ‘

DECLARE
operando_a FOR ALIAS as $1; -- $1 é o primeiro parâmetro
operando_b FOR ALIAS as $2; -- $2 é o segundo parâmetro
BEGIN
RETURN operando_a + operando_b;
END;
' LANGUAGE 'plpgsql';

Uma das vantagens da linguagem PL/pgSQL consiste em estas poderem consultar e

manipular os dados da base de dados, com acontece com as instruções SQL.

Para demonstrar como é que se pode criar uma função que possa consultar os dados
de uma ou mais tabelas, é necessário primeiro introduzir mais alguns conceitos, em
concreto as variáveis do tipo: %ROWTYPE, %TYPE e os cursores.

O tipo %ROWTYPE permite ao utilizador criar uma variável cujo tipo seja do tipo registo
(record) de uma determinada tabela. Quer isto dizer que este tipo adapta-se à estrutura
da tabela. Se declararmos uma variável do tipo CLIENTES%ROWTYPE, estamos a
declarar uma variável, que vai ter os mesmos campos que os da tabela clientes e
cujos tipos de dados dos campos serão os que tiverem definidos até ao momento para
os campos da tabela.

Página 91
 Funcionalidades Avançadas - Capítulo 6

Para se entender melhor a potencialidade deste tipo de variáveis, nada melhor que um
exemplo. O exemplo abaixo vai retornar o nome de um certo cliente, o qual será
identificado através do parâmetro que é passado à função. Caso o cliente não exista,
será gerada uma excepção (erro).

CREATE FUNCTION ClienteNome( INTEGER) RETURNS TEXT AS ‘

DECLARE
p_clienteID FOR ALIAS as $1;
m_clienteREC CLIENTE%ROWTYPE;
BEGIN
SELECT * INTO m_clienteREC WHERE cliente_id = p_clienteID;
IF NOT FOUND
RAISE EXCEPTION ''O cliente cujo ID é % não existe '',
p_clienteID;
END IF;
RETURN m_clienteREC.cliente_nome;
END;
' LANGUAGE 'plpgsql';

A função acima, declara duas variáveis, a primeira de nome p_clienteID que vai ser
usada como chave na pesquisa do cliente e a segunda de nome m_clienteREC que vai
servir como local de armazenamento temporário para o nome do cliente. Repare-se
que no query SELECT anterior, está a ser usada uma nova cláusula, a INTO. No
contexto da função acima, a cláusula INTO serve para indicarmos ao SGBD que o
resultado do query será enviado para a variável m_clienteREC. Note-se que quando as
variáveis são do tipo %ROWTYPE, o query geralmente extrai todos os campos.
No caso de o query devolver algum registo, ou seja caso o cliente exista, a variável
FOUND do tipo BOOLEAN vai conter TRUE caso o cliente exista e FALSE caso não exista.
No caso do cliente não existir, será gerada uma excepção (erro) e a execução será
terminada. Note-se que o formato da mensagem apresenta alguma semelhança à
função printf da linguagem C, repare-se na presença do % no interior da frase.
Uma vez encontrado o cliente, resta-nos devolver o nome do mesmo. Para tal, acede-
se ao campo cliente_nome da variável m_clienteREC através do operador “.” e
devolve-se o valor que lá se encontrar.

O tipo %TYPE permite ao utilizador criar uma variável cujo tipo seja do tipo que estiver
associado ao campo da tabela. Se declararmos uma variável do tipo

Página 92
 Funcionalidades Avançadas - Capítulo 6

CLIENTES.cliente_nome%TYPE, estamos a declarar uma variável cujo tipo de dados é

igual ao tipo de dados do campo cliente_nome da tabela clientes.
Este tipo de variável tal como acontece com o tipo %ROWTYPE adapta-se a estrutura da
tabela, ou seja, se por algum motivo alguém decidir alterar o campo cliente_nome de
VARCHAR(80) para VARCHAR(100) ou até mesmo para CHAR(100), as funções que usem o
tipo de variável %TYPE, não terão que ser alteradas.
De seguida apresenta-se uma função de nome ClienteNome que faz o mesmo que a
anterior, mas em vez de usar uma variável %ROWTYPE usa antes uma do tipo %TYPE.

CREATE FUNCTION ClienteNome( INTEGER) RETURNS TEXT AS ‘

DECLARE
p_clienteID FOR ALIAS as $1;
m_nome CLIENTE.cliente_nome%TYPE;
BEGIN
SELECT cliente_nome INTO m_nome WHERE cliente_id = p_clienteID;
IF NOT FOUND
RAISE EXCEPTION ''O cliente cujo ID é % não existe'',
p_clienteID;
END IF;
RETURN m_nome;
END;
' LANGUAGE 'plpgsql';

Repare-se que nesta função, o número de campos a ler do SELECT é igual ao número
de variáveis, o que não acontecia com a anterior versão da função.

Para se introduzir o conceito e os mecanismos associados aos cursores, nada melhor

do que um exemplo prático. Tal como acontece com o cursor do rato, as variáveis do
tipo cursor, tem a mesma finalidade – apontar. No caso das variáveis do tipo cursor
estas apontam a posição do registo actual. Pode-se então concluir que os cursores
servem então para se percorrer de forma sequencial um conjunto de registos.
Supondo que se quer obter o valor total das vendas efectuadas para um certo cliente.
Suponhamos também que não é possível através de um query SELECT obter o mesmo
resultado (o que não é verdade). Para se obter o resultado pretendido, será necessário
consultar duas tabelas, a tabela clientes e a tabela vendas.

Página 93
 Funcionalidades Avançadas - Capítulo 6

CREATE FUNCTION ClienteTotalVendas( INTEGER) RETURNS INTEGER AS ‘

DECLARE
p_clienteID FOR ALIAS as $1;
m_id CLIENTE.cliente_id%TYPE;
m_vendREC VENDAS%ROWTYPE; -- variável destino a usar no FETCH
m_acomulador INTEGER; -- armazena o acumulado das vendas
m_cursor refcursor; -- variável cursor para iterar sobre as vendas
BEGIN
m_acomulador:=0;

SELECT cliente_id INTO m_id WHERE cliente_id = p_clienteID;

IF NOT FOUND
RAISE EXCEPTION ''O cliente cujo ID é % não existe'',p_clienteID;
END IF;

OPEN m_cursor FOR SELECT * FROM VENDAS WHERE cliente_id =

p_clienteID;

LOOP
FETCH m_cursor INTO m_vendREC;
IF FOUND THEN
m_acomulador:=m_acomulador + m_vendREC.venda_valor;
ELSE
-- fim do cursor ou cursor vazio
EXIT; -- abandona o loop
END IF;
END LOOP;

CLOSE m_cursor;
RETURN m_acomulador;
END;

Na secção DECLARE existe um novo tipo de variável – o refcursor. É através deste tipo
que se declaram os cursores no PostgreSQL. A função acima está dividida em quatro
partes. Na primeira verifica-se a existência do cliente. Na segunda efectua-se a
inicialização do cursor. Na terceira calcula-se o valor acumulado das vendas. E por
fim na quarta, processa-se o fecho do cursor e o retorno.

Relativamente à primeira não há nada de novo a adicionar, uma vez que esta parte é
igual à das funções anteriores.
No que respeita à segunda parte, processa-se a abertura do cursor utilizando para o
efeito um query SELECT que tem o resultado filtrado através do cliente. Note-se que

Página 94
 Funcionalidades Avançadas - Capítulo 6

antes de se processar a abertura do cursor, o cursor está desligado (unbound). Isto

acontece, porque na declaração do cursor não foi fornecido qualquer query. Uma vez
aberto o cursor será então possível extrair informação a partir deste. Caso contrário o
PostgreSQL geraria uma excepção.
Em relação à terceira parte, usa-se um ciclo infinito para se extrair a informação de
cada registo de vendas. A cada iteração do ciclo, a instrução FETCH (buscar), vai
colocar o registo actual do cursor m_cursor na variável m_vendREC. A instrução FETCH é
semelhante à instrução SELECT ... INTO abordada anteriormente, daí que a variável
FOUND seja alterada consoante o FECTH retornou registos ou não (no fim do cursor ou
caso o cursor esteja vazio).
Na quarta parte é encerrado o cursor e retornado o valor. Uma vez fechado o cursor,
este passa a estar desligado (unbound), por isso qualquer operação sobre o cursor que
não a abertura irá gerar uma excepção.

6.3. Triggers

Um trigger corresponde a uma função que é chamada pelo SGBD quando um certo
evento acontece. Os eventos estão directamente relacionados com os tipos de
operações DML suportadas, ou seja os tipos de eventos podem ser: ON INSERT, ON

UPDATE e ON DELETE. Deve-se ainda indicar ao SGBD quando é que este deve disparar
o trigger, mais concretamente, depois de ocorrido o evento (AFTER) ou antes de o
evento ter ocorrido (BEFORE). Pode parecer confuso disparar um trigger após o evento
ser sido executado correctamente, mas há situações em que tal é necessário.
Suponhamos que tínhamos um trigger que tinha que validar uma regra de negócio e
que para essa regra de negócio pode ser executada correctamente este tinha que
conhecer os valores correctos e não nulos.
Quando um trigger é executado depois do evento, temos a certeza que a base de
dados, através do mecanismo de restrições de integridade já validou esses mesmos
valores. Caso contrário esses valores teriam que ser verificados pelo próprio trigger o
que não é a forma mais correcta e eficiente de o fazer. O mais correcto seria usar os
mecanismos internos de integridade da própria base de dados, os quais são muito
mais rápidos que o possível código do trigger. Uma outra razão deve-se a que a
restrição de integridade fica escondida. Por escondida entenda-se que a restrição não
apareceria quando se pedisse uma informação sobre a estrutura da tabela.

Página 95
 Funcionalidades Avançadas - Capítulo 6

Quando um trigger é executado antes do evento, os valores presentes nos campos

ainda não foram verificados pelas restrições de integridade. Uma das situações em que
se recorre ao uso de triggers BEFORE está relacionada com situações em se deseja
alterar os valores efectivamente registados. Por exemplo: usando como referência o
exemplo habitual do cinema, suponhamos que sempre que fosse registada uma venda
cuja quantidade de bilhetes fosse superior a 5 unidades, então ao valor da venda será
feito um desconto de 10% sobre o valor total da venda.
Para implementar um trigger deste género em que é necessário alterar o valor presente
num campo antes de este ser submetido para a base de dados, só através de um trigger
BEFORE é que tal é possível.

De um ponto de vista prático um trigger corresponde à execução de um stored

procedure. Os stored procedures que sejam usados por triggers têm algumas
particularidades, entre as quais se destacam:
• Tipo de retorno - o tipo de valor a retornar tem que ser TRIGGER para
versões superiores ao PostgreSQL 7.3 e para as anteriores o tipo de retorno
tem que ser OPAQUE.
• Variáveis especiais – As variáveis NEW e OLD são do tipo RECORD e são
definidas automaticamente sempre que o código do trigger é executado.
Estas tem a estrutura da tabela onde o trigger foi aplicado, ou seja os
campos apresentam os mesmos nomes, tipos e restrições que os da tabela
onde o trigger foi aplicado.
A variável NEW é usada nas operações de INSERT, UPDATE e DELETE para se
aceder aos valores “actuais”.
As variáveis OLD e NEW são usadas nas operações de UPDATE. A variável
OLD contem os valores já existentes antes de se dar a alteração e a variável
NEW contém os valores a usar, os quais serão sobrepostos aos anteriores.

Presumindo que o stored procedure do trigger está criado, é necessário indicarmos ao

SGBD como é que queremos que o trigger seja executado, ou seja BEFORE, AFTER e em
que situações é que o deve fazer, ou seja para cada registo (EACH ROW) ou como um
todo (EACH STATEMENT).

Página 96
 Funcionalidades Avançadas - Capítulo 6

Para tal é necessário executar o seguinte comando:

CREATE TRIGGER nome_trigger { BEFORE | AFTER } { evento [OR ...] }

ON nome_tabela FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE nome_do_stored procedure( argumentos_opcionais )

Suponhamos que se queria aplicar um trigger de nome trg_aplica_desconto_venda

sobre a tabela vendas e que esse trigger seria disparado depois do evento INSERT e
para cada registo executa-se o stored procedure de nome trg_proc_desconto_venda. O
comando a usar seria:

CREATE TRIGGER trg_aplica_desconto_venda AFTER INSERT

ON vendas FOR EACH ROW

EXECUTE PROCEDURE trg_proc_desconto_venda();

É possível aplicar vários triggers numa mesma tabela. Dizem as regras de bom senso
que não se deve usar a sequência de disparo na lógica de criação dos triggers. No
entanto só para referência, o PostgreSQL usa a ordenação do nome do trigger como
forma de obter a ordem de disparo.
Embora não tenha sido dado nenhum exemplo de um trigger ao longo do documento,
optei por não o fazer, umas vez que a base para a criação destes é a mesma que a dos
stored procedures, tirando algumas variáveis que só existem nos triggers, como é o
caso da NEW,OLD e outras.

Para informações sobre triggers, cursores, retorno de cursores, estruturas de

controlo, migração de stored procedures e outro assuntos relacionados com a
linguagem PL/pgSQL, consulte [20].

Página 97
 Funcionalidades Avançadas - Capítulo 6

6.3.1. Triggers no PostgreSQL

Até ao momento o PostgreSQL não suporta as seguintes funcionalidades presentes na

normal SQL99:
• Disparo de um trigger quando forem actualizados valores de certas
colunas de uma tabela. (ou seja: AFTER UPDATE OF col1, col2).
• Criar alias para as variáveis “old” e “new”. Uma vez que o PostgreSQL
permite criar triggers em várias linguagens, o acesso aos dados é tratado
de forma específica consoante a linguagem.
• Suporta somente triggers do tipo ROW, não suportando triggers do tipo
STATEMENT.

• O SQL99 define que múltiplos triggers devem ser disparados consoante a

data/hora de criação dos mesmos. O PostgreSQL usa os nomes dos
triggers em vez da hora de criação.

6.4. Controlo da Concorrência

Antes de mais convém dizer o que se entende por concorrência no contexto dos
SGDBs. Diz-se que um query está a executar de forma concorrente com outro(s)
queries quando esses mesmos queries operam sobre os mesmos dados.
Por exemplo: Na situação que se segue, ignore-se o facto de as contas bancárias
possuírem os dois tipos de saldos: o corrente e o contabilístico. Num balcão de um
banco a esposa do Sr. A faz um depósito em numerário sobre a conta XPTO.
Enquanto o depósito está a ser feito (presuma-se que demora algum tempo) o Sr. A
(também titular da conta XPTO) vai ao Multibanco e levanta dinheiro.
Qual o saldo que é mostrado ao Sr. A após este acabar o levantamento?
Para se responder a esta questão é necessário conhecer os mecanismos de controlo de
concorrência usados pelo SGBD.

O Controlo da Concorrência é um dos aspectos mais importantes a ter em conta em

SGBDs multi-utilizador.

Página 98
 Funcionalidades Avançadas - Capítulo 6

Os métodos de controlo da concorrência que geralmente são suportados pelos SGBDs

em geral, são:
• Bloqueio de tabelas.
• Bloqueio de páginas de dados (data pages).
• Bloqueio de registos.

Se no exemplo anterior o SGBD em causa usasse o mecanismo de bloqueio de

tabelas, o Sr. A poderia no melhor dos casos efectuar o levantamento do dinheiro após
aguardar pelo fim do depósito ou a operação de levantamento iria falhar, uma vez que
a tabela já estava bloqueada por outra operação.
Embora está técnica seja a mais simples de implementar por parte dos SGBDs, esta
técnica é a que pior se adapta a nível de acessos concorrentes, porque só um query
(quer seja de leitura ou escrita) pode operar sobre a tabela bloqueada. Uma vez que só
um query pode operar sobre a tabela, o que iria acontecer às restantes tentativas de
movimento efectuados pelos restantes clientes do banco? A resposta a esta última
pergunta é simples: tinham que esperar até que a tabela(s) não estivesse bloqueada.
Este mecanismo é sem dúvida o que pior se adapta a acessos concorrentes.

Um outro mecanismo de bloqueio que também existe é o bloqueio de páginas de

dados (data pages).
Utilizando este mecanismo de bloqueio em vez do anterior, só algumas contas, as que
residissem (incluindo a conta do Sr. A) no mesmo bloco de dados que a conta do Sr.
A é que seriam bloqueadas.
Com este mecanismo de bloqueio o SGBD poderia estar a processar outros
movimentos de conta enquanto tratava do movimento da conta do Sr. A.
Usando este mecanismo iria trazer vantagens do ponto de vista do desempenho do
SGBD uma vez que este poderia executar outros queries enquanto processava o query
para o depósito ou levantamento da conta do Sr. A. De forma geral este método de
controlo da concorrência é um método seguro e eficiente.
Caso algum cliente das contas que residissem na mesma página de dados que a conta
do Sr. A quisessem movimentar ou consultar a conta, só o poderiam fazer assim que a
operação do Sr. A tivesse desbloqueado a página de dados respectiva.

Página 99
 Funcionalidades Avançadas - Capítulo 6

Para o caso de bases de dados com muitos acessos concorrentes, como a base de
dados de um banco, este mecanismo não é muito aceitável, mas muito melhor que o
anterior.

O método de controlo da concorrência baseado no bloqueio de registos é sem dúvida

o melhor método dos que até agora foram enunciados. Este método em vez de
bloquear a página de dados (que geralmente contém vários registos) vai bloquear
somente os registos necessários. Desta forma e usando como analogia o exemplo do
banco, o acesso à conta do Sr. A iria sofrer um acesso concorrente e o acesso às
restantes contas de outros clientes poderiam ser permitidos em simultâneo com as
operações sobre a conta do Sr. A.

No caso do exemplo acima, se alguém (um empregado do banco) quisesse obter o

alguma informação sobre a conta do Sr. A não o poderia fazer, porque a conta estava
a ser actualizada por força quer do movimento de depósito ou do movimento de
levantamento.

Para resolver esta situação e outras, os SGBDs como é o caso do PostgreSQL

implementa um mecanismo mais lógico é mais eficiente para controlo da concorrência
– o MVCC — MultiVersion Concurrency Control.

Para mais informações sobre o MVCC, consulte a secção 6.4.

6.4.1. Transacções

As transacções são dos mecanismos mais importantes do ponto de vista da integridade

das bases de dados. Para se entender o que é uma transacção e qual a sua utilidade,
vamos recorrer uma vez mais ao exemplo anterior do banco.
Supondo que a esposa do Sr. A desta vez queria transferir dinheiro da conta do Sr.A
para a conta particular da Sra. A. Como será fácil de se perceber serão necessários
dois movimentos, um levantamento da conta do Sr. A e um depósito na conta
particular da Sra. A. É fácil de nos apercebermos que o estado de integridade da base
de dados, por momentos vai estar inconsistente. Supondo que durante a transferência
ocorria uma folha de hardware no SGDB, qual é que seria o saldo de cada uma das

Página 100
 Funcionalidades Avançadas - Capítulo 6

contas? É difícil conseguir saber. Mas há uma certeza, se a movimentação das contas
estivesse a ser feita sobre uma transacção, o saldo das contas ficavam com os saldos
que tinham (no caso de uma falha qualquer) ou uma conta era debitada e a outra
creditada devidamente, mas nunca uma situação intermédia (uma conta debitada e
uma conta não creditada e vice versa).
Uma transacção consiste num conjunto de operações que são executadas como se
fosse uma única operação. Mais concretamente ou a transacção executa até ao fim
com sucesso ou tudo o que foi feito é ignorado. Esta é a única forma que há para se
manter a integridade da base de dados.
As transacções têm quatro propriedades muito importantes que se devem conhecer:
• Atomicidade – O conjunto de operações que constituem uma transacção
formam um grupo indivisível (atómico), no sentido em que todas as
operações são executadas com sucesso ou nenhuma é executada. Por
outras palavras, uma transacção ou termina com sucesso (faz o commit) ou
então todas as operações realizadas sobre a base de dados são desfeitas
(faz o rollback), dando assim desta forma a ilusão que as operações nunca
foram realizadas.
• Integridade – Uma transacção se envolver actualizações de dados, deve
transportar a base de dados de um estado de integridade para outro também
de integridade. Durante a execução da transacção, a integridade da base de
dados pode ser momentaneamente violada, contudo quando a transacção
terminar a integridade deve ser assegurada.
• Isolamento – Embora as transacções executem concorrentemente, o
sistema deve dar a cada transacção a ideia de que é a única transacção que
está a executar no SGBD ou seja, dar a ideia que a transacção executa
isoladamente das restantes. Numa abordagem mais prática, no caso de
haverem várias transacções concorrentes a acederem aos mesmos dados o
sistema deve evitar que estas interfiram entre si, garantindo que o resultado
seria o mesmo se as transacções fossem executadas em série.

Página 101
 Funcionalidades Avançadas - Capítulo 6

• Persistência – O SGBD deve assegurar que todos os efeitos causados por

uma transacção bem sucedida se tornem persistentes (definitivos) na base
de dados e visíveis para as outras transacções. De um ponto de vista mais
prático, após uma transacção terminar com sucesso (ter feito commit), as
suas actualizações sobre a base de dados passam a ser efectivas,
sobrevivendo a todo o tipo de falhas que eventualmente possam ocorrer.
Assim sendo os efeitos sobre a base de dados não poderão ser desfeitos, a
não ser por outras transacções posteriores.

As quatro características anteriores são conhecidas na área das bases de dados como o
princípio ACID ( Atomicity, Consistency, Isolation and Durability).

Há uma questão que é necessário responder. Como é que o SGBD mesmo em caso de
falhas (qualquer que seja) consegue desfazer o que tinha feito?
Para se responder a esta questão aconselha-se a leitura da secção que se segue.

[Link]. Transacções no PostgreSQL

Actualmente o PostgreSQL (v.7.3.3) ainda só suporta transacções simples, não

suportando nested transactions nem save points.
Para implementar as transacções o PostgreSQL bem como outros SGBDs como é o
caso do Oracle tira partido de uma técnica conhecida na área das bases de dados por
WAL – Write Ahead Logging.
A ideia prática subjacente ao WAL consiste em registar num suporte seguro4 todas as
alterações a serem feitas na base de dados antes de essa mesmas alterações serem
reflectidas sobre os dados reais. Só através de um mecanismo deste género é que será
possível desfazer as alterações feitas. Na realidade as alterações a fazer nunca são
feitas sobre os dados reais, mas sim sobre uma cópia dos dados a alterar. Quando for
dada a ordem de finalização da transacção (o commit), a transacção executa cada uma
das operações que estavam pendentes sobre os dados reais (as tabelas). O registo da

4
Preferencialmente aconselha-se que o disco onde os ficheiros do WAL serão armazenados, seja um
disco independente daquele onde residem os ficheiros de dados. Convém que o disco em questão
consiga assegurar ao sistema operativo que após uma operação de flush sobre este, o disco não se limite
a sinalizar o sistema operativo que os dados foram efectivamente escritos e que os tenha somente
armazenado na cache do próprio disco.

Página 102
 Funcionalidades Avançadas - Capítulo 6

ocorrência no WAL só é removido (deixa de estar pendente) assim que o SGBD tiver
a certeza que todas as operações foram executadas com sucesso5. Usando este
pressuposto os SGBDs conseguem um melhor desempenho, uma vez que não
necessitam de fazer flush das páginas de dados sempre que há alterações.
Usando como referência a figura Figura 10 da página 70, pode-se ver as camadas que
cada operação na base de dados pode originar. Na presença de uma mecanismo do
género do WAL, o SGBD só tem que se assegurar que as operações presentes no
WAL foram efectivamente registadas no disco assim que receber um commit vindo de
uma transacção. Se assim não fosse este não conseguiria assegurar a atomicidade do
princípio ACID. Enquanto uma transacção estiver activa (ainda não fez o commit mas
já fez o begin) as operações vão sendo registadas no WAL sem que seja necessário ter
a certeza que estas estão a ser fisicamente armazenadas no disco. Desta forma quer o
sistema operativo quer o hardware envolvido podem fazer caching dos dados, o que
leva a muito menos pedidos de I/O, resultando assim num desempenho muito superior
a nível do SGBD. Se um SGBD não suportar transacções, então este a cada operação
realizada sobre os dados, tem6 que se assegurar que os dados da operação ficam
fisicamente armazenados no disco antes de podes processar uma nova operação, o que
leva este tipo de SGBD tenham grandes perdas de desempenho. É devido a tudo o que
é mencionado anteriormente que as operações de carregamento das bases de dados,
também conhecidas por operações de bulk-loading, devem ser realizadas sob
transacções. Só a título de curiosidade, um ficheiro de backup com cerca de 1.400.000
registos sob a forma de instruções INSERT ao ser executado sob uma transacção
demora aproximadamente 4 minutos a ser processado. A mesma operação realizada
sem estar sob uma transacção pode demorar entre 25-40 minutos.
Os custos inerentes a uma operação de flush7 (assegurar que os dados estão
fisicamente registados no disco) de uma ou mais data pages são muito elevados, uma

5
Mesmo após a transacção ter feito commit não é garantido que os dados estejam fisicamente
armazenados nos ficheiros de dados e índices. Há uma certeza, é que mesmo que esses dados não
estejam definitivamente armazenados nos ficheiros de dados, os dados que serão vistos serão a última
versão dos dados, que o mais certo é que estejam na pool de memória partilhada à espera de uma “boa”
oportunidade para serem escritos definitivamente nos ficheiros de dados. Após o SGBD ter a certeza
que os dados foram devidamente registados nos “ficheiros” de dados, este remove o registo da
transacção do WAL.
6
O MySQL por exemplo e ignorando o suporte para as tabelas InnoDB, não se assegura que as
operações estão escritas efectivamente no disco, uma vez que este por omissão funciona com o
parâmetro que controla a system call fsync ou equivalente desligado, segundo [37]. O PostgreSQL
por omissão tem o parâmetro de configuração fsync activo.
7
Estas operações são geralmente asseguradas em sistema Unix pela system call fsync ou fdatasync.

Página 103
 Funcionalidades Avançadas - Capítulo 6

vez que serão necessárias várias operações que vão desde posicionamento no ficheiro;
síncronia de acessos a vários acessos ao disco e escrita dos dados no disco. No caso de
ocorrer um crash, o SGBD tem sempre acesso às operações pendentes que não
chegaram a finalizar através do WAL.

Uma outra técnica que é usada pelo PostgreSQL e por outros SGBDs como é o caso
do Oracle no que respeita à manutenção da consistência é o MVCC — MultiVersion
Concurrency Control. Na prática o MVCC permite que cada transacção veja um
snapshot (versão) da base de dados tal como a base de dados estava até a transacção
ter iniciado, independentemente do estado dos dados nos ficheiros de dados e das
alterações feitas por outras transacções. Desta forma consegue-se que as transacções
não vejam dados inconsistentes causados por outras transacções a executar
concorrentemente, garantindo assim o isolamento das transacções.
A grande diferença que existe entre este mecanismo e outros é que através do uso do
MVCC os queries que leiam dados não entram em conflito com os queries que
escrevem dados e por isso as leituras nunca bloqueiam as escritas nem vice-versa.
Desta forma os níveis de concorrência serão muito mais elevados, levando a que o
desempenho do SGBD seja muito melhor.
Embora o MVCC seja o método preferível a nível de controlo da concorrência o
PostgreSQL também disponibiliza os métodos tradicionais de bloqueio (tabelas,
páginas de dados e registos) para que estes possam ser usados pelas aplicações que
não se adaptem facilmente ao MVCC.
Antes do PostgreSQL suportar MVCC os backups tinham que ser feitos em horas cuja
base de dados não tivesse actividade porque eram utilizados bloqueios de tabelas em
modo exclusivo, para se ter a certeza que o backup continha dados consistentes.
Com a adopção do MVCC, os backups passaram a poder ser feitos qualquer altura. É
necessário referir que os dados das transacções que não tenham terminado não serão
incluídos no backup.
Com o uso desta técnica, o PostgreSQL pode começar a ser usado (na realidade é
usado) em sistemas que necessitam de uma disponibilidade de 24 horas por dia 365
dias por ano.

Página 104
 Funcionalidades Avançadas - Capítulo 6

[Link]. Níveis de Isolamento

O nível de isolamento de uma transacção define como é que a própria transacção vai-
se comportar quando esta estiver a executar juntamente com outras, ou seja vai definir
quais as regras que estas devem usar para que as transacções entre si não sintam a
presença das restantes.
O standard SQL define quatro níveis de isolamento para três fenómenos que podem
ocorrer em cada um dos níveis. Esses fenómenos são os seguintes:
• Dirty read – Uma transacção lê dados que foram escritos por uma
transacção não terminada (ainda não fez commit).
• Nonrepeatable read— Uma transacção relê dados que tinha previamente
lido e descobre que esses dados foram modificados por uma outra
transacção que terminou depois da primeira leitura dos dados.
• Phantom read — Uma transacção re-executa um query que devolve um
conjunto de registos que estão de acordo com a condição de pesquisa e
descobre que o que o conjunto de registos retornados da segunda vez é
diferente, porque uma transacção terminou muito recentemente.

Na tabela abaixo encontram-se as relações existentes entre os níveis de isolamento

definidos pelo standard SQL e os fenómenos descritos anteriormente.
Nível de isolamento Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possível Possível Possível
Read committed Impossível Possível Possível
Repeatable read Impossível Impossível Possível
Serializable Impossível Impossível Impossível

Tabela 12 – Relação entre o nível de isolamento e os três fenómenos ligados às transacções

Em destaque na tabela anterior encontram-se os níveis de isolamento suportados pelo

PostgreSQL.

Para mais informações sobre os níveis de isolamento suportados pelo

PostgreSQL e sobre afinação da configuração do WAL, consulte
respectivamente [21] e [22].

Página 105
 07
7.1. BLOBs no PostgreSQL 107
 Campos Binários - Capítulo 7

7. Campos Binários (BLOBs)

Os campos binários mais conhecidos na área das base de dados como BLOBs- Binary
Large Objects, são um tipo de campos especiais que permitem que sejam
armazenados quaisquer tipos de dados. Geralmente neste tipo de campos armazenam-
se ficheiros de imagens (.gif, .jpg, etc) ou qualquer outro tipo de informação. Estes
campos podem ser vistos como simples contentores de informação, porque não tem
limite nem quaisquer restrições a nível de conteúdo.
Do ponto de vista do SQL a usar este tipo de campos não tem qualquer tratamento
especial uma vez que são tratados como outros tipos de campos quaisquer.

7.1. BLOB’s no PostgreSQL

No PostgreSQL há duas formas de se aceder aos BLOBs:

• Forma habitual de acesso.
• Através de uma interface “especial” do PostgreSQL.

No que respeita à primeira há algumas considerações a ter em conta.

Em relação à inserção as considerações a ter estão relacionadas com a forma como o
PostgreSQL interpreta certos bytes. Ou seja, antes que se possa inserir um registo que
contenha um BLOB, é necessário fazer umas pequenas conversões nos dados de
entrada para certos bytes que ocorram. Os bytes que são afectados na inserção são os
que se encontram na Tabela 13.

Valor Descrição Conversão Exemplo Resultado à

Decimal saída
0 Zero '\\000' SELECT \000
'\\000'::bytea;
39 Plica '\'' ou '\\047' SELECT '
'\''::bytea;
92 Backslash '\\\\' ou '\\134' SELECT \\
'\\\\'::bytea;
Tabela 13 – Bytes que necessitam de tratmento especial na inserção

O tipo de dados a usar no PostgreSQL para os BLOB’s é o tipo bytea.

Página 107
 Campos Binários - Capítulo 7

Os caracteres presentes na tabela são geralmente interpretados pelos SGBDs como

caracteres “especiais”, porque estes indicam ao SGBD que o tratamento tem que ser
diferente dos restantes caracteres.
O carácter zero (\0) tem o significado de fim de string na linguagem C, a qual é usada
na criação deste tipo de software. Por isso este é interpretado pelo PostgreSQL como
sendo um carácter especial.
O carácter plica (‘) nos SGBDs é interpretado como início/fim de string.
O carácter backslash (\) funciona como um carácter que tira o significado especial de
qualquer um dos restantes caracteres presentes na tabela. Se este carácter não existisse
não era possível armazenar por exemplo nomes de locais que tivessem uma plica no
próprio nome. Por exemplo a string ‘Arca D’ Água’, teria que ser usada sob a forma
de ‘Arca D\’ Água’ em vez da forma anterior.

Para a leitura de conteúdos dos campos BLOB é necessário também fazer algumas
conversões, uma vez que os bytes do campo são armazenados sob a forma de bytes em
formato octal.

Para assegurar estas conversões quer sejam as de inserção de dados quer as de leitura,
o PostgreSQL fornece funções na API libpq para esse efeito.
As funções a usar para o efeito são as seguintes:

Função Descrição
PQescapeBytea Converte os dados à entrada (i.e INSERT)
PQunescapeBytea Converte os dados à saída (i.e SELECT)

Tabela 14 – Funções para tratamento anterior e posterior de BLOBs

As funções enumeradas na Tabela 14 estão definidas no header file libpg-fe-h.

Para mais informações sobre BLOBs no PostgreSQL, consulte [23].

Para melhor se entender o funcionamento da criação/acesso a BLOBs aconselha-se a

leitura da secção 8.2.

Página 108
 Campos Binários - Capítulo 7

Uma outra forma que há para aceder/criar BLOBs é através de uma interface
“proprietária” do PostgreSQL a qual permite que os BLOBs sejam acedidos como se
tratassem de ficheiros. Esta interface conhecida por lo – Large Object é muito mais
eficiente do que a anterior uma vez não é necessário fazer qualquer tipo de conversão
dos dados. Esta interface apresenta ainda uma outra vantagem, em relação ao acesso
aos dados esta permite que se aceda directamente a parte dos dados, um mecanismo
do género da função lseek (permite que se posicione um ficheiro num certo offset em
relação a uma posição conhecida), o que não acontece com a interface libpq em que é
necessário obter primeiro todos os bytes e só depois escolher qual a gama de bytes a
usar.

Para mais informação sobre a interface lo – Large Object, consulte [24].

Página 109
 08
8. 8Arquitectura Tecnológica 111

[Link] a Campos Binários em PHP 115

 Usar PostgreSQL com PHP na Web - Capítulo 8

8. Usar o PostgreSQL com o PHP na Web

O uso do PostgreSQL na Web é generalizado e em particular a sua utilização conjunta

com o PHP é maioritária, desta forma pareceu-me necessário incluir esta secção,
porque muitas das questões que são colocadas nos grupos de discussão sobre o
PostgreSQL e sobre PHP estão relacionadas com a incompreensão da interligação
entre as várias partes envolvidas.
Antes de mais, convém dizer que as características demonstradas ao longo deste
documento e não só, permitem que o PostgreSQL possa ser usado em sites e portais
na Internet. Na realidade este é amplamente usado em ambientes empresariais e em
sistemas com base na Internet.

8.1. Arquitectura Tecnológica

Para que se entenda qual a estrutura tecnologia a usar na ligação do PostgreSQL com
o PHP e quais os factores condicionantes, aconselha-se a análise da Figura 22.

Figura 22 - Esquema simplificado da arquitectura tecnológica usando o PHP e PostgreSQL

O esquema de interligação acima corresponde ao modo mais aconselhado de

interligação, o qual recorre a três sistemas distintos. Um para servidor Web, outro
para servidor base de dados e um outro que representa o cliente. Com este género de
interligação consegue-se a assegurar um bom desempenho.
Também é possível ter no mesmo sistema o servidor Web e o servidor base de dados,
no entanto este arranjo pode levar (depende da carga imposta quer a base de dados
quer ao servidor Web) a quebras significativas de desempenho.

Página 111
 Usar PostgreSQL com PHP na Web - Capítulo 8

Usando como base a Figura 22 da página 111, o processo de interligação resulta no

seguinte:
• Cliente faz um pedido ao servidor Web (pedido HTTP sobre uma ligação
TCP previamente estabelecida com o servidor Web).
• Servidor Web invoca o interpretador PHP.
Interpretador PHP ao ser executado, carrega as bibliotecas necessárias,
neste caso a [Link] e outras, as quais são identificadas em
Unix/Linux por ficheiros com a extensão so, em Windows por
ficheiros com a extensão dll.
Após carregamento das bibliotecas, o interpretador PHP dá início ao
processamento da página.
Código da página PHP usa a base de dados através de queries SQL.
Página PHP recebe o resultado do query.
Página PHP trata o resultado, no qual, em grande parte dos casos
resulta numa página Web.
Servidor Web recolhe o resultado gerado pelo PHP e responde ao
cliente.
Página PHP termina, encerrando assim o acesso a qualquer recurso (i.e
ligações a bases de dados, etc.) que foi usado pela página a que se
segue o encerramento da ligação ao cliente.

A parte que nos interessa analisar daqui por diante é a parte do processamento do
pedido.
Partindo do pressuposto que o pedido é uma página html normal, a única tarefa que o
servidor Web vai ter que fazer é somente ler o respectivo ficheiro html e enviar o
conteúdo de volta para o cliente.
Mas se o pedido for uma página PHP, o processamento é ligeiramente diferente.
Através da Figura 22 é possível observar qual a sequência de operações necessárias
por parte do servidor Web para que este consiga atender um pedido de uma página
.php.
Recorrendo à mesma figura, constata-se que quando o servidor Web recebe como
pedido uma página PHP, o interpretador PHP é invocado com vista a processar a
página. Supondo que a página PHP em questão necessita de aceder ao PostgreSQL,

Página 112
 Usar PostgreSQL com PHP na Web - Capítulo 8

assim que no código PHP seja iniciada uma ligação à base de dados, a implementação
do PHP irá assegurar a ligação à base de dados através da API libpq do PostgreSQL.
Uma vez encerrada a ligação ou o fim da página PHP tenha sido atingido, o PHP
encerra a ligação à base de dados automaticamente.
Uma vez que tal acontece, este aspecto revela-se bastante importante porque o
handler (descritor) que representa a ligação à base de dados só existe enquanto o
processo ou thread do pedido existiam. Desta forma, não é possível 8 armazenar um
handler da base de dados e através de uma variável de sessão PHP ou mecanismo
equivalente, com o objectivo de passar essa mesma ligação para outra página.
Resumindo, em cada página PHP que necessite de aceder à base de dados é necessário
estabelecer uma ligação. Ou seja num servidor Web quantos os pedidos de páginas
PHP que existam (e que acedam à base de dados) quantas as ligações que existem à
base de dados em simultâneo.
Pretende-se com o exemplo do código PHP presente na Tabela 15, demonstrar como é
simples utilizar o PostgreSQL no PHP. O código encontra-se agrupado por fases, o
que facilita a sua compreensão.

Passo Código PHP

1 //Abertura de uma ligação à base de dados que reside na própria
//máquina (localhost), usando a conta do utilizador teste e a
//password xpto

$ligacao = pg_connect(“dbname=teste user=teste password=xpto”);

if( !$ligacao )
die(“Erro na ligação”);

8
As ligações mais usuais em página .php são as ligações não persistentes que são criada pela função
pg_connect. Há a possibilidade de se criar uma ligação persistente através da função pg_pconnect, a
qual não está associada ao processo/thread que corre a página, mas sim a um processo independente e
por isso válido em qualquer página. As ligações persistentes não são aconcelhadas na maioria dos
casos.

Página 113
 Usar PostgreSQL com PHP na Web - Capítulo 8

Passo Código PHP

2 //ligação estabelecida com sucesso
//Uma vez obtida a ligação, é necessário executar o query SQL

//Query SQL a executar

$sql = “SELECT cliente_nome FROM clientes”;

//tentar executar o query na ligação preestabelecida

$resultado = pg_query($ligacao,$sql);
if( !$resultado )
die(“Erro ao executar o query”);

3 //Query executado com sucesso, já é possível aceder ao resultado

$registo = 0; //primeiro registo, índice no resultado
do{
//obter o resultado do registo actual sob a forma de um
array associativo
$arr_rec_cli = pg_fetch_array($resultado, $registo,
PGSQL_ASSOC);
if( !$arr_rec_cli) //ultimo registo
break;

//mostrar o nome do cliente. A string “cliente_nome”

identifica o nome do campo que se quer aceder

printf(“Nome: %s”, $arr_rec_cli[“cliente_nome”]);

$registo++;
while($arr_rec_cli);

4 //Destruir o resultado e a ligação à base de dados

//Destroi o descritor do resultado. A partir do momento que o resultado
//for liberto, já não é possível efectuar operações sobre o resultado
pg_free_result( $resultado );

//Encerra a ligação à base de dados

//Uma vez encerrada a ligação, o descritor da ligação não pode ser
novamente usado.
pg_close( $ligacao );

Tabela 15 – Exemplo código PHP para extrair bytes de um campo bytea

Página 114
 Usar PostgreSQL com PHP na Web - Capítulo 8

No passo 1 é estabelecida uma ligação à base de dados. Seguidamente no passo 2, é

criado um descritor para o query SQL a executar. Através desse mesmo descritor é
possível no passo 3 obter-se a informação resultante. Por fim no passo 4, liberta-se os
recursos reservados, concretamente os descritores do query e da ligação à base de
dados.

8.2. Acesso a Campos Binários em PHP

Caso pretenda informações sobre o suporte para campos binários (BLOBs) no

PostgreSQL, por favor consulte a secção 7.

Pretende-se com este ponto explicar sob o ponto de vista do código PHP qual a
sequência das operações e as respectivas funções a usar para que se consiga
inserir/extrair conteúdos binários de campos binários.

A estrutura da tabela de referência a usar é a seguinte:

CREATE TABLE IMAGENS(

filme_id INTEGER NOT NULL,
imagem_bytes BYTEA NOT NULL,
imagem_tamanho INTEGER NOT NULL,
CONSTRAINT PK_IMAGENS PRIMARY KEY( filme_id ),
CONSTRAINT FK_filme_id FOREIGN KEY( filme_id ) REFERENCES
FILMES( filme_id ),
CONSTRAINT CK_imagem_tamanho CHECK( imagem_tamanho > 0 )
);

Figura 23 - Estrutura da tabela imagens

A sequência de operações a realizar para inserir a imagem na base de dados é:

• Leitura dos bytes da imagem a partir de um ficheiro para uma string
• Abertura da ligação à base de dados
• Tratamento dos bytes da imagem à entrada
• Criação/execussão do query SQL
• Encerramento dos recursos alocados

Página 115
 Usar PostgreSQL com PHP na Web - Capítulo 8

Código PHP para inserção de uma imagem num BLOB

//carregamento do conteudo do ficheiro para uma string

$fich_nome = “/tmp/[Link]”;
$fich = fopen( $fich_nome, “r”);
$fich_tamanho = filesize ($fich_nome);
$bytes_imagem = $contents = fread ($fich, $fich_tamanho );
$fclose( $fich );

//Abertura da ligação à base de dados

$ligacao = pg_connect(“dbname=teste user=teste password=xpto”);
$conv_imagem_bytes = pg_escape_bytea( $bytes_imagem );

//Criação/execusão query SQL

$filme_id = 10;
$sql = “INSERT INTO imagens( filme_id, imagem_bytes, imagem_tamanho )
VALUES( $filme_id, ‘$conv_imagem_bytes’::bytea, $fich_tamanho
)
“;
$resultado = pg_query($ligacao,$sql);

//libertação dos recursos usados

pg_free_result( $resultado );
pg_close( $ligacao );

Em relação à sequência de operações a realizar para a leitura da imagem a partir da

base de dados, as operações a realizar serão:
• Abertura da ligação à base de dados
• Criação/execussão do query SQL
• Leitura dos bytes da imagem presentes no registo da tabela
• Tratamento dos bytes da imagem à saída
• Encerramento dos recursos alocados

Página 116
 Usar PostgreSQL com PHP na Web - Capítulo 8

Código PHP para leitura de uma imagem a partir de um BLOB

//Abertura da ligação à base de dados

$ligacao = pg_connect(“dbname=teste user=teste password=xpto”);

//Criação/execusão query SQL

$sql =“SELECT imagem_bytes, imagem_tamanho FROM imagens WHERE filme_id=10”;
$resultado = pg_query($ligacao,$sql);
$arr_rec_imagem = pg_fetch_array($resultado, 0, PGSQL_ASSOC);
$imagem_bytes = pg_unescape_bytea( $arr_rec_imagem[“imagem_bytes”] );
$imagem_tamanho = arr_rec_imagem[“imagem_tamanho”];

//Caso a imagem fosse para ser enviada para um browser o codigo seria
header("Content-type: image/gif");
header(“Content-length: $imagem_tamanho”);
print $imagem_bytes;

//libertação dos recursos usados

pg_free_result( $resultado );
pg_close( $ligacao );

Página 117
09
 Comparação Entre SGBDs - Capítulo 9

9. Comparação Entre SGBDs

Ao longo deste documento, muitas afirmações foram feitas relativamente ao

PostgreSQL usando como termo de comparação o Oracle.
Nesta parte do documento, irão ser referidas situações de comparação somente entre
os produtos open source. Os fabricantes Oracle Inc. e Microsoft Corporation têm
cláusulas nas EULA – End User Licence Agreement, que não permitem a publicação
de quaisquer comparações numéricas. Segundo alguma informação que tive
oportunidade de ler, isto acontece, porque é muito fácil obter resultados maus,
principalmente em máquinas mal configuradas, podendo colocar em risco quer a
imagem do produto quer a da empresa. Devido a esta restrição, não serão efectuados
quaisquer testes comparativos entre o PostgreSQL e esses mesmos produtos.

Uma vez que não é possível (infelizmente) efectuar comparações de desempenho

entre as versões comerciais de SGBDs e entre o PostgreSQL, então as comparações
serão feitas com um dos mais conhecidos SGBDs open source — o MySQL.
Em primeiro lugar, gostava de deixar claro o seguinte: efectuar comparações entre o
MySQL e o PostgreSQL não me parece justo uma vez que o primeiro não implementa
um quarto das funcionalidades que o PostgreSQL implementa. Pessoalmente acho que
não há comparação possível, mesmo assim, vou fazer um esforço para salientar os
aspectos positivos e negativos de cada um destes.

O MySQL é um SGBD multi-utilizador que apresenta um conjunto razoável de

funcionalidades. No que respeita a estas e segundo o fabricante deste, as funções base
do SQL implementadas seguem um regra conhecida na área como a 80/20. Esta regra
significa que 20% das funcionalidades deste são as que são necessárias por parte de
80% das aplicações. Como fundamentação desta regra, argumentam que os
programadores de sistemas simples, conseguem “viver” sem as funcionalidades em
falta, entre as quais de encontram o suporte a chaves estrangeiras; stored procedures;
triggers; sub queries; transacções. Uma vez que estas não estão presentes, os
programadores são obrigados a recorrer a programação “criativa”.

O PostgreSQL por outro lado fornece muito mais funcionalidades que o MySQL,
entre as quais se destacam: mais funções base implementadas do SQL; stored

Página 119
 Comparação Entre SGBDs - Capítulo 9

procedures; triggers, sub queries; suporte a chaves estrangeiras; transacções; métodos

sofisticados para manipulação e extracção de informação relativa a campos do tipo
DATE, TIME e TIMESTAMP; capacidades OO sob modelo relacional; tipos de dados
geométricos; criação de tipos de dados definidos pelo utilizador; criação de
operadores definidos pelo utilizador; encapsulamento das regras de negócio das
aplicações a nível do servidor, usando para o efeito stored procedures e/ou triggers.

Uma boa forma de diferenciar a qualidade entre as duas bases de dados é através do
teste ACID – Atomicity, Consistency, Isolation, Durability. As propriedades ACID
dizem respeito às características que as transacções devem apresentar.
Para mais informações sobre as características ACID, consulte 6.4.1.

O PostgreSQL suporta todas as características que são impostas pelo teste ACID. A
implementação standard (MyISAM) de tabelas no MySQL não suporta as
características ACID a 100%, uma vez que não suporta a consistência, isolamento e
durabilidade, suportando por omissão atomicidade através de bloqueios a nível das
tabelas. Há implementações de tabelas que são asseguradas pela MySQL AB e usadas
no MySQL-max, que consiste numa versão que suporta transacções, mas mesmo esta
não é 100% ACID. A única forma de se obter suporte ACID a 100% é através de
implementações de table handlers criados por terceiros, como as Genini da
NuSphere’s. Para activar estes table handlers, durante a criação das tabelas tem que se
indicar qual o table handler a usar.

Devido à ausência de muitas funcionalidades presentes no PostgreSQL, o MySQL é

muito rápido. Este pode ser incrivelmente rápido se as aplicações se poderem adaptar
a tabelas residentes em memória (in-memory tables), desde que a durabilidade dos
dados não seja uma prioridade. Em relação ao PostgreSQL, este possui as
funcionalidades já enumeradas e há a certeza que os dados estão seguros, mesmo após
um crash. No caso de uma aplicação usar todas as funcionalidades presentes no
PostgreSQL, esta certamente que irá sofrer alguns problemas de desempenho, mas
neste capítulo o PostgreSQL tem tido melhorias muito consideráveis.
Quando se entra na área do SQL suportado por cada um das implementações, as
diferenças sobressaem. Tal deve-se à diversidade do dialecto SQL suportado pelo
PostgreSQL o qual é muito mais vasto que o suportado pelo MySQL. Suponhamos

Página 120
 Comparação Entre SGBDs - Capítulo 9

que tínhamos duas tabelas, uma com dados de funcionários e uma outra com dados
relativos às presenças destes. Suponhamos ainda que queríamos dar um bónus aos
funcionários mais velhos, que tivessem apresentado o menor número de faltas. Uma
forma simples e intuitiva de atingir o objectivo, seria através de uma instrução
UPDATE que usasse um sub query. Isto é possível de se fazer no PostgreSQL, o que
não acontece no MySQL. Para resolver este problema no MySQL, seria necessário
criar um tabela temporária que guardasse os códigos dos funcionários que tivessem o
mínimo número de faltas. Seguidamente tinha-se que criar uma outra tabela
temporária que guardasse os códigos dos funcionários mais velhos e por fim fazer um
join sobre as duas tabelas temporárias. Se por algum motivo alguém (o departamento
de Recursos Humanos) decidisse alterar os dados após estes terem sido colocados na
tabela temporária? Perante esta situação, estaríamos num cenário de inconsistência,
uma vez que os funcionários lidos da primeira vez não eram os verdadeiros
funcionários que deviam ter sido lidos. É claro que é possível contornar a situação,
efectuando um lock exclusivo em ambas as tabelas, o que não iria permitir que estas
fossem quer lidas quer escritas por qualquer outra ligação à base de dados,
impossibilitando assim que os outros funcionários pudessem usar o sistema.

Embora o princípio ACID proteja os dados de inconsistências de acessos e outros

factores, este mesmo mecanismo não previne falhas a nível de lógica das aplicações,
como por exemplo: permitir remover um cliente que tenha facturas a pagar. Um
operação deste tipo e usando o MySQL seria pura e simplesmente possível! É claro
que se pode circundar este problema, para isso é necessário implementar esta e outras
restrições do género a nível das aplicações, o que leva a que as regras básicas de
negócio andem espelhadas por estas. Isto acontece porque o MySQL não suporta
chaves estrangeiras, mas aceita as cláusulas na criação das tabelas! Usando o
PostgreSQL esta situação pura e simplesmente não aconteceria e seria detectada pela
base de dados, desde que na definição da tabela se tivessem usado chaves
estrangeiras.

Supondo que estamos perante um site que usa o MySQL. Esse site, permite que os
seus utilizadores façam alterações, criações de vários tipos de registos. Suponhamos
ainda que algumas das operações têm que alterar e criar vários registos noutras
tabelas, de forma a guardar as ordens a efectuar. Se num cenário destes e porque a

Página 121
 Comparação Entre SGBDs - Capítulo 9

operação estava a demorar muito tempo (o que não é difícil de acontecer) o utilizador
decidiu pressionar o botão stop do browser, o que aconteceria? Pura e simplesmente
os dados registados iam ser os que tinham sido registados até ao momento do
encerramento da ligação, deixando assim a base de dados inconsistente. Isto
aconteceria porque o MySQL não suporta a 100% as regras ACID. Se nesse mesmo
cenário fosse usado o PostgreSQL, podíamos estar certos que ou todos os registos
eram efectivamente registados ou nenhum eram registado, desde que as operações
sobre as tabelas fossem efectuadas sob uma transacção.

A nível de backups e segundo [37], o MySQL tem uma funcionalidade que permite
criar backups on-line (sem que o servidor mysql tenha que ser desligado). Um facto
interessante que descobri e segundo [37] é que quando é iniciado um backup on-line,
o MySQL põe a base de dados em modo read only, não permitindo assim que este
SGBD possa ser usado em ambientes que necessitem de funcionar sem interrupção.
Os verdadeiros backups on-line podem ser feitos se o table handler for InnoDB Há
no entanto um pequeno senão, é que a licença para o software de backup das tabelas
do tipo InnoDB não é grátis. Em relação aos backups on-line no PostgreSQL este
possibilita que o backup seja feito on-line e com a base de dados activa (em modo
read/write) Esta característica torna-o uma vez mais apto para ambientes cuja
disponibilidade do SGBD tem que total (27/7).

Uma vez que ambas as bases de dados tem algumas similaridades, como é que se
pode escolher entre cada uma delas? Deve-se escolher um SGBD ou outro
dependendo do grau de complexidade envolvido e também de acordo com o grau de
importância dos dados a armazenar. Por exemplo: suponhamos que se pretende migrar
um conjunto de aplicações de Oracle, Sybase ou mesmo um SQLServer. Se o SGBD
escolhido fosse o PostgreSQL então a vida ficaria mais facilitada, uma vez que os
últimos suportam as mesmas funcionalidades que o PostgreSQL.
No entanto se o objectivo fosse por exemplo permitir consultas num site, o MySQL
possivelmente dúvida seria uma boa escolha, mas só se forem feitas somente leituras
sobre a tabela. Este perante acessos concorrentes de leitura e escrita sobre os mesmos
dados, comporta-se verdadeiramente mal, uma vez que tem que bloquear toda a tabela
para conseguir consistência. O PostgreSQL nesta mesma situação, não teria qualquer
problema graças ao mecanismo de controlo da concorrência usado,

Página 122
 Comparação Entre SGBDs - Capítulo 9

o MVCC – MultiVersion Concurrency Control, o qual não bloqueia nem leitores nem
escritores e vice-versa, sendo por isso muito melhor que qualquer mecanismo de
bloqueio.

9.1. Comparação MySQL com PostgreSQL

Esta secção do documento corresponde a pequenos extractos do teste de desempenho

efectuado por um dos colunistas do site [32], o qual também é autor do site [34].
Para aceder à versão original do teste, visite [33].
Este teste foi realizado por [32] usando a versão 7.1 do PostgreSQL e a 3.23.26 beta
do MySQL, no qual foram usados dados verdadeiros e iguais os quais fazem parte de
[34] . Segundo o autor, assim que este migrou [34] para o PostgreSQL 7.1 o seu site
foi considerado segundo [35] um dos sites mais estáveis.
Para o teste, foi activado no modelo de dados do PostgreSQL as chaves estrangeiras,
com o intuito de verificar se estas degradariam o desempenho. O teste foi efectuado
numa máquina com 1GB de RAM e com quatro processadores Intel Xeon. No teste
efectuado, o cliente Apache “AB”, o Apache/PHP e as bases de dados estavam a
correr na mesma máquina. Em relação aos queries seleccionados, foram escolhidos os
que eram mais intensos a nível da base de dados.

1ª Situação : Avaliação de desempenho para acessos concorrentes de leitura e escrita

Foi criada uma página PHP que estava constantemente a executar queries, INSERT,

UPDATE, dentro de transacções. Colocaram-se trinta clientes a visitar essa página e

outros trinta em simultâneo a visitar a página “My Personal Page”. Uma vez que o
PostgreSQL suporta transacções, o autor decidiu que 25% das transacções fariam o
rollback para testar se havia quebras de desempenho.

Na Figura 24 encontram-se os resultados dos testes efectuados. Como se pode reparar

o MySQL quando confrontado neste teste com um total de clientes superiores a 20,
pura e simplesmente deixou de responde, embora o SGBD não tenha sofrido nenhum
crash.

Página 123
 Comparação Entre SGBDs - Capítulo 9

Figura 24 – Resultados do teste de concorrência Read/Update. Fonte: [33]

Através da figura, constata-se que o MySQL mesmo para 5 utilizadores teve um

desempenho muito mau, cerca de 0.77 páginas por segundo, enquanto que o
PostgreSQL perante as mesmas condições obteve cerca de 2.05 páginas por segundo.
Assim que o número de ligações vai aumentando a ideia que dá é que o PostgreSQL
foi respondendo cada vez melhor.

2ª Situação: Avaliação de desempenho para acessos que envolvam vários joins

Apresentação da página Web do “Bug Tracker”. Segundo o autor, para que a página
do “Bug Tracker” possa ser mostrada ao visitante do site esta necessita que sejam
executados 16 queries, com vista a obter toda a informação relevante ao bug
submetido.
Segundo o autor do teste, os queries envolvidos tem pouca complexidade à excepção
um deles ter que sofrer um duplo join com a tabela dos utilizadores (para obter o
nome da pessoa que enviou o bug).
Nestas situações segundo o autor, o MySQL em princípio devia sair vencedor, uma
vez que SELECT’s e joins são a sua especialidade.

Página 124
 Comparação Entre SGBDs - Capítulo 9

Figura 25 – Resultados da página “Bug Tracker”. Fonte: [33]

Segundo a análise da Figura 25 pode-se reparar que o MySQL no início do teste,

quando estavam 5 utilizadores a aceder à base de dados, teve melhores resultados do
que o PostgreSQL. Assim que o nível de concorrência foi sendo cada vez mais
elevado, o MySQL assim atingiu os 20 utilizadores em simultâneo, teve uma descida
abrupta que o levou das 14 páginas por segundo até às 8 páginas por segundo, tendo
desta forma apresentando uma quebra de desempenho na ordem dos 55%.
Note-se ainda que o PostgreSQL assim que o nível de concorrência foi aumentando,
também teve uma tendência (menos abrupta) para perder desempenho.

3ª Situação: Avaliação de desempenho que envolve joins e funções agregadoras

Nesta situação será avaliado o desempenho dos SGBDs, tendo como base o join entre
duas tabelas e o uso de uma função de agregação (COUNT) com vista a obter um total.
Correspondendo a situação descrita a uma das melhores “armas” do MySQL, o autor
do teste achou que o MySQL sairia vencedor neste teste. Segundo o autor, o
desempenho do COUNT(*) em versões anteriores do PostgreSQL era muito má. Os
resultados obtidos presentes na Figura 26, mostram como o PostgreSQL tem tido
muito melhoramentos a nível da optimização dos queries.

Página 125
 Comparação Entre SGBDs - Capítulo 9

Figura 26 – Resultado do teste “Forum”. Fonte: [33]

Resultado

Como se pode depreender das situações analisadas, o PostgreSQL em nenhuma das

situações revelou qualquer falha de estabilidade. O mesmo não se pode afirmar para o
MySQL, o qual deixou de responder na 1ª situação.
Em termos de desempenho o MySQL de forma geral não conseguiu obter melhores
resultados, nem mesmo nas situações que são a sua “especialidade”. No que respeita
ao teste da 1ª situação em que existiam situações de leituras e actualizações em
simultâneo, os resultados obtidos seriam de esperar. Isto porque o MySQL para
garantir a consistência faz bloqueios a nível da tabela, reduzindo assim desta forma o
nível de concorrência. Nestas mesmas condições, o PostgreSQL e o seu modelo de
concorrência MVCC mostraram ser um dos seus trunfos.
Segundo o autor do teste e citando-o: “Well, it means you can't simply cross
PostgreSQL off your list because it's a dog anymore. Now, not only does PostgreSQL
have dozens of very advanced features that MySQL lacks, but it doesn't appear to be
weighed down by those features as it was in the past. And that's good for everyone.”.

Página 126
 Comparação Entre SGBDs - Capítulo 9

De seguida são apresentadas algumas opiniões de utilizadores de SGBDs comerciais e

open source (MySQL e PostgreSQL) através dos quais é possível ver o que estes
pensam. Esta opiniões foram extraídas de um grupo de discussão em [36].

Re:It's all well and good..... (Score:1)

by johnnyb (johnnyb@[Link]) on Tuesday August 15, @05:07AM (#855896)
(User #4816 Info | [Link]
You should try backing up your claim. Obviously, MySQL is a toy database. It is used
for fast tabular data access, not true database work. What makes PostgreSQL a toy?
It supports things such as table inheritance, very nice transaction support, and other
things.
…

Actually, for database applications, the critical component is the hardware (assuming
you have a _real_ RDBMS - not MySQL). Sun hardware beats just about anything. If
you want to see PostgreSQL being used in the real world, take a look at
[Link]

Re:The Hate-MySQL Squad is out in force1 (Score:2)

by Wdomburg (galt@[Link].) on Monday August
14, @07:27PM (#855900)
(User #141264 Info)
….
Have you read the MySQL docs?

Their section on why not to use foreign keys at best makes me laugh, at worst makes
me cringe. This sentiment is even shared by most of the MySQL fans I know.

They gloss over their lack of sub-selects with an example that doesn't require the
feature.

They gloss over the lack of transactions and commit/rollback syntax by suggesting
table locking, which simply is not practical in high volume environments. Moreover,
their examples neatly omit the idea that you may need to lock a *LOT* of tables,
stalling updates to most of the database in order to circumvent the lack of MVCC.

Whether you want to admit it or not, the drawbacks in MySQL are *VERY* real, and
the MySQL documentation trys to play them off as minimal annoyances.

I find their lack of responsibility in this more offensive than a few causally posted
insults.

Página 127
 Comparação Entre SGBDs - Capítulo 9

Re:Is it really ready for production use? (Score:1)

by dhogaza on Monday August 14, @03:20PM (#855903)
(User #64507 Info | [Link]
…
Once the leaks were fixed, the folks I know using AOLserver+Postgresql who were
forced to reboot periodically found their problems disappeared.

We at OpenACS ([Link] have been running our Postgres

installations for months at a time with no need to reboot.

It truly has improved dramatically in the last 15 months or so.

Am I a biased Postgres fanatic? Not exactly - when I evaluated 6.4 in January, 1999 I
decided it was useless for web work, far too slow and crash-prone. When 6.5 was
released it was so dramatically improved that I changed my mind.

And, the OpenACS web toolkit project intends to support both Postgres and Interbase
(now that the latter's Open Source), so we're not Postgres-only bigots.

Re:A book is coming out soon (Score:2)

by GMontag (gmontag@[Link]) on Monday August 14, @08:03PM (#855928)
(User #42283 Info | [Link]
*IF* you are in a production environment, you need a *real* RDMS, NOT MySQL (a
file system with a SQL interface).

PostgreSQL sounds cool, will look deeper for my next DB server.

Visit DC2600

Página 128
10
 Conclusão - Capítulo 10

10. Conclusão

Antes de mais gostava de explicar o que me motivou na escolha deste projecto. Para a
escolha houveram dois factores que tiveram bastante peso:
• Possibilidade de estudar um SGBD a diferentes níveis, desde:
administração; instalação; desenvolvimento até à manutenção.
• O desafio de ter que explicar conceitos geralmente complexos a pessoas
que ainda estão a dar os primeiros passos na área para que estas ao usarem
esses mesmos conceitos e técnicas entendam o que se está a passar e não
tomem atitudes passivas como: “acontece por é assim”.
Alguns conceitos abordados eram novos para mim, por isso este projecto possibilitou-
me a oportunidade de os estudar. Embora já tivesse usado várias bases de dados e
entende-se os mecanismos inerentes, o facto é que este documento permitiu-me
aprofundar ainda mais os meus conhecimentos principalmente no que respeita a
índices, optimização de queries e também a nível da arquitectura dos próprios
SGBDs.
No que respeita à base de dados PostgreSQL, nunca pensei que houvesse um SGBD
open source com as potencialidades do PostgreSQL, uma vez que a concepção de um
SGBD é uma tarefa muito complexa e demorada. Fiquei admirado, porque a única
base de dados open source que conhecia era o MySQL, o qual me deixou muito má
imagem deste tipo de SGBDs. Do meu ponto de vista e comparando as capacidades,
funcionalidades implementadas e desempenho oferecido pelo PostgreSQL com outras
bases de dados open source (em concerto o MySQL), posso afirmar que o
PostgreSQL é sem margem de dúvida um SGBD que deve ser tido em conta, quer este
se destine a sistemas simples, quer para complexos, quer sejam para ser usados na
Web ou em empresas que necessitem de armazenar e processar grandes volumes de
dados.
Na minha opinião o PostgreSQL foi sem margem para dúvidas a melhor base de
dados open source com que tive a oportunidade de trabalhar.
Em comparação com outros SGBDs comerciais, como o Oracle, considero que o
PostgreSQL está no bom caminho, uma vez que apresenta de raiz funcionalidades
muito importantes tais como: restrições de integridade, triggers, stored procedures
em várias linguagens, transacções, criação de operadores, criação de tipos, etc.
Através de técnicas como o WAL e o MVCC pode-se reparar que o PostgreSQL está

Página 130
 Conclusão - Capítulo 10

ao nível das implementações comerciais, como é o caso do Oracle que usa as mesmas.
Pessoalmente acho que o PostgreSQL está apto para ambientes mais exigentes, como
aqueles em que a integridade dos dados e o desempenho sejam um pré-requisito, uma
vez que este usa e implementa as mesmas técnicas que a versões comerciais de outros
SGBDs.
Sendo o PostgreSQL software open source, muitas pessoas tendem a vê-lo como um
produto com pouca ou sem qualidade. A meu ver esta visão está completamente
errada. Isto porque a comunidade open source já demonstrou através de produtos
como o sistema operativo Linux; Apache (servidor Web mais usado segundo [35]) e o
Squid (proxy Web usado mundialmente quer pelo governo, quer por ISP’s e
empresas) que existe qualidade. Pessoalmente penso que o único factor que leva a que
as pessoas pensem desta forma é a desinformação.
Em relação ao PostgreSQL e focando a qualidade, perguntei a uma conjunto de
pessoas conhecidas qual a base de dados open source que geralmente usavam. A
resposta era inevitável (infelizmente), o MySQL. Quando a estas mesmas pessoas lhes
perguntei se conheciam o PostgreSQL, a grande maioria, cerca de 90% disse que
desconhecia. Pois bem perante esta falta de conhecimentos, enumerei-lhes as
características técnicas; as vantagens e desvantagens do PostgreSQL. Um colega meu
que estava a implementar um sistema de gestão de documentos para a universidade
(projecto de bacharelato), ao ter ouvido tais características, decidiu dar uma hipótese
ao PostgreSQL. Actualmente o sistema encontra-se a correr e que eu saiba (ele ter-
me-ia dito), não apresentou quaisquer problemas relacionados com a base de dados.
Um outro factor pelo qual eu depreendo que o PostgreSQL não seja tão conhecido e
usado como devia está relacionado com o facto de muito poucos ISP’s – Internet
Service Providers fornecerem o PostgreSQL nas suas propostas de alojamento.
Um outro factor que geralmente assola o software open source é a assistência técnica
e o “ficar” preso ao produto. Relativamente à assistência técnica do software open
source em geral, considero-a bastante boa e eficaz. Tem imensas vantagens, umas das
quais é que se pode contactar directamente com as pessoas que o desenvolveram,
permitindo dessa forma que possíveis correcções surjam muito rapidamente. Isto não
acontece nas empresas mundialmente conhecidas, que chegam a demorar mais de
meses a disponibilizar correcções.
No que respeita à assistência técnica oferecida pela comunidade PostgreSQL, esta é
acessível quer através de mailing lists (específicas ou gerais) ou através de contratos

Página 131
 Conclusão - Capítulo 10

comerciais realizados com a empresa psql. No caso das mailing lists, se nenhum dos
membros conseguir resolver o problema, é sempre possível recorrer à mailing list dos
programadores do PostgreSQL. Em relação ao suporte comercial, os contratos, vão
desde o suporte técnico via e-mail e telefone até situações em que as empresas podem
reservar um certo número de pessoas que a qualquer momento e quando solicitadas,
resolvem os problemas que possam haver. Em relação ao “ficar” preso ao produto,
com o PostgreSQL isso não acontece, uma vez que este tem utilitários que permitem
exportar parte ou totalidade da base de dados (incluído os dados) sob uma forma
standard – o SQL, em vez de usar formatos proprietários. Há ainda vários scripts que
permitem a migração de e para o PostgreSQL.

Por fim e para dar como encerrado o documento, gostava de referir que o PostgreSQL
actualmente e cada vez mais está a disponibilizar mecanismos e técnicas que até agora
só apareciam em implementação comerciais, em concreto está previsto o início do
desenvolvimento dos mecanismos que asseguram a replicação. À medida que estas
forem sendo implementadas estou convicto que o PostgreSQL cada vez mais irá
aproximar-se das implementações comerciais de referência como o Oracle, quer a
nível de desempenho quer de funcionalidades.
Para terminar gostava de mencionar que conheço sistemas em que o PostgreSQL está
a ser usado, tanto em ambientes Linux com em Windows 2000 Professional. No
sistema Linux, o PostgreSQL já está a correr há cerca de um ano e meio sem que me
tenha sido transmitido qualquer problema relacionado com falhas na base de dados ou
mesmo crash’s. Actualmente esse mesmo sistema está a executar a versão 7.3.3, a
qual foi passando por vários upgrades, 3 no total. O PostgreSQL em comparação com
o Oracle 7.1 da mesma organização, tem-se revelado muito mais estável, estou
convicto que a instabilidade deste, não está certamente relacionada como o próprio
Oracle mas sim com a administração quer da base de dados quer do respectivo
servidor.
No sistema PostgreSQL enumerado acima, em média existem cerca de 75 ligações
(não é um valor muito importante, mas só para referência) em simultâneo, sob as
quais são realizadas execuções desde os queries mais simples, até aos queries mais
complexos ( que envolvem joins bastante complexos entre várias tabelas, cerca de 15
e disparos de triggers), sem que nunca os utilizadores se tenham queixado quer por

Página 132
 Conclusão - Capítulo 10

parte da base de dados quer pelo sistema como um todo, o qual se encontra a executar
funções muito variadas (router/firewall, PostgreSQL, Apache).
Certamente deve ter reparado que ao longo deste foram sendo feitas comparações
quase sempre com o Oracle. Não escolhi efectuar tais comparações com os produtos
open source, porque pura e simplesmente a grande maioria das funcionalidades que o
PostgreSQL disponibiliza, não se encontram disponíveis em mais nenhum produto
open source (que eu tenha conhecimento). Por isso optei por o comparar com o
Oracle, porque este é sem dúvida o produto comercial com que o PostgreSQL pode
ser comparado, uma vez que usa as mesmas técnicas.

Página 133
11
 Bibliografia - Capítulo 11

11. Bibliografia

[1] Rnald R. Plew / Ryan K. Stephens, Sams Teach Yourself SQL in 24 Hours,
SAMS, ISBN: 007231245X
[2] José Luís Pereira, Tecnologia de Bases de Dados, FCA, ISBN: 972-722-143-
2, 1998
[3] Raghu Ramakrishnan / Johannes Gehrke, Database Management Systems
(second edition), McGraw-Hill, ISBN 0-07-232206-3, 1999
[4] [Link]
[5] [Link]
[6] [Link]
[7] [Link]
[8] [Link]
[9] [Link]
[10] [Link]
[11] PostgreSQL Administrator Guide - Chapter 1. Installation Instructions - 1.5.
Installation Procedure
[12] [Link]
[14] PostgreSQL Administrators Guide – Client Authentication
[15] PostgreSQL Programmers Guide - I. Client Interfaces - 1.2. Database
Connection Functions
[16] PostgreSQL Programmers Guide – - I. Client Interfaces - 1.4. Asynchronous
Query Processing
[17] PostgreSQL Administrator Guide - Chapter 10. Monitoring Database
Activity - 10.2. Statistics Collector
[18] PostgreSQL Administrator Guide – 3. Server Run-time Environment - 3.4
Runtime configuration
[19] PostgreSQL Reference Manual – I. SQL Commands - Alter table
[20] PostgreSQL Reference Manual - II. Server Programming — Chapter 19 -
PL/pgSQL - SQL Procedural Language
[21] PostgreSQL User guide – 9 Concurrency Control
[22] PostgreSQL Administrator guide- 12 Write Ahead Logging
[23] PostgreSQL User's Guide – Chapter 5. Data Types - 5.4 Binary Strings

Página 135
 Bibliografia - Capítulo 11

[24] PostgreSQL Programmer's Guide – I. Client Interfaces - Chapter 2. Large

Objects
[25] J. Melton and A. Simon. Understanding the New SQL: A Complete Guide.
Morgan Kaufmann, 1993.
[26] C. Date and H. Darwen. A Guide to the SQL Standard (3rd ed). Assison-
Wesley, 1995.
[27] [Link]
[28] A. Eisenberg and J. Melton. Sql: 1999, formerly known as sql3 ACM
SIGMOD Record, 28(1): 131-139, 1999
[29] PostgreSQL Reference Manual – I. SQL Commands – Create Database
[30] PostgreSQL Reference Manual – I. SQL Commands – Create User
[31] Raghu Ramakrishnan / Johannes Gehrke, Database Management Systems
(second edition), Chapter 10 Hash-Based Indexing, McGraw-Hill, ISBN 0-
07-232206-3, 1999
[32] [Link]
[33] [Link]
[34] [Link]
[35] [Link]
[36] [Link]
[37] [Link]
[38] [Link]

Página 136
12
 Anexos - Capítulo 12

12. Exemplo de execução síncrona usando libpq

#include <stdio.h>
#include <libpq-fe.h>
void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
main()
{
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
int nFields;
int i,
j;
/* FILE *debug; */
PGconn *conn;
PGresult *res;
/*
* comecemos por configurar os parâmetros para a ligação ao servidor. Se algum
* dos parâmetros for NULL, o sistema tenta usar valores razoáveis por
* omissão, caso tal na seja possível, usa valores obtidos durante
* compilação do PostgreSQL.
*/
pghost = NULL; /* nome DNS do servidor */
pgport = NULL; /* porta TCP do servidor */
pgoptions = NULL; /* opções especiais para configuração servidor */
pgtty = NULL; /* tty para debugging do servidor */
dbName = "clientes"; /* nome da base de dados */

/* estabelece uma ligação à base de dados */

conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

/*
* verifica se a ligação foi estabelecida com sucesso
*/

if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "A ligação à base de dados ’%s’ falhou.\n", dbName);
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
/* debug = fopen("/tmp/[Link]","w"); */

Página 138
 Anexos - Capítulo 12

/* PQtrace(conn, debug); */

/* inicializa uma transação */

res = PQexec(conn, "BEGIN");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "O comando BEGIN falhou\n");
PQclear(res);
exit_nicely(conn);
}

/*
* É necessário invocar PQclear no objecto PGresult sempre que o resultado não
* seja necessário, para evitar fugas de memória (memory leaks)
*/

PQclear(res);

res = PQexec(conn, "SELECT * from clientes");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "O comando SELECT falhou. Linha:%d, Ficheiro:%s\n",
__LINE__, __FILE__ );
PQclear(res);
exit_nicely(conn);
}

/* primeiro, mostra os nomes dos atributos */

nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");

/* de seguida, mostra os valores para cada tuplo */

for (i = 0; i < PQntuples(res); i++)

{
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);

/* commit à transacção */

res = PQexec(conn, "COMMIT");

PQclear(res);

/* encerra a ligação à base de dados e liberta os recursos */

Página 139
 Anexos - Capítulo 12

PQfinish(conn);
/* fclose(debug); */
return 0;
}

12.1. Exemplo de execução assíncrona usando libpq

/* Descrição: Supondo que temos que mostrar a informação do cliente e também uma
análise de compras. A análise de compras consiste em todas as reservas de todos os
movimentos efectuados do cliente desde a sua existência, de forma a que o operador
possa aconselhar melhor o cliente.
Supondo ainda que os valores necessários tem que ser calculados em tempo de
execução em vez de serem valores somados a partir de campos acumuladores.
Supondo ainda que há um requisito funcional que obriga a que a informação base
sobre o cliente seja mostrada em primeiro lugar.
Suponhamos também que o query (irrelevante para o exemplo) está residente numa
vista (view) com o nome de v_resumo_cliente_demorado.
Presuma-se ainda que o número de ligações à base de dados está limitado por uma
ligação por cliente.

Resolução possível: Criar um query que execute de forma assíncrona o query sobre a
vista v_resumo_cliente_demorado. Após o envio do query (o query esta a ser
processado pelo servidor em background), executar um query síncrono para obter os
dados base do cliente.

Resolução impossível (até ao momento): Criar uma thread extra que partilhe a mesma
ligação e dessa forma usar uma ligação para calcular os dois queries. Esta solução
torna-se impossível porque a API libpq é thread safe, o que quer dizer que se uma
thread (a principal) está a aceder à ligação, mais nenhuma outra o pode fazer em
simultâneo.
*/

#include <stdio.h>
#include "libpq-fe.h"

Página 140
 Anexos - Capítulo 12

void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}

main()
{
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
int nFields;
int i,
j;
PGconn *conn;
PGresult *res;
PGresult *resAsync;
PGnotify *notify;
int notifyCompleted=0;
int haveClienteInfo = 0;
/*
* comecemos por configurar os parâmetros para a ligação ao servidor. Se algum
* dos parâmetros for NULL, o sistema tenta usar valores razoáveis por
* omissão, caso tal na seja possível, usa valores obtidos durante
* compilação do PostgreSQL.
*/

pghost = NULL; /* nome DNS do servidor */

pgport = NULL; /* porta TCP do servidor */
pgoptions = NULL; /* opções especiais para configuração servidor */
pgtty = NULL; /* tty para debugging do servidor */
dbName = "clientes"; /* nome da base de dados */

/* estabelece uma ligação à base de dados */

conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

/*
* verifica se a ligação foi estabelecida com sucesso
*/

if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "A ligação à base de dados ’%s’ falhou.\n", dbName);
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}

Página 141
 Anexos - Capítulo 12

/*
* Indica ao servidor que queremos criar um género de variável de condição, em
* que uma ou várias conexões se encontram possivelmente à escuta
*/

res = PQexec(conn, "LISTEN identificador_query_complexo");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "O comando LISTEN falhou.\nLinha:= %d\nFicheiro:%s",
__LINE__, __FILE__ );
PQclear(res);
exit_nicely(conn);
}
/*
* É necessário invocar PQclear no objecto PGresult sempre que o resultado não
* seja necessário, para evitar fugas de memória (memory leaks)
*/

PQclear(res);

/*
* Envia os dois queries sem aguardar por qualquer resultado.
*/
resAsync = PQsendQuery (conn, "SELECT * from v_resumo_cliente_demorado;
NOTIFY identificador_query_complexo;");
if (!resAsync || PQresultStatus(resAsync) != PGRES_COMMAND_OK)
{
fprintf(stderr, "O comando SELECT e ou NOTIFY falhou.\nLinha:=
%d\nFicheiro:%s", __LINE__, __FILE__ );
PQclear(resAsync);
exit_nicely(conn);
}

while (1)
{
/*
* Aguarda um pouco entre verificações;
*/
sleep(1);
/* le notificações assíncronas que tenham vindo do servidor */
PQconsumeInput(conn);
if( notifyCompleted && haveClienteInfo ){
; /*Mostra a informacao do resumo de movimentos*/
break;
}
/* verifica se existem mensagens assíncronas a processar */

notify = PQnotifies(conn));

if( notify != NULL)

Página 142
 Anexos - Capítulo 12

notifyCompleted = 1;
else if( !haveClienteInfo ){
; /* algo do género do exemplo anterior, para o SELECT */
haveClienteInfo = 1;
;/*parte do código para mostrar a informacao do cliente */
}
}

PQclear( resAsync );
Pqclear( res );

/* encerra a ligação à base de dados e liberta os recursos */

PQfinish(conn);
return 0;
}

12.2. Modelo de dados de referência ao documento

Instruções DDL para criação do modelo de dados de referência

CREATE TABLE DISTRITOS(

distrito_codigo SMALLINT NOT NULL,
distrito_nome VARCHAR(60) NOT NULL,
CONSTRAINT PK_DISTRITOS PRIMARY KEY( distrito_codigo ),
CONSTRAINT CK_DISTCOD CHECK( distrito_codigo > 0 )
);

Explicação: A instrução CREATE TABLE acima vai criar uma tabela de nome DISTRITOS que
vai ter dois campos: distrito_codigo e distrito_nome. Os valores possíveis para o
campo distrito_codigo, são os mesmos do tipo de dados SMALLINT, cujos limites são: -
32768 até +32767.
Os valores possíveis para o campo distrito_nome, serão todos as sequências de
caracteres numéricos e alfanuméricos, cujo comprimento máximo seja inferior ou igual a
60 caracteres. O facto de se definir o tipo de dados deste atributo como VARCHR em vez
de CHAR tem as implicações descritas de seguinda.
O tipo de dados VRCHAR(n), permite guardar sequencias de caracteres, mas cujo número
de caracteres não seja superior a n, podendo no entanto ser inferior.
Por exemplo: Para armazenar a sequência de caracteres “abcd” num campo VARCHAR(20) a
base de dados iría gastar somente 4 caracteres em vez dos 20 para cada registo. O tipo
de dados VARCHAR(n) é conhecido na área das bases de dados como character varying(n).
Se a definição do campo fosse em vez de VARCHAR(60) fosse CHAR(60), o SGBD para
sequência de caracteres “abcd”, iria armazenar os quatros caracteres, preenchendo os
restantes com espaços em branco. Ou seja a sequência de carateres deixou de ter um
comprimentos variável e passou a ter um comprimento fixo.
Como os atributos distrito_codigo e distrito_nome estão definidos com a restrição
NOT NULL, então nenhum destes campos poderão estar por preencher.
Nota: NULL é diferente de espaços em branco. NULL significa que o valor está ausente.
Para além das restrições impostas pelos tipos de dados dos atributos, há duas
restrições de integridade que estão definidas:

Página 143
 Anexos - Capítulo 12

CONSTRAINT PK_DISTRITOS PRIMARY KEY( distrito_codigo )

CONSTRAINT CK_DISTCOD CHECK( distrito_codigo > 0 )
A primeira de nome PK_DISTRITOS indica ao SGBD que os valores presentes no atributo
distrito_codigo são obrigatoriamente não nulos(NULL) e únicos, uma vez que se trata de
uma restrição(constraint) do tipo PRIMARY KEY.
A Segunda restrição de nome CK_DISTCOD permite refinar a gama de valores que serão
aceites para o campo distrito_codigo, neste caso os valores têm que ser superiores a
zero.

CREATE TABLE REGIOES(

regiao_codigo SMALLINT NOT NULL,
regiao_nome VARCHAR(60) NOT NULL,
CONSTRAINT PK_REGIOES PRIMARY KEY( regiao_codigo )
);

CREATE TABLE CODIGO_POSTAL(

codigopostal_id SERIAL NOT NUlL,
distrito_codigo SMALLINT NOT NULL,
regiao_codigo SMALLINT NOT NULL,
localidade_nome VARCHAR(60) NOT NULL,
CONSTRAINT PK_CODIGOPOSTAL PRIMARY KEY( distrito_codigo, regiao_codigo ),
CONSTRAINT FK_distritocodigo FOREIGN KEY( distrito_codigo ) REFERENCES
DISTRITOS( distrito_codigo ),
CONSTRAINT FK_regiaocodigo FOREIGN KEY( regiao_codigo ) REFERENCES
REGIOES( regiao_codigo ),
CONSTRAINT UK_codigopostal_id UNIQUE( codigopostal_id )
);

Explicação: O tipo de dados SERIAL (também conhecido em outras base de dados por
autonumber e autoincrement) usado na definição do campo codigopostal_id corresponde a
um tipo de dados INT, mas cuja responsabilidade de gerar o valor é do SGBD e por isso
a sequencia de valores é assegurada por este. Uma vez que este atributo faz parte das
chaves candidates, logo este será usado como chave estrangeira noutras tabelas,
passando assim a haver menos redundância.
Na 5ª linha da instrução CREATE TABLE acima, está presente a criação de uma restrição
de integridade referencial, a qual cria uma chave estrangeira. O valor a inserir no
campo distrito_codigo tem que ser um dos valores que estejam presentes no campo
distrito_codigo da tabela DISTRITOS.
Na 6ª linha da instrução CREATE TABLE encontra-se a definição de uma restrição
semelhante à da 5ª linha.
A última linha define uma restrição de integridade que garante que os valores

presentes no campo codigopostal_id são sempre únicos, logo este campo vi funcionar como

uma chave candidata e por isso será usada como chave estrangeira noutras tabelas.

CREATE TABLE CLIENTES(

cliente_id SERIAL NOT NULL,
cliente_nome VARCHAR(80) NOT NULL,
cliente_morada VARCHAR(60) NOT NULL,
cliente_idade SMALLINT NOT NULL,
codigo_postal_id INTEGER NOT NULL,
cliente_sexo CHAR NOT NULL,

Página 144
 Anexos - Capítulo 12

CONSTRAINT PK_CLIENTES PRIMARY KEY( cliente_id ),

CONSTRAINT FK_codigopostal FOREIGN KEY( codigo_postal_id )
REFERENCES CODIGO_POSTAL( codigopostal_id ),
CONSTRAINT CK_cliente_idade CHECK( cliente_idade >=2 ),
CONSTRAINT CK_cliente_sexo CHECK( cliente_sexo IN( 'M', 'F' ) )
);

CREATE TABLE CINEMAS(

cinema_id SERIAL NOT NULL,
cinema_nome VARCHAR(60) NOT NULL,
codigo_postal_id INTEGER NOT NULL,
cinema_morada VARCHAR(60) NOT NULL,
CONSTRAINT PK_CINEMAS PRIMARY KEY( cinema_nome, codigo_postal_id ),
CONSTRAINT FK_codigopostal FOREIGN KEY( codigo_postal_id ) REFERENCES
CODIGO_POSTAL( codigopostal_id ),
CONSTRAINT UK_cinema_id UNIQUE( cinema_id )
);

CREATE TABLE SALAS(

sala_id SERIAL NOT NULL,
cinema_id INTEGER NOT NULL,
sala_nome VARCHAR(30) NOT NULL,
sala_capacidade SMALLINT NOT NULL,
CONSTRAINT PK_SALAS PRIMARY KEY( cinema_id, sala_nome ),
CONSTRAINT FK_CINEMA_ID FOREIGN KEY( cinema_id ) REFERENCES
CINEMAS( cinema_id ),
CONSTRAINT CK_capacidade CHECK( sala_capacidade > 5 ),
CONSTRAINT UK_sala_id UNIQUE( sala_id )
);

CREATE TABLE FILMES(

filme_id SERIAL NOT NULL,
filme_nome VARCHAR(60) NOT NULL,
filme_tipo CHAR NOT NULL,
filme_duracao SMALLINT NOT NULL,
CONSTRAINT PK_FILMES PRIMARY KEY( filme_nome ),
CONSTRAINT CK_filme_tipo CHECK( filme_tipo IN('A','D','R') ),
CONSTRAINT CK_filme_duracao CHECK( filme_duracao > 10 ),
CONSTRAINT CK_filme_id UNIQUE( filme_id )
);

CREATE TABLE IMAGENS(

filme_id INTEGER NOT NULL,
imagem_bytes BYTEA NOT NULL,
imagem_tamanho INTEGER NOT NULL,
CONSTRAINT PK_IMAGENS PRIMARY KEY( filme_id ),
CONSTRAINT FK_filme_id FOREIGN KEY( filme_id ) REFERENCES
FILMES( filme_id ),
CONSTRAINT CK_imagem_tamanho CHECK( imagem_tamanho > 0 )
);

Página 145
 Anexos - Capítulo 12

CREATE TABLE SESSAO(

sessao_id SERIAL NOT NULL,
sala_id INTEGER NOT NULL,
filme_id INTEGER NOT NULL,
sessao_hora TIME NOT NULL,
CONSTRAINT PK_SESSAO PRIMARY KEY( sala_id, filme_id, sessao_hora ),
CONSTRAINT FK_sala_id FOREIGN KEY( sala_id ) REFERENCES SALAS( sala_id ),
CONSTRAINT FK_filme_id FOREIGN KEY( filme_id ) REFERENCES
FILMES( filme_id ),
CONSTRAINT CK_sessao_hora CHECK( sessao_hora BETWEEN '14:00' AND '4:00' ),
CONSTRAINT UK_sessao_id UNIQUE( sessao_id )
);

CREATE TABLE RESERVAS(

cliente_id INTEGER NOT NULL,
sessao_id INTEGER NOT NULL,
bilhete_tipo CHAR NOT NULL,
quantidade SMALLINT NOT NULL,
CONSTRAINT PK_RESERVAS PRIMARY KEY( cliente_id,sessao_id,bilhete_tipo ),
CONSTRAINT FK_sessao_id FOREIGN KEY( sessao_id ) REFERENCES
SESSAO( sessao_id ),
CONSTRAINT CK_bilhete_tipo CHECK( bilhete_tipo IN('N','E','R','C') ),
CONSTRAINT CK_quantidade CHECK( quantidade >=1 )
);

CREATE TABLE VENDAS(

cliente_id INTEGER NOT NULL,
sala_id INTEGER NOT NULL,
filme_id INTEGER NOT NULL,
bilhete_tipo CHAR NOT NULL,
quantidade SMALLINT NOT NULL,
venda_valor MONEY NOT NULL,
venda_data DATE NOT NULL DEFAULT current_date,
venda_hora TIME NOT NULL DEFAULT current_time,
CONSTRAINT PK_VENDAS PRIMARY KEY( cliente_id, filme_id,
venda_data,
bilhete_tipo ),
CONSTRAINT FK_sala_id FOREIGN KEY(sala_id) REFERENCES SALAS( sala_id ),
CONSTRAINT FK_cliente_id FOREIGN KEY( cliente_id ) REFERENCES
CLIENTES( cliente_id ),
CONSTRAINT FK_filme_id FOREIGN KEY( filme_id ) REFERENCES
FILMES( filme_id ),
CONSTRAINT CK_bilhete_tipo CHECK( bilhete_tipo IN('N','E','R','C') ),
CONSTRAINT CK_quantidade CHECK( quantidade >=1 ),
CONSTRAINT CK_venda_valor CHECK( venda_valor > 0.0 )
);

Página 146