sexta-feira, 30 de novembro de 2012

Reaproveite Senhas de Atendimento com o PostgreSQL

Voltei a ensinar a disciplina de banco de dados e uma dúvida inteligente de um aluno aplicado ajudou a animar o final da última aula. Respondi à dúvida e aparentemente ele ficou satisfeito, mas aproveitei a ideia dele para compor este post. O problema a ser resolvido é manter uma lista de códigos com valores entre 1 e N para serem utilizados por um sistema ou aplicação. Os códigos são reaproveitados, isto é, caso um código não esteja mais disponível, pode ser reutilizado, como por exemplo, no caso de uma senha para atendimento em uma fila. Como manter e tratar este problema com os recursos do banco de dados postgres?



Em primeiro lugar, deixo para os comentários dos interessados maiores discussões sobre as possíveis alternativas. Apresentarei uma solução simples que utiliza triggers, mas espero que a mesma possa ser melhorada e questionada por você, caro leitor.

* Solução de lista de códigos

Permite o controle da utilização e reutilização de códigos através de uma lista com dois campos, um sendo o primeiro o valor do código e outro a indicação de sua utilização ou não.
A lista é armazenada em uma tabela e inicializada na sua criação. Esta lista é atualizada por uma trigger na tabela "usuária" dos códigos. O gatilho atualiza a tabela de código em dois momentos: na utilização de um código e na liberação do código para reuso.

* Tabela de Lista de Códigos

Abaixo, coloco o script da nossa lista de códigos. Coloquei a cláusula UNIQUE para o campo de código para evitar repetições que violassem a integridade da lista e ao tempo gerar um índice implícido para o campo de código, a ser utilizado nas consultas. Vamos inicializar a nossa lista com 10 códigos, inteiros com valor entre 1 e 10, com o valor false, equivalente a "desalocado".

CREATE TABLE lista_codigos (codigo integer UNIQUE, flag_utilizado boolean);
INSERT INTO lista_codigos (codigo, flag_utilizado) VALUES (1,false), (2,false), (3,false), (4,false), (5,false), (6,false), (7,false), (8,false), (9,false), (10,false); --INICIALIZACAO
* Tabela Usuária de Códigos

A tabela em que serão armazenados os códigos utilizados apresentará dois gatilhos, sendo um em caso de inserção de código, e outra para exclusão de código. Adicionei também uma restrição UNIQUE no campo código para não ter de validar tentativas de utilizar o código mais de uma vez.

CREATE TABLE usa_codigos (codigo integer UNIQUE);
Abaixo, os códigos das trigers e trigger functions associadas.

CREATE OR REPLACE FUNCTION pegar_codigo() RETURNS trigger AS $pegar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = true WHERE codigo = new.codigo;
    RETURN new;
END;
$pegar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER pega_codigo BEFORE INSERT ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE pegar_codigo ();

CREATE OR REPLACE FUNCTION liberar_codigo() RETURNS trigger AS $liberar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = false WHERE lista_codigos.codigo = old.codigo;
    RETURN old;
END;
$liberar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER libera_codigo AFTER DELETE ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE liberar_codigo ();

* Testes da Lista


CENÁRIO 1: Reservando códigos. Neste cenário, basta inserir um registro na tabela usa_codigos e consultar a tabela lista_codigos para verificar se foram reservados os códigos.

INSERT INTO usa_codigos (codigo) VALUES (1), (3), (4), (9), (10);

O resultado obtido está correto.

SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | SIM
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)

CENÁRIO 2: Liberando códigos. Inicialmente, libero o código 3. A liberação é a exclusão da tabela de códigos.

DELETE FROM usa_codigos WHERE codigo = 3;
O resultado obtido está de acordo com o desejado.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)

CENÁRIO 3
: Liberando todos os códigos de uma só vez. O resultado corresponde à expectativa.

DELETE FROM usa_codigos;
 codigo | reservado
--------+-----------
      1 | NÃO
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)


CENÁRIO 4: Testando reservar código já reservado. A restrição CHECK garante a integridade e o código é reservado, como esperado.

postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
INSERT 0 1
postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
ERRO:  duplicar valor da chave viola a restrição de unicidade "usa_codigos_codigo_key"
DETALHE:  Chave (codigo)=(1) já existe.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)
CENÁRIO 5: Testando liberar código já liberado. Não ocorre erro e a alocação dos códigos permanece inalterada. Ao menos neste teste inicial, as operações foram realizadas a contento!

postgres=# DELETE FROM usa_codigos WHERE codigo = 3;
DELETE 0
postgres=# SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;
 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)


* Adicionando Funcionalidades

Podem ser implementadas consultas ou funções que ajudem a emitir as senhas e liberar as senhas para reaproveitamento, além de consultas básicas para:

-  Consultar o primeiro código disponível

-  Consultar o último código disponível
-  Consultar a quantidade de códigos disponíveis
-  Consultar a quantidade de códigos não disponíveis

Mas deixo aos leitores estas tarefas como exercício.

* Notas conclusivas

- Uma trigger ou restrição de acesso pode evitar que se utilize a cláusula UPDATE para a tabela de códigos, ou tratar o update, o que eu acho mais trabalhoso!
- Deixo para os leitores a tarefa de implementar esta solução utilizando arrays, e empregando outras formas alternativas e talvez mais eficientes.

segunda-feira, 26 de novembro de 2012

Recuperando Informações de Sessão no PostgreSQL


Qual é o banco de dados corrente? E qual o usuário corrente? Qual é exatamente a versão do postgres que estamos utilizando? Estas informações são úteis para se poder trabalhar com bancos de dados, e são fornecidas por funções informacionais de sessão. Vamos apresentar neste post uma listagem com as funções disponibilizadas no postgres e alguns exemplos de utilização:

current_database() name Nome do banco de dados corrente.
current_schema() name Nome do esquema corrente.
current_schemas(boolean) name[] Nomes dos esquemas no caminho de procura incluindo, opcionalmente, os esquemas implícitos.
current_user name Nome do usuário do contexto de execução corrente.
inet_client_addr() inet Endereço da conexão remota.
inet_client_port() int4 Porta da conexão remota.
inet_server_addr() inet Endereço da conexão local.
inet_server_port() int4 Porta da conexão local.
session_user name Nome do usuário da sessão.
user name Equivalente à função "current_user"
version() text Informações relativas à versão corrente do PostgreSQL

* Banco de dados e Esquema

Para recuperar informações de banco de dados e esquema, utilizam-se as funções current_database(), current_schema() e current_schemas(boolean).

Exemplo 1: Dados dos esquemas.

postgres=# SELECT current_database(), current_schema();
 current_database | current_schema
------------------+----------------
 postgres         | public
(1 registro)
Exemplo 2: Caminho de procura com e sem os esquemas implícitos.

postgres=# SELECT current_schemas(true) as SCHEMAS_TODOS, current_schemas(false) AS SCHEMAS_EXPLICITOS;
    schemas_todos    | schemas_explicitos
---------------------+--------------------
 {pg_catalog,public} | {public}
(1 registro)

* Informações de Conexão

Permitem a recuperação dedados sobre os servidores e clientes utilizados na conexão, e as portas de comunicação utilizadas. Funções: inet_client_addr(), inet_client_port(), inet_server_addr() e inet_server_port().

Exemplo 3: Informações da conexão corrente.

postgres=# SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port();
 inet_client_addr | inet_client_port | inet_server_addr | inet_server_port
------------------+------------------+------------------+------------------
                  |                  |                  |                
(1 registro)

* Dados do usuário da sessão.

São consultados com as funções sem parênteses current_user, session_user e user.

Exemplo 4: Dados do usuário e mudança de usuário.
postgres=# SELECT current_user, session_user, user;
 current_user | session_user | current_user
--------------+--------------+--------------
 postgres     | postgres     | postgres
(1 registro)

postgres=# SET SESSION AUTHORIZATION 'user1';
SET
postgres=> SELECT current_user, session_user, user;
 current_user | session_user | current_user
--------------+--------------+--------------
 user1        | user1        | user1
(1 registro)

* Versão do PostgreSQL

Exemplo 5: Consulta à versão do postgresql.

postgres=# SELECT version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.1 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit
(1 registro)


* Recuperando todos os dados da sessão

Exemplo 6: Consulta a todas as informações de sessão:

postgres=# SELECT 'BANCO    : ' || current_database() AS INFO UNION SELECT 'ESQUEMA  : ' || current_schema() AS INFO UNION
SELECT 'ESQUEMAS : ' || CAST(current_schemas(true) AS VARCHAR) AS INFO UNION
SELECT 'USUARIO  : ' || current_user AS INFO UNION
SELECT 'SES. USR.: ' || session_user AS INFO UNION
SELECT 'USER     : ' || user AS INFO UNION
SELECT 'CLI. ADR.: ' || inet_client_addr() AS INFO UNION
SELECT 'CLI. POR.: ' || inet_client_port() AS INFO UNION
SELECT 'SER. ADR.: ' || inet_server_addr() AS INFO UNION
SELECT 'SER. POR.: ' || inet_server_port() AS INFO UNION
SELECT 'VERSAO   : ' || version() AS INFO;
                                                        info                                                       
--------------------------------------------------------------------------------------------------------------------

 ESQUEMAS : {pg_catalog,public}
 ESQUEMA  : public
 USER     : postgres
 SES. USR.: postgres
 USUARIO  : postgres
 VERSAO   : PostgreSQL 9.2.1 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit
 BANCO    : postgres
(8 registros)




quarta-feira, 14 de novembro de 2012

sexta-feira, 9 de novembro de 2012

PGTUNE: Otimize a configuração do PostgreSQL!

Tuning de banco de dados não é exatamente uma tarefa fácil. É necessário conhecer os rudimentos de bancos de dados, o SGBD utilizado em profundidade, linguagem SQL (ou outra, dependendo do SGBD), além de saber informações precisas sobre os bancos de dados em si e as aplicações que os utilizam.

São muitas variáveis em jogo e o processo de otimização raramente possibilita a quantidade de testes realmente necessária em virtude do tempo ser exíguo, e os sistemas não poderem parar. O PGTUNE é um script python, desenvolvido por Gregory Smith, que atua como ferramenta complementar para o tuning de bancos de dados postgresql, mais especificamente, sugerindo alterações no complexo arquivo postgresql.conf visando um melhor desempenho.

Neste post, vamos apresentar o funcionamento básico do pgtune e elencar pontos fortes e limitações.

* Instalação

O PGTUNE demanda a instalação do python, a qual é bastante simples para linux, windows e MAC (além de máquina virtual do java, .NET, etc.).

Assumindo que você já possui o postgres e o python instalados, basta acessar o sítio do PGTUNE, baixar o arquivo compactado e descompactá-lo.

Para este post utilizamos:
- pgtune versão 0.9.3
- postgresql versão 9.2.1
- python versão 2.6.5


* Utilização

Abra o prompt de comando e entre na pasta descompactada do pgtune.

O comando abaixo aciona o pgtune, passando como parâmetros o arquivo postgresql.conf do postgres e o arquivo de saída (no caso, psql.conf), gerado com sugestões para o tuning do banco de dados.

python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf

O resultado da execução aparece na figura abaixo, com a listagem do arquivo de saída:


Caso deseje testar as alterações, altere o postgresql.conf e reinicie o servidor do banco.

Além dos arquivos de entrada e saída, o script apresenta outros recursos realmente interessantes, através de parâmetros (opcionais) de execução:

* -M ou –memory: Utilizar este parâmetro para fornecer a memória do servidor. Caso não esteja especificado, o pgtune tentará detectar a memória da máquina de execução do script, assumindo que é a máquina servidora.
* -T ou –type: Especifica o tipo de base de dados. As opções aceitas são:
- DW - Grandes massas de dados com poucas alterações mas com consultas extremamente complexas
- OLTP - Sistema tradicional de processamento de transações
- Web - Sistema web, com grande número de acessos concorrentes
- Mixed - Sistema com características intermediárias em relação aos demais tipos.
- Desktop - Sistema monousuário
* -c or –connections: Especifica o máximo de conexões desejado. Este é o parâmetro mais relevante em sistemas com grande número de usuários.

Exemplos adicionais com os parâmetros:

python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -M 500000
 
python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -c 1000
 
pgtune-0.9.3$ python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -M 500000 -c1000



* Considerações finais

O pgtune mostrou que pode ser utilizado sem problemas como ferramenta complementar de tuning de banco de dados. Recomendo que antes de qualquer alteração no postgresql.conf, que se faça testes e backup do arquivo antigo.

Abaixo, listo pontos fortes e limitações:

Vantagens:
- Facilidade de instalação e utilização
- Ser multiplataforma, por utilizar python
- Regras de alteração de valores são acessíveis dentro do script
- Facilidade de interpretação de resultados
- Pode ser utilizado sem interromper o funcionamento do servidor de banco de dados
- Não altera o ambiente de produção, deixando para o DBA a opção de aceitar ou não as alterações sugeridas

Limitações:
- Não faz testes aprofundados no ambiente que indiquem a eficácia da otimização
- Funcionar através de cálculos sobre regras relacionadas à configuração, e não realiza testes de simulação comprobatórios
- Não considera outros fatores além da configuração do postgresql.conf
- Não existe uma forma clara de distinguir entre os diversos tipos de sistema, e um servidor pode ter mais de um banco de dados com tipos diferentes, o que invalidaria a configuração com o parâmetro -T.
- O parâmetro -M não aceita abreviações como 4gb, 5000mb.

Teste você mesmo e me diga o que achou!

segunda-feira, 22 de outubro de 2012

APGDIFF: Ferramenta Mediana que pode ser Útil!

Basta um momento de descuido para termos várias versões de um mesmo banco de dados em funcionamento. Identificar os pontos de alteração em esquemas de bancos de dados manualmente é mais do que cansativo: é arriscado.

Para solucionar estes problemas, existem várias ferramentas para comparação entre esquemas de banco de dados, dentre elas a "Another PostgreSQL Diff Tool", também chamada (apgdiff). É uma ferramenta livre que apresenta versão gratuita na web e atualmente está em sua versão 2.4. 

Neste post é mostrado o funcionamento básico da versão web da ferramenta, que é bem simples, e são colocadas as primeiras impressões na sua utilização.

* Funcionamento

A operação da ferramenta é bem simples:
- Inicialmente, acesse o sítio da ferramenta;
- Faça o backup dos bancos de dados que se deseja comparar. Com o utilitário pg_dump a sintaxe poderia ser: pg_dump -U postgres -v -f teste_atual.txt postgres (extraindo o banco de dados postgres para o arquivo txt teste_atual.txt)
- Uma vez que tenha extraído o backup dos dois bancos de dados a comparar, acione a opção "Create Diff online" e faça o upload dos arquivos de backup obtidos
- Acione a opção de comparação de esquemas. Abaixo, colocamos um exemplo passo a passo.

Tela inicial

Inclusão de arquivos para comparação

Diferença entre os esquemas

* Primeiras impressões

O site foi bastante rápido em suas análises, mas não detectou todas as poucas alterações realizadas.

No teste foi acrescentado um novo usuário, o que não foi detectado pela ferramenta. A chave primária da tabela incluída também não foi encontrada pela apgdiff. A ferramenta também apresentou como diferente uma chave primária que na verdade estava igual em ambos os esquemas.

A primeira impressão é que a apgdiff é interessante, mas está longe de ser perfeita. A análise dos backups mostrou que podem ser deixados de lado detalhes importantes, o que não tira sua importância como potencial ferramenta auxiliar.

O trabalho do DBA, ajudado por scripts próprios e outras ferramentas, seguramente pode se beneficiar dos recursos da apgdiff. Mas como qualquer ferramenta, esta apresenta limitações, algumas das quais identificadas neste post.

quarta-feira, 17 de outubro de 2012

Range Types: Novo recurso do Postgresql 9.2!

Início e fim, começo e encerramento. Armazenar intervalos de valores é uma tarefa importante que já estava disponível no Postgresql, porém de modo mais dispendioso em termos de programação. Era possível por exemplo criar dois campos indicando os extremos de um intervalo sem problemas, implementar uma função ou ainda criar um tipo intervalar com o comando CREATE TYPE.

Representação Matemática de Intersecção de Intervalos de Valores


A versão 9.2 apresenta o conceito de "Range Types", que engloba um tipo de dados específico para intervalos, além de recursos para cálculos e manipulações relacionadas a estes tipos peculiares de dados. Considero este um grande avanço, que pode reduzir o esforço de implementação e aumentar o desempenho em várias situações. Pode-se por exemplo, indexar um campo intervalar.

Neste post, embora não se busque esgotar o tema, vamos ilustrar as principais possibilidades desta nova feature com exemplos.

* Tipos de Intervalos

O postgresql apresenta seis tipos de intervalos padrão, sendo alguns discretos e outros contínuos, mas você pode criar outros utilizando CREATE TYPE:

- int4range — Intervalo de inteiros de 4 bytes.
- int8range — Intervalo de "bigint" (inteiros de 8 bytes)
- numrange — Intervalo de números reais
- tsrange — Intervalo de timestamps sem time zone
- tstzrange — Intervalo de timestamps com time zone
- daterange — Intervalo de datas

* Operações Básicas para Definir Dados Intervalares

Um intervalo consiste em um conjunto de valores delimitados por um valor inicial e um final. O postgres oferece opção de se trabalhar com intervalos total e parcialmente limitados e não limitados. Os delimitadores compreendem colchetes "[]" para os intervalos fechados e parênteses "()" para os intervalos abertos.

 Intervalos abertos e fechados

Abaixo segue uma sequência de exemplos ilustrando as principais operações feitas com intervalos e os resultados obtidos.

Exemplo 1: Intervalo fechado contendo os números 1 a 5.
postgres=# SELECT '[1,5]'::numrange;
 numrange
----------
 [1,5]
(1 registro)

Exemplo 2: Intervalo fechado em campo inteiro, contendo os números de 1 a 5.
postgres=# SELECT '[1,5]'::int4range;
 int4range
-----------
 [1,6)
(1 registro)

Exemplo 3: Intervalo de 1 a 5, aberto no 5, isto é, não contendo o valor 5.
postgres=# SELECT '[1,5)'::numrange;
 numrange
----------
 [1,5)
(1 registro)

Exemplo 4: Intervalo sem limite máximo.
postgres=# SELECT '[1,]'::numrange;
 numrange
----------
 [1,)
(1 registro)

Exemplo 5: Intervalo sem limite mínimo.
postgres=# SELECT '(,1]'::numrange;
 numrange
----------
 (,1]
(1 registro)

Exemplo 6: Duas formas de expressar um intervalo sem quaisquer limites.
postgres=# SELECT '(,)'::numrange, numrange(null, null);
 numrange | numrange
----------+----------
 (,)      | (,)
(1 registro)

Exemplo 7: Intervalos de 1 a 5, abertos e fechados, criados a partir do construtor do tipo numrange.postgres=# SELECT numrange(1,5,'[)'), numrange(1,5,'(]'), numrange(1,5,'[]'), numrange(1,5,'()');
 numrange | numrange | numrange | numrange
----------+----------+----------+----------
 [1,5)    | (1,5]    | [1,5]    | (1,5)
(1 registro)

Exemplo 8: Duas formas de expressar um intervalo sem elementos.postgres=# SELECT numrange(1,1,'[)'), '[1,1)'::numrange;
 numrange | numrange
----------+----------
 empty    | empty
(1 registro)

Exemplo 9: Intervalo em campo data.
postgres=# SELECT daterange('12/01/2012',current_date,'[)');
        daterange       
-------------------------
 [2012-01-12,2012-10-17)
(1 registro)

Exemplo 10: Intervalo em campo data, exemplo 2.
postgres=# SELECT daterange(current_date -10,current_date,'[)');
        daterange       
-------------------------
 [2012-10-07,2012-10-17)
(1 registro)

Exemplo 11: Teste de pertencimento de elemento a um intervalo.
postgres=# SELECT int4range(10, 20, '[]') @> 9, int4range(10, 20,'[]') @> 15, int4range(10, 20,'[]') @> 21;
 ?column? | ?column? | ?column?
----------+----------+----------
 f        | t        | f

Exemplo 12: Recuperando Limites Superior e Inferior de um Intervalo
postgres=# SELECT lower(numrange(10, 100, '[]')), upper(numrange(10, 100, '[]'));
 lower | upper
-------+-------
    10 |   100
(1 registro)

Exemplo 13: Verificação de sobreposição de intervalos
É feita com o operador &&.

postgres=# SELECT numrange(10, 20) && numrange(25, 30), daterange('01/01/2010', '31/12/2011') && daterange('01/01/2011', '31/12/2012');
 ?column? | ?column?
----------+----------
 f        | t
(1 registro)

Exemplo 14: Intersecção de Intervalos
O teste é feito com o operador *, retornando 'empty' caso a intersecção não apresente elementos.

postgres=# SELECT int4range(10, 20,'[]') * int4range(15, 25,'[]'),daterange('2010-01-01','2012-06-30','[]') * daterange('2011-01-01','2012-12-30','[]');
 ?column? |        ?column?        
----------+-------------------------
 [15,21)  | [2011-01-01,2012-07-01)
(1 registro)

postgres=# SELECT int4range(10, 20) * int4range(25, 35);
 ?column?
----------
 empty
(1 registro)

Exemplo 15: Teste se intervalo é vazio (empty)

postgres=# SELECT isempty(numrange(10, 20)), isempty(numrange(10, 10));
 isempty | isempty
---------+---------
 f       | t
(1 registro)

Exemplo 16: Criação de Tabelas e Visões e Índices com Intervalos

postgres=# CREATE TABLE teste_range (rang_data daterange, rang_int4 int4range, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
CREATE TABLE
postgres=# CREATE TABLE teste_range_2 (rang_data daterange PRIMARY KEY, rang_int4 int4range UNIQUE, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "teste_range_2_pkey" na tabela "teste_range_2"
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "teste_range_2_rang_int4_key" na tabela "teste_range_2"
CREATE TABLE
postgres=# CREATE OR REPLACE VIEW view_teste_range AS SELECT * FROM teste_range ORDER BY rang_data;
CREATE VIEW
postgres=# CREATE INDEX ind_teste_range ON teste_range (rang_data, rang_int4);
CREATE INDEX
postgres=# INSERT INTO teste_range (rang_data, rang_int4) VALUES (daterange('2010-01-01','2012-06-30','[]'), int4range(10, 20,'[]'));
INSERT 0 1
postgres=# select * from TESTE_RANGE;
        rang_data        | rang_int4 | rang_int8 | rang_num | rang_timestamp
-------------------------+-----------+-----------+----------+----------------
 [2010-01-01,2012-07-01) | [10,21)   |           |          |
(1 registro)

* Pontos Fortes

- Programas, funções e consultas que utilizem intervalos ficam menores.
- Opções para manipulação de intervalos são confiáveis e não demandam pluigins ou instalação de novos componentes.

* Pontos Fracos

- Perda de compatibilidade com outros SGBDs em todas as funcionalidades que utilizem intervalos.

segunda-feira, 24 de setembro de 2012

Blog Indiano de PostgreSQL

Achei bastante interessante este blog. É de um indiano chamado Ragavedra e está em inglês.

Os pontos fortes são:
- Informações sobre a arquitetura de aplicações PostgreSQL
- As imagens de alta qualidade que ele utiliza, que acredito serem de "produção própria". São explicativas e relativamente detalhadas.

Acesse o site aqui.



sexta-feira, 24 de agosto de 2012

Categorias de Volatilidade de Funções no Postgres

O processamento de funções em bancos de dados é uma opção bastante utilizado em certos ambientes. A quantidade e complexidade das lógicas de negócio que são alocadas nas funções podem ser expressivas. O lado negativo de se empregar funções no Postgres ou em qualquer SGBD é a necessidade de um tempo significativo de processamento.

Caso se utilize muitas funções o sistema, ao mesmo tempo em que gerencia os dados, passa a ser também um processador de regras da camada de negócios do sistema. Um recurso válido para facilitar o processamento de funções, reduzindo o esforço computacional, é empregar as categorias de volatilidade de funções no Postgres ao escrever os seus códigos. Esta feature promete maior desempenho no processamento das funções sem a necessidade de alterações no corpo das funções implementadas.

As categorias de funções são:

- VOLATILE - Uma função volatile pode alterar os dados de um banco. Também pode retornar valores diferentes em chamadas sucessivas com os mesmos parâmetros. Uma função com essas características terá seu plano de execução recalculado a cada chamada da função, portanto não é passível de otimização;

- STABLE - Quando uma função é STABLE, assume-se que a mesma não modifica a base de dados e retornará os mesmos valores caso sejam fornecidos os mesmos parâmetros na mesma chamada. Desta forma, o processador de consultas do postgres pode otimizar múltiplas chamadas da função para uma única chamada;

- IMMUTABLE - Se a sua função não altera jamais a base de dados e sempre retorna os mesmos valores para os mesmos parâmetros, não importando o contexto, deve ser classificada como IMMUTABLE, facilitando a otimização das consultas. Caso uma função seja assinalada como IMMUTABLE, mas não seguir este comportamento, apresentando respostas distintas para um mesmo conjunto de parâmetros, por exemplo, este será um erro difícil de rastrear.

* Como definir a classificação das funções?

Se a função apresentar comandos INSERT, DELETE ou UPDATE, pode portanto alterar o banco de dados, e deve ser assinalada como VOLATILE. Caso utilize ou produza valores aleatórios (random()), ou empregue funções que variam seu resultado como currval(), timeofday(), também se classifica como VOLATILE.

Caso ao sua função não possua comandos de alteração de dados, mas apresente o comando SELECT internamente, o resultado da consulta pode variar a cada chamada da função, pois o banco de dados está sujeito a atualizações entre as execuções da função, então a mesma possivelmente pode ser classificada como STABLE. Utilizei o termo "possivelmente" porque a consulta pode ser feita, por exemplo, em tabelas que não sofrem atualização, e neste caso, a função até poderia ser classificada como IMMUTABLE(!). SE sua função emprega funções da família de current_timestamp, possivelmente se alinhará com a classificação STABLE, pois o valor destas funções não muda no decorrer da transação.

Funções que realizem cálculos matemáticos sem utilização de valores aleatórios e funções com resultado variável são candidatas a IMMUTABLE, assim como funções que realizem validações simples sobre os parâmetros fornecidos, sem uso do comando SELECT.

O valor padrão é VOLATILE, assumido pelo postgres quando não fornecido pelo programador.

Uma função VOLATILE enxerga alterações no banco de dados ocorridas durante sua execução, enquanto que funções STABLE e IMMUTABLE não apresentam esta visibilidade.

* IMMUTABLE

O exemplo 1 gera erro em tempo de execução por alterar o banco de dados com o comando INSERT em uma função IMMUTABLE.

Exemplo 1:

CREATE TABLE teste (codigo integer, descricao varchar(20));

CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
    INSERT INTO teste VALUES (1,'Teste Class.');
    RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_ins();
ERRO:  INSERT não é permitido em uma função não-volátil
CONTEXTO:  comando SQL "INSERT INTO teste VALUES (1,'Teste Clas.')"
PL/pgSQL function "teste_ins" line 2 at comando SQL
O exemplo 2 executa normalmente. ele utiliza o SELECT mas não consulta tabelas ou visões, então pode ser IMMUTABLE.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_soma_p1_p2(p1 integer, p2 integer) RETURNS integer AS $$
BEGIN
    RETURN (select $1 + $2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_soma_p1_p2 (1,1); 

teste_soma_p1_p2
------------------
                2
(1 registro)

postgres=# SELECT teste_soma_p1_p2 (5,4);
 teste_soma_p1_p2
------------------
                9
(1 registro)



* STABLE

O exemplo abaixo funciona como função STABLE, apresentando o comando SELECT.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_select() RETURNS varchar(10) AS $$
BEGIN
    RETURN (SELECT CAST(count(*) AS VARCHAR) FROM teste);
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_select();
 teste_select
--------------
 1
(1 registro)

O exemplo 4 apresenta função STABLE om utilização da função current_timestamp().

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_timest() RETURNS varchar(30) AS $$
BEGIN
    RETURN (SELECT CAST(current_timestamp AS VARCHAR) );
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_timest();
         teste_timest         
-------------------------------
 2012-08-24 10:57:59.429209-03
(1 registro)

* VOLATILE

O exemplo abaixo só funciona se a função for VOLATILE.

Exemplo 5:

postgres=# CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
INSERT INTO teste VALUES (1,'Teste Class.');
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL VOLATILE;

postgres=# SELECT teste_ins();
 teste_ins
-----------
 OK
(1 registro)

Os exemplos apresentados são relativamente simples, mas quanto mais complexa a função, maior o ganho de utilização da classificação de funções. Empregue no seu dia a dia este recurso do postgres, mas não se esqueça de fazer testes criteriosos para cada função, pois os erros de execução causados pela classificação incorreta de uma função podem de difícil detecção.

sexta-feira, 17 de agosto de 2012

Tratamento de Parâmetros de Funções com Pl/PgSQL

Existem várias formas de se processar erros em parâmetros fornecidos a funções. Existem casos em que valores diferentes do esperado e nulos são fornecidos, o que faz com que as entradas de parâmetros devam receber um tratamento meticuloso.

Neste post vamos apresentar alguns recursos simples que podem ser utilizados para tratar parâmetros em funções no Postgresql.

* Raise Notice

Utilize Raise Notice para disparar avisos ao usuário da função. Estes avisos podem funcionar como advertências, apresentar informações relevantes sobre os parâmetros fornecidos e sobre a execução da função em si.

Estes avisos não interrompem a execução da função nem são considerados erros pelos aplicativos.

Exemplo 1:

CREATE OR REPLACE FUNCTION teste_par_1(par_1 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_1) < 2 THEN
    RAISE NOTICE 'Valor não fornecido ou muito pequeno: %',$1;
    RETURN 'AVISO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;



banco=# Select teste_par_1 ('T');
NOTA:  Valor não fornecido ou muito pequeno: T
 teste_par_1
-------------
 AVISO
(1 registro)


* Raise Exception


Utilize Raise Exception para disparar um erro ao usuário da função acompanhado de uma mensagem explicativa. A emissão de erro interrompe a execução da função.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_par_2(par_2 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_2) < 2 THEN
    RAISE EXCEPTION 'Formato inválido: %',$1;
    RETURN 'ERRO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;



banco=# Select teste_par_2 ('T');
ERRO:  Formato inválido: T

* RETURNS NULL ON NULL INPUT ou STRICT

O uso da cláusula STRICT ou "RETURNS NULL ON NULL INPUT" faz com que seja retornado valor nulo caso um dos parâmetros fornecidos seja nulo. É um recurso interessante e que pode poupar tempo de processamento em funções mais elaboradas. Para que a função aceite valores nulos, existe a cláusula "CALLED ON NULL INPUT", mas a mesma é pouco utilizada por ser o comportamento default para as funções no Postgresql.


Observe no exemplo abaixo que o valor nulo (null) é diferente da string sem elementos.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_par_3(par_3 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;


 
banco=# Select teste_par_3 (null);
 teste_par_3
-------------
 
(1 registro)

banco=# Select teste_par_3 ('');
 teste_par_3
-------------
 OK
(1 registro)
banco=# Select teste_par_3 ('T');
 teste_par_3
-------------
 OK
(1 registro)

A utilização de várias validações conjuntamente é a melhor forma de assegurar que a função receba valores processáveis. O exemplo abaixo é uma ilustração desta necessidade.

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_par (par_todos varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_todos) <=3  THEN
    RAISE EXCEPTION 'Valor muito pequeno não nulo: %',$1;
    RETURN 'ERRO';
ELSE
    IF char_length(par_todos) <=5  THEN
        RAISE NOTICE 'Valor muito pequeno: %',$1;
        RETURN 'AVISO';
    END IF;
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;


pf=# Select teste_par (null);
 teste_par
-----------

(1 registro)

pf=# Select teste_par ('T');
ERRO:  Valor muito pequeno não nulo: T
 

pf=# Select teste_par ('Test');
NOTA:  Valor muito pequeno: Test
 teste_par
-----------
 AVISO
(1 registro)


Atualmente existem além de NOTICE e EXCEPTION vários outros qualificadores das mensagens: DEBUG, LOG, INFO, NOTICE, WARNING, e EXCEPTION, sendo que este último é o valor padrão. 

Utilize-os nas suas validações, tentando sempre manter o código o mais simples possível!

segunda-feira, 30 de julho de 2012

Expresso Livre: Mais de 500000 contas de correio eletrônico. Todas PostgreSQL!



Email, Agenda, Catálogo de Endereços, Workflow e Mensagens Instantâneas em um único ambiente. Essa é a promessa do Expresso Livre, software livre mantido por um consórcio de entidades que engloba:
  • CAIXA ECONÔMICA FEDERAL
  • CELEPAR - Empresa de TI do Governo do Paraná
  • PROCERGS - Empresa de TI do Governo do Rio Grande do Sul
  • PROGNUS - Empresa de Consultoria
  • SERPRO - Empresa de TI do Governo Federal
A ferramenta está em desenvolvimento desde 2007 e é utilizada atualmente por mais de 500.000 usuários em 167 empresas ou instituições, e utiliza como banco de dados o PostgreSQL. Vale a pena conhecer!

terça-feira, 17 de julho de 2012

Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Neste post, vamos gerar automaticamente comandos GRANT e REVOKE utilizando SQL. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres |       10 | t           | t        | t         | ******** |          |
gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.


2. Quais são as tabelas criadas no banco?



Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)

3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)


Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)

4. Revogando permissões de acesso


Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:



pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros)


5. Considerações Práticas


Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;


Este post é mais um exercício do que um exemplo prático, mas pode ser útil em situações em que se deseje maior controle.

Consulte as especificações dos comandos GRANT e REVOKE para ver a grande diversidade de opções disponíveis!