Clever Geek Handbook

Antipadrões PostgreSQL: IDs únicos

Freqüentemente, um desenvolvedor precisa gerar alguns identificadores únicos para os registros da tabela PostgreSQL - tanto ao inserir registros quanto ao lê-los.





Mesa de balcão



Parece - o que é mais fácil? Montamos uma placa separada, nele - uma entrada com um contador. Precisamos obter um novo identificador - leia a partir daí para escrever um novo valor - faça isso UPDATE...



Não faça isso ! Porque amanhã você terá que resolver problemas:



  • travas sobrepostas persistentes ao UPDATE

    ver os antipadrões do PostgreSQL: lutando contra hordas de "mortos"
  • degradação gradual da velocidade de acesso aos dados da tabela do contador,

    consulte Antipadrões PostgreSQL: atualizando uma grande tabela sob carga
  • ... e a necessidade de limpar com transações ativas que vão incomodar ,

    veja DBA: quando o VACUUM passa, limpamos a tabela manualmente


Objeto SEQUENCE



Para tais tarefas, o PostgreSQL fornece uma entidade separada - SEQUENCE. Não é transacional, ou seja, não causa travamentos , mas duas transações "paralelas" certamente receberão valores diferentes .



Para obter o próximo ID de uma sequência, basta usar a função nextval: 



SELECT nextval('seq_name'::regclass);


Às vezes, você precisa obter vários IDs de uma vez - para gravação de streaming via COPY, por exemplo. Usar para isso setval(currval() + N)é fundamentalmente errado ! Pela simples razão de que entre as chamadas para as funções "inner" ( currval) e "outer" ( setval), uma transação simultânea pode alterar o valor atual da sequência. A maneira correta é ligar para o nextvalnúmero necessário de vezes: 



SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);


Pseudo serial



Não é muito conveniente trabalhar com sequências no modo "manual". Mas nossa tarefa típica é garantir a inserção de um novo registro com um novo ID de sequência! Especialmente para esse propósito, é inventado o PostgreSQL serial, que, ao gerar uma tabela, "se expande" para algo parecido . Não é necessário lembrar o nome da sequência gerada automaticamente vinculada ao campo, existe uma função para isso . A mesma função pode ser usada em suas próprias substituições - por exemplo, se houver necessidade de fazer uma sequência comum para várias tabelas ao mesmo tempo. No entanto, como o trabalho com a sequência é não transacional, se o identificador dele foi recebido por uma transação revertida, então nos registros da tabela salva o ID da sequência será "vazado"id integer NOT NULL DEFAULT nextval('tbl_id_seq')



pg_get_serial_sequence(table_name, column_name)DEFAULT



...



Colunas GERADAS



A partir do PostgreSQL 10 , é possível declarar uma coluna de identidade ( GENERATED AS IDENTITY) em conformidade com o padrão SQL: 2003. Na variante, o GENERATED BY DEFAULTcomportamento é equivalente serial, mas com GENERATED ALWAYStudo mais interessante: 



CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);


INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.


Sim, a fim de inserir um valor específico "em" tal coluna, você precisa fazer um trabalho extra com OVERRIDING SYSTEM VALUE: 



INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .


Observe que agora temos dois valores idênticos na tabela id = 1- isto é, GENERATED não impõe condições e índices UNIQUE adicionais , mas é puramente uma declaração, também serial.



Em geral, nas versões modernas do PostgreSQL, o uso de serial está obsoleto, sendo preferível substituir GENERATED. Exceto, talvez, a situação de suporte para aplicativos de versão cruzada trabalhando com PGs abaixo de 10.



UUID gerado



Tudo está bem contanto que você trabalhe em uma instância de banco de dados. Mas quando há vários deles, não há uma maneira adequada de sincronizar as sequências (no entanto, isso não impede que você sincronize-as “inadequadamente” , se você realmente quiser). Aqui, o tipo UUIDe as funções para gerar valores para ele vêm ao resgate . Eu geralmente o uso uuid_generate_v4()como o mais "casual".



Campos ocultos do sistema



tableoid / ctid



Às vezes, ao obter registros de uma tabela, você precisa de alguma forma endereçar um registro "físico" específico ou descobrir de qual seção particular um registro específico foi obtido ao acessar a tabela "pai" usando herança .



Nesse caso, os campos ocultos do sistema presentes em cada registro nos ajudarão a :



  • tableoidarmazena o oid-id da tabela - isto é, tableoid::regclass::textfornece o nome de uma seção de tabela específica
  • ctid - endereço "físico" do registro no formato (<>,<>)


Por exemplo, ctidele pode ser usado para operações com uma tabela sem uma chave primária , mas tableoidpara a implementação de certos tipos de chaves estrangeiras.



oid



-Se a 11 PostgreSQL foi possível declarar ao criar a tabela de atributos WITH OIDS: 



CREATE TABLE tbl(id serial) WITH OIDS;


Cada entrada nesta tabela obtém um campo oculto adicional oidcom um valor globalmente exclusivo dentro do banco de dados - como foi organizado para tabelas do sistema como pg_class, pg_namespace...



Quando você insere um registro em uma tabela, o valor gerado é retornado imediatamente ao resultado da consulta: 



INSERT INTO tbl(id) VALUES(DEFAULT);


  :   OID 16400   11 .


Esse campo é invisível para uma consulta de tabela "normal": 



SELECT * FROM tbl;


id
--
 1


Ele, como outros campos do sistema, deve ser solicitado explicitamente: 



SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;


tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1


Verdade, o valor oidé de apenas 32 bits , por isso é muito fácil obter um estouro, depois do qual oidnem será possível criar nenhuma tabela (precisa de uma nova !). Portanto, desde PostgreSQL 12, ele WITH OIDSnão é mais suportado .



Tempo "justo" clock_timestamp



Às vezes, quando uma consulta ou procedimento está em execução por um longo tempo, você deseja vincular o tempo "atual" ao registro. A falha aguarda qualquer um que tente usar a função para fazer isso now()- ela retornará o mesmo valor durante toda a transação .



Para obter o tempo "agora", há uma função clock_timestamp()(e outro grupo de seus irmãos). A diferença no comportamento dessas funções pode ser vista no exemplo de uma consulta simples: 



SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);


              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03


More articles:


All Articles