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!