Translate

Pesquisar este blog

terça-feira, 2 de fevereiro de 2016

Criando uma função no PostgreSQL usando linguagem C - Parte 2: funções que recebem tipos compostos

Receber um registro de uma tabela, na lista de parâmetros é possível.

Observe que PG_MODULE_MAGIC só deve ser usada uma vez em todo o projeto, senão há erro de compilação.






#include "postgres.h"
#include "fmgr.h"  

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(limite_salario);

Datum limite_salario(PG_FUNCTION_ARGS)
{
    HeapTupleHeader  t = PG_GETARG_HEAPTUPLEHEADER(0);
    int32            limite = PG_GETARG_INT32(1);
    bool isnull;
    Datum salario = GetAttributeByName(t, "salario", &isnull);
 
    if (isnull)
        PG_RETURN_NULL(); 
 
  PG_RETURN_BOOL(DatumGetInt32(salario) > limite);
}



Existe uma tabela profissional assim:
create table profissional (
id serial primary key,
salario int,
nome varchar
);



Declarar a função assim:
CREATE FUNCTION limite_salario(profissional,int) returns bool
as '/tmp/teste.so', 'limite_salario' language c strict;






Chamar a função assim:
select a.*,limite_salario(a.*,10) from profissional a

Criando uma função no PostgreSQL usando linguagem C - Parte 1: funções de retorno simples

 É possível criar funções em linguagem C e chamá-las no banco PostgreSQL via SQL. Para isso é necessário criar uma biblioteca dinâmica (.so) contendo as rotinas.

As funções que fiz nesse exemplo usei o NetBeans. O Code::Blocks só travava na minha máquina.

É preciso colocar como diretório de inclusão de cabeçalhos /usr/include/postgresql/server . Claro esse caminho pode variar de distro.

Exemplo 1: Soma de dois parâmetros inteiros.

#include<postgres.h>
#include <fmgr.h>
#include<funcapi.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(soma);


Datum soma(PG_FUNCTION_ARGS) {
 
     PG_RETURN_INT32( PG_GETARG_INT32(0)+PG_GETARG_INT32(1));
}


  • PG_MODULE_MAGIC:  Esta macro obrigatória indica que o arquivo .so é válido para ser usado pelo PostgreSQL.
  • PG_FUNCTION_INFO_V1: Cada função C que vai ser utilizada no banco deve ser declarada com o uso desta macro.
  • Datum: O retorno da função deve ser sempre do tipo Datum.
  • PG_FUNCTION_ARGS: A lista de parâmetros de entrada da função. Use a macro PG_GETARG_xxx(x)  para pegar esses valores de entrada.
  • PG_RETURN_xxx(x): Use no lugar de return.




Na Makefile do NetBeans eu coloco a seguinte linha no começo do arquivo  para ele copiar o teste.so para /tmp e ficar mais fácil com permissões.

install: build
    cp -f $(CND_ARTIFACT_PATH_Debug) /tmp/teste.so





Agora eh só declarar a função no SQL com os parâmetros de entrada e saída adequados.

CREATE FUNCTION soma(integer,integer) RETURNS integer
     AS '/tmp/teste.so', 'soma'
     LANGUAGE C STRICT;


domingo, 8 de novembro de 2015

Benchmark de Firebird vs PostgreSQL.

Firebird e PostgreSQL são dois dos mais robustos bancos de dados open source existentes. Pode ser difícil determinar qual será o melhor para determinado projeto.
Neste post, eu fiz um teste de velocidade entre os dois bancos, usando o JMeter  .

Carreguei um volume considerável de dados do OpenStreetMap para a massa de dados: mais de 75 milhões de pontos de latitude e longitude, e mais 6 milhões de vias/ruas da América do Sul.

Permita-me apresentar os oponentes:

PostgreSQL

O PostgreSQL possui o slogan "The most advanced Open Source database". É um banco de dados bastante completo e avançado. Sua instalação vem com o Pgadmin3 que é o cliente oficial para administração da base. 
O PostgreSQL possui funcionalidades, algumas bastante exóticas, como tais:
  • Alta disponibilidade e replicação.
  • Full Text Search (FTS) com indexação.
  • Backups incrementais contínuos, com possibilidade de restauração até uma linha de tempo anterior, se necessário.
  • Foreign Data Wrappers (FDW), para a conexão com meios de dados externos.
  • Criação de índices concorrentes, sem a necessidade de travar a tabela em produção (CREATE INDEX CONCURRENTLY).
  • Suporte a funções e tipos de dados de XML e JSON. Permite indexar partes desses arquivos também. 
  • Suporte a funções e tipos de dados trigonométricos.
  • Linguagens de programação no lado do servidor plugáveis.


Firebird

O slogan do Firebird é "The true open source database". (Esse slogan me parece uma zueira com o MySQL, que não é totalmente livre, hehehee).















As funcionalidades do Firebird denotam sua grande facilidade de uso e versatilidade:
  • Três modos de servidor (SuperServer indicado para servidores menores), (Classic para cargas maiores), Embedded e SuperClassic. O formato dos arquivos de banco são os mesmos.
  • Capacidade de rodar em modo embarcado, sem necessidade de um servidor. Isso é bastante usado em programas embarcados feitos em Delphi ou Lazarus.
  • Boa retro-compatibilidade dos servidores com versões anteriores de arquivos. Facilitando a migração de versão do servidor.
  • Backups incrementais, com o comando nbackup. (Mas eu acho menos eficientes que os do PostgreSQL).

O servidor

Eu utilizei um desktop com disco SATA e CPU AMD Athlon64, 4 cores e 4 GB de RAM. Linux Slackware64 14.1com kernel compilado.

O PostgreSQL versão 9.4.4, compilado com -march=native -mtune=native -fPIC.

O Firebird SuperServer versão 2.5.4 compilado com -march=native -mtune=native -fPIC.



Parâmetros

No postgresql.conf:
shared_buffers = 384MB
work_mem = 12MB
checkpoint_segments = 32

No firebird.conf
FileSystemCacheThreshold = 67108864
DefaultDbCachePages = 8192

(O Firebird possui uma documentação oficial muito fraca, foi difícil procurar na net como melhorar esses parâmetros).

A Carga de dados

Eu baixei o arquivo da América do Sul do site do OpenStreeMap. Após tentar entender o XML do arquivo, criei um programa para transformá-lo em CSV e ser carregado no PostgreSQL com o comando COPY.

Depois disso, criei os índices no PostgreSQL, e fiz o comando COPY novamente para gerar um arquivo TXT e carregar no Firebird usando uma tabela external file desse arquivo TXT. Criei os índices no Firebird.

Minha impressão foi de que o PostgreSQL carregou bem mais rápido, tanto o arquivo texto como criou os índices. Infelizmesnte eu não anotei os tempos de cada um mas o PostgreSQL foi mais rápido.


Gráficos

Rodei os seguintes comandos, para cada thread:
  1. SELECT pela chave primária.
  2. INSERT na tabela de log.
  3. SELECT pela chave primária.
  4. SELECT por índice de inteiro.


Tempo médio gasto, com 10, 50 e 100 usuários.





















Aqui vemos que o Firebird possui mais dificuldade de escalar à medida que o número de usuários aumenta.

sexta-feira, 19 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 2.

Controle de fluxo.

Toda linguagem de programação têm controle de fluxo, que são
instruções IF, CASE, FOR, WHILE, FUNCTION, etc.


Declaração de variável local:
CREATE OR REPLACE FUNCTION myfunction(entrada integer)
  RETURNS integer AS
$BODY$
declare
    i int;
    v varchar(200);
    rec record;
begin
...




IF-THEN-ELSE:

IF <condição SQL> THEN
 <instrução>;
 <instrução>;
ELSIF  <condição SQL> THEN
  <instrução>;
  <instrução>;
ELSE
  <instrução>;
  <instrução>;
END IF;



LOOP-END LOOP:

LOOP
  <instrução>;
  <instrução>;
    EXIT WHEN <condição SQL>;
  <instrução>;
  <instrução>;
END LOOP;

* Observação: A instrução EXIT WHEN pode ser usada em qualquer lugar dentro do loop.



RECORD IN SELECT LOOP-END LOOP:

declare
   rec record;
begin
     FOR rec IN select id, nome from tabela order by nome
     LOOP
     <instrução>;
     <instrução>;
     END LOOP;
...


* Observação: É preciso criar uma variável do tipo record ou rowtype para usar nesse tipo de loop;



Arrays:
Arrays de qualquer tipo de dados podem ser declarados. Coloca-se o tipo de dados seguido de [].

declare
   arr varchar[];
   str varchar;
begin
   arr:=array(select nome from usuario limit 100); --Aqui definimos com um SELECT em uma tabela.
   arr:=ARRAY['Zé', 'Fulano', 'Ciclano'];                --Colchetes é diferente de parênteses.
   arr:=string_to_array('Zé;Fulano;Beltrano',  ';');   --É possível expandir varchar delimitado para array.
   str:=array_to_string(arr,  ';');                                --O oposto também é possível.

   arr:=arr||'Lóide';                                                    --Concatenando um array.

   IF array_length(arr, 1)>5 THEN                            --Retornando o tamanho de um array.
   ...
   END IF;

   FOR str IN SELECT unnest(arr)                         --Estamos fazendo um LOOP com o array.
   LOOP
   <instrução>;
   <instrução>;
   END LOOP;


end;



CASE (com variável):

CASE var
WHEN <valor1>,<valor2> THEN
  <instrução>;
  <instrução>;
WHEN <valor3>,<valor4> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



CASE (sem variável):

CASE
   WHEN <condição1> THEN
  <instrução>;
  <instrução>;
WHEN <condição2> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



FOR:

FOR i IN 1..10 --i vai de 1 a 10
LOOP
    <instrução>;
    <instrução>;
END LOOP;

FOR i IN REVERSE 10..1 --i vai de 10 a 1
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

FOR i IN REVERSE 10..1 BY 2 --i vai de 10,8,6,4,2
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

quinta-feira, 18 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 1

Programar dentro do banco de dados é uma polêmica entre desenvolvedores, com certeza. Mas há vantagens em usar o banco de dados para desenvolver a lógica de negócio dos aplicativos.

Eu mesmo já desenvolvi usando o PL/PGSQL, e achei muito produtivo e performático.

Produtivo principalmente quando o sistema exige consultas SQL complexas, e você está usando uma linguagem de programação como Java.
Performático porque uma consulta pré-compilada no banco exige menos passos do banco para executar as instruções.

A principal desvantagem consiste em que não poderá portar para outros DBs esse código feito. Mas se você usa apenas um banco de dados isso não é problema.

Um exemplo de função:
CREATE OR REPLACE FUNCTION teste(entrada integer)
  RETURNS integer AS
$BODY$
begin
    return entrada*5;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


A função teste apenas retorna o parâmetro entrada vezes 5. Para executá-lo com o parâmetro chame o seguinte comando:

Select * from teste(6)

Ela retornará 30 que é 5*6.




Veja outro exemplo. Suponha que você tenha uma tabela usuarios (id int primary key, nome varchar, descricao varchar):

CREATE OR REPLACE FUNCTION get_usuario(id_user int, out user_nome varchar, out user_descricao varchar)
  RETURNS record AS
$BODY$
begin


   select a.nome, a.descricao from usuarios as a
   where a.id= id_user
   into user_nome, user_descricao;

   if not found then
     raise exception '%', 'Usuário não encontrado';
   end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



  • Veja que os parâmetros user_nome e user_descricao são parâmetros de saída(OUT). 
  • O tipo de retorno da função de torna record. Isso tem que ser porque temos mais de um valor de retorno.
  • Pegamos os dados que precisamos com a instrução SELECT e colocamos os valores nas variáveis usando a cláusula INTO.
  • Em seguida é usada a variável do PG found. Ela serve para testar se algum registro retornado com SELECT ou UPDATE foi encontrado. Se nenhum registro foi encontrado, found será igual a false. Se o registro não foi encontrado, disparamos exceção.

select * from get_usuario(7)
Isso retornará os dados do usuário 7, ou dispara exceção se ele não existe.
Observe que não colocamos os parâmetros de saída, eles é que retornarão da função.





Também é possível retornar várias linhas de uma vez só. Para isso o tipo de retorno deve ser precedido de SETOF.

CREATE OR REPLACE FUNCTION get_usuarios(out user_id int, out user_nome varchar, out user_descricao varchar)
  RETURNS setof record AS
$BODY$
begin

  return query select id, nome, descricao from usuarios
  order by nome;  


end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

  • Quando o retorno da função é declarado com SETOF, deve-se retornar os dados com a instrução RETURN QUERY ao invés de setar uma variável sozinha. 
  • É possível chamar RETURN QUERY mais de uma vez, porque ele NÃO vai retornar da função imediatamente.

Atualizando o PostgreSQL de versão

Ao contrário do Firebird, o PostgreSQL usa um formato de banco para cada versão.

Até mesmo trocar a mesma versão do PG em Windows e Linux, ou entre 32 ou 64 bits não vai funcionar. Por motivos óbvios, restaurar um Point-In-Time Recovery (PITR) também não vai funcionar.

Por isso o PostgreSQL você deve fazer um dump da database antiga com o  pg_dumpall. E depois de atualizar a database deve fazer um restore com psql.

$ pg_dumpall -h myhost -p porta > db.sql

Agora instale o novo PostgreSQL, depois faça a restauração da base com o psql.

$ cat db.sql | psql -h myhost -p porta -U usuario

Atualizando o Firebird de versão.

No Firebird, não é necessário fazer o dump de uma database, apenas instalar o novo banco, desinstalando o antigo. Os bancos de dados (Arquivos FDB) podem permanecer inalterados porque o novo banco sempre reconhece as versões antigas.

Mas se você está migrando para uma versão mais antiga isso pode ser diferente. É preciso ver o dialeto em que o banco está e ver se o antigo banco suporta.