PostgreSQL

Chaves artificiais no PostgreSQL

Criar chaves artificiais virou um vício com o advento das ferramentas de ORM. Mesmo antes delas, as chaves artificiais já existiam e temos bons motivos (e maus motivos também) para adota-las. Existem várias formas de criar uma chave artificial. No PostgreSQL temos duas bastante utilizadas. Uma utiliza as sequências, para gerar números sequenciais. Note que não há garantia de que a sequência não ganhe buracos no caminho. Outra utiliza números aleatórios que nunca se repetem, o famoso UUID. O uso de sequências é bastante conhecido e difundido, mas o UUID é útil em sistemas distribuídos onde você pode gerar um número para sua chave artificial em diversos nós distintos. Se você utilizar uma sequencia comum, você não poderá garantir que dois nós não utilizem o mesmo número, perdendo assim a unicidade da sua chave. Existem sequências distribuídas também, até a versão 10 do PostgreSQL ela não foi implementada ainda. O Oracle utiliza sequencias distribuías no Oracle RAC onde cada nó guarda no cache números diferentes para os próximos valores da sequência. No MySQL, é comum criar sequências com um salto entre cada número, onde cada nó possui um valor inicial diferente, assim com 3 nós por exemplo, o nó um teria números sequenciais como 1, 4, 7, 10… o nó 2 teria números 2, 5, 8, 11… e o nó 3 teria números 3, 6, 9, 12…

UUID

UUID é um acrônimo de “Universally unique identifier” ou identificador único universal. O PostgreSQL tem suporte nativo para este tipo de dado e funções para gerar localmente estes números, sem precisar da aplicação para isso. Mas estas funções não são nativas no core. Por sorte estão num módulo do contrib (sempre recomendo instalar todos os módulos do contrib e depois criar as extensões conforme a necessidade). Vejamos um exemplo simples de sua utilização:

Note que não existe um objeto como uma SEQUENCE aqui, apenas o tipo de dados uuid e a função uuid_generate_v4() para gerar os números aleatórios (que nunca se repetem). Você também pode automatizar um pouco as coisas e usar a função uuid_generate_v4() como valor padrão para nossa coluna id:

 

 

Note que utilizei duas sintaxes ligeiramente diferentes para obter o mesmo resultado no INSERT.

Por fim, existem diversos algorítimos diferentes para gerar o número aleatório do UUID, o pacote uuid-ossp tem 5 diferentes funções para gera-lo. Leia a documentação deste módulo e escolha a mais apropriada antes de começar a utilizar o UUID.

SEQUENCE

As sequencias existem como um objeto independente no banco de dados. Ao cria-las você pode definir algumas propriedades particulares, como número inicial e final, saltos, etc:

A partir da versão 10, ficou mais fácil acompanhar a vida da

A view pg_sequences é nova no PostgreSQL 10 e ajuda bastante a verificar o status da sua sequencia. Note que o valor da coluna last_value, reflete aqui o último valor gerado.

Algumas aplicações utilizam a função nextval exatamente da forma como mostrada e só depois realizam um INSERT em uma tabela. Mas assim como no caso do UUID, você pode fazer isso de forma mais direta:

Você pode também ter uma segunda tabela utilizando a mesma sequência:

Muitas pessoas preferem pegar o próximo número da sequência antes do INSERT por um simples motivo: elas vão utilizar esta numeração em outros lugares depois. Da forma que fizemos, para saber qual foi o número gerado pela nossa sequência ‘s’, temos que fazer um SELECT depois do INSERT. é claro que existe um jeito mais prático de fazer isso, utilizando a cláusula RETURNING existente nos comandos de DML INSERT, UPDATE e DELETE:

Vejam que o seu INSERT assume um comportamento parecido com um SELECT, uma vez que ele retorna um valor para você.

Você também pode colocar o valor da sua sequência como valor padrão para a sua coluna na tabela, assim como fizemos com o UUID:

Um detalhe extra sobre a sequência é que ela pode ter um “dono”. Você pode definir que um determinado campo de uma tabela seja o dono desta sequência. A única vantagem de fazer isso é que se em algum momento a sua coluna (ou a tabela inteira, claro) for excluída, a sequência será excluída junto:

De fato, estas 3 operações são muito comuns:

  • Criar uma sequência
  • Definir a sequência como valor padrão de uma coluna de uma tabela
  • Definir a sequência como pertencente à coluna desta tabela

Para facilitar a vida, o Postgres possui os tipos de dados serial, bigserial e smallserial que criam respectivamente campos do tipo integer, bigint e smallint com uma sequence associada utilizando estes 3 passos implicitamente:

 

Note que estes tipos de dados são apenas atalhos, e não tipos de dados reais. Os tipos de dados na tabela continuam sendo do tipo integer, bigint e smallint. Muitas pessoas que utilizam o serial não percebem as 3 operações criadas implicitamente e sequer imaginam que existe uma sequência sendo utilizada nos bastidores.

Colisão

O grande problema com as sequências é que embora você possa utilizar uma única sequência para várias tabelas, uma chave artificial só pode utilizar valores gerados por uma única sequência. Se você resolver inserir dados literais na sua tabela, o valor da sequência não vai ser incrementado e você terá uma colisão:

Ou seja, depois de inserir os IDs 4, 5 e 6 manualmente, a sequência continuou no valor 4. Ao tentar inserir utilizando a sequencia novamente ela duplicou a chave primária e deu erro. Para concertar isso, vamos ter que alterar o valor do próximo número da sequência, utilizando a função setval:

 

Um detalhe aqui: o nome da sequência agora é seq_teste_id_seq, que é o nome gerado internamente pelo Postgres quando você utilizou o serial na criação da tabela.

Além do problema da possível colisão, existem alguns detalhes adicionais que podem complicar a vida de quem usa sequências para alimentar chaves artificiais:

  • Se você criar uma tabela com o CREATE TABLE … LIKE, a nova tabela vai apontar para a mesma sequência da tabela original e não criar uma nova;
  • Quando você remove o valor padrão que referencia uma sequência, a sequência em si não é removida da base;
  • Você tem permissões (GRANT, REVOKE) adicionais para a sequência, além das permissões na tabela;
  • Se você quiser apagar uma sequência utilizada como valor padrão de uma tabela, você precisa utilizar o DROP SEQUENCE … CASCADE;

Para mais detalhes sobre estes problemas e a comparação com o uso das novas IDENTITY COLUMNS veja o artigo do Sr. Peter Eisentraut sobre o assunto.

IDENTITY COLUMNS

O padrão SQL:2003 criou um padrão novo para lidar com sequências em chaves artificiais. O postgreSQL passou a implementar este padrão no PostgreSQL 10, embora continue suportando o uso de campos do tipo serial, mantendo a compatibilidade com o legado. No lugar da cláusula DEFAULT de uma coluna, você pode utilizar a seguinte sintaxe:

Desta forma você tem que escolher entre as opções ALWAYS ou BY DEFAULT e pode adicionar opcionalmente algumas opções para a sequência que será criada implicitamente. A diferença entre o ALWAYS e o BY DEFAULT é justamente a possibilidade de colisão durante o INSERT. Se você utilizar o BY DEFAULT, terá um comportamento mais parecido com o tipo de dados serial do Postgres, ou seja, qualquer INSERT pode escolher valores fora da sequência:

Aqui vemos que o valor padrão da coluna id da tabela é referenciado como “generated by default as identity” e não aparece o nome da sequência. Já na sequência aparece a referência “Sequence for identity column: public.teste_by_default.id”

Agora veremos que utilizando a opção ALWAYS, não é possível fazer a mesma coisa:

Agora o valor padrão da coluna aparece como “generated always as identity” e quando tentamos atribuir um valor arbitrário para a coluna id recebemos um erro. Mas logo abaixo vem uma dica de como podemos burlar isso, usando o OVERRIDING SYSTEM VALUE.

OVERRIDING VALUE

Existem duas opções para esta cláusula OVERRIDING SYSTEM VALUE e OVERRRIDING USER VALUE:

Ou seja, você pode sim utilizar um valor arbitrário seu, mas se o fizer, tem que tomar cuidado com o que está fazendo! O padrão SQL exige que você coloque a cláusula OVERRIDING SYSTEM VALUE, como um alerta para que você tenha certeza do que está fazendo.

Existe também a cláusula OVERRIDING USER VALUE, que pode ser utilizada numa situação onde você quer ignorar os valores arbitrários e utilizar apenas os valores da sequencia:

Veja que toda a sequência da tabela teste_by_default2 ignora os valores que vieram da tabela teste_by_default ou mesmo os inseridos com valores arbitrários.

Um detalhe: você só pode utilizar esta opção em colunas definidas com o GENERATED BY DEFAULT, em colunas GENERATED ALWAYS você obtém o seguinte erro:

Por último, você tem a opção de passar algumas opções para a sequência que será gerada implicitamente:

Você não pode especificar o nome da sequencia que será criada. Seja utilizando o serial ou o IDENTITY, o nome da sequência sempre terá o formato <nome_da_tabela>_<nome da coluna>_seq.

 

Bom, recomendo que você faça alguns testes com chaves artificiais e se já estiver utilizando o PostgreSQL 10, passe a adotar o IDENTITY COLUMN ao invés do serial. Além deste formato ser mais robusto e apresentar menos problemas, ele também é padrão SQL, o que ajuda na portabilidade do seu código. Qualquer dúvida, deixe um comentário aqui.

Continuação…

Publiquei outro artigo com ênfase no desempenho de chaves artificiais utilizando sequencias e UUID, confira!

One Comment

Leave a Reply