Clever Geek Handbook

Antipatrones de PostgreSQL: ID únicos

Muy a menudo, un desarrollador necesita generar algunos identificadores únicos para los registros en la tabla de PostgreSQL , tanto al insertar registros como al leerlos.





Mesa de mostrador



Parecería, ¿qué es más fácil? Instalamos un plato separado, en él, una entrada con un mostrador. Necesitamos obtener un nuevo identificador - leemos desde allí para escribir un nuevo valor - lo hacemos UPDATE...



¡ No hagas eso ! Porque mañana tendrás que solucionar problemas:





Objeto SEQUENCE



Para este tipo de tareas, PostgreSQL proporciona una entidad separada - SEQUENCE. No es transaccional, es decir, no causa bloqueos , pero dos transacciones "paralelas" ciertamente recibirán valores diferentes .



Para obtener la siguiente ID de una secuencia, simplemente use la función nextval: 



SELECT nextval('seq_name'::regclass);


A veces es necesario obtener varios ID a la vez , por ejemplo, para la grabación en streaming a través de COPY. ¡Usar para esto setval(currval() + N)es fundamentalmente incorrecto ! Por la sencilla razón de que entre llamadas a las funciones "inner" ( currval) y "outside" ( setval), una transacción concurrente podría cambiar el valor actual de la secuencia. La forma correcta es llamar el nextvalnúmero requerido de veces: 



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


Pseudo serie



No es muy conveniente trabajar con secuencias en modo "manual". ¡Pero nuestra tarea típica es asegurar la inserción de un nuevo registro con un nuevo ID de secuencia! Especialmente para este propósito, se inventa PostgreSQL serial, que, al generar una tabla, se "expande" en algo parecido . No es necesario recordar el nombre de la secuencia generada automáticamente vinculada al campo, hay una función para esto . La misma función se puede utilizar en sus propias sustituciones, por ejemplo, si es necesario hacer una secuencia común para varias tablas a la vez. Sin embargo, dado que trabajar con la secuencia no es transaccional, si el identificador de ella fue recibido por una transacción revertida, entonces la secuencia de ID en los registros de la tabla guardada tendrá "fugas".id integer NOT NULL DEFAULT nextval('tbl_id_seq')



pg_get_serial_sequence(table_name, column_name)DEFAULT



...



Columnas GENERADAS



A partir de PostgreSQL 10 , es posible declarar una columna de identidad ( GENERATED AS IDENTITY) que cumpla con el estándar SQL: 2003. En la variante, el GENERATED BY DEFAULTcomportamiento es equivalente serial, pero con GENERATED ALWAYStodo más interesante: 



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.


Sí, para insertar un valor específico "en" dicha columna, tendrá que hacer un esfuerzo adicional con OVERRIDING SYSTEM VALUE: 



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


Tenga en cuenta que ahora tenemos dos valores idénticos en la tabla id = 1, es decir, GENERATED no impone condiciones e índices ÚNICOS adicionales , sino que es exclusivamente una declaración, así como serial.



En general, en las versiones modernas de PostgreSQL, el uso de serie está en desuso, con el reemplazo preferido de GENERATED. Excepto, quizás, la situación de soporte para aplicaciones de versiones cruzadas que trabajan con PG por debajo de 10.



UUID generado



Todo está bien siempre que trabaje dentro de una instancia de base de datos. Pero cuando hay varios de ellos, no existe una forma adecuada de sincronizar las secuencias (sin embargo, esto no le impide sincronizarlas “inadecuadamente” , si realmente lo desea). Aquí es donde el tipo UUIDy las funciones para generar valores vienen al rescate . Normalmente lo uso uuid_generate_v4()como el más "casual".



Campos de sistema ocultos



tableoid / ctid



A veces, cuando se obtienen registros de una tabla, es necesario abordar de alguna manera un registro "físico" específico o averiguar de qué sección en particular se obtuvo un registro en particular al acceder a la tabla "principal" usando la herencia .



En este caso, los campos del sistema ocultos presentes en cada registro nos ayudarán a :



  • tableoidalmacena el oid-id de la tabla, es decir, tableoid::regclass::textda el nombre de una sección de tabla en particular
  • ctid - dirección "física" del registro en el formato (<>,<>)


Por ejemplo, ctidse puede usar para operaciones con una tabla sin clave primaria , pero tableoidpara la implementación de ciertos tipos de claves externas.



oid



Se pudieron declarar hasta 11 PostgreSQL al crear la tabla de atributos WITH OIDS: 



CREATE TABLE tbl(id serial) WITH OIDS;


Cada entrada en esta tabla obtiene un campo oculto adicional oidcon un valor globalmente único dentro de la base de datos, como se organizó para tablas del sistema como pg_class, pg_namespace...



Cuando inserta un registro en una tabla, el valor generado se devuelve inmediatamente al resultado de la consulta: 



INSERT INTO tbl(id) VALUES(DEFAULT);


  :   OID 16400   11 .


Este campo es invisible para una consulta de tabla "normal": 



SELECT * FROM tbl;


id
--
 1


Al igual que otros campos del sistema, debe solicitarse explícitamente: 



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


Es cierto que el valor oides de solo 32 bits , por lo que es muy fácil obtener un desbordamiento, después de lo oidcual ni siquiera será posible crear ninguna tabla (¡necesita una nueva !). Por lo tanto, desde PostgreSQL 12, ya WITH OIDSno es compatible .



Hora "justa" clock_timestamp



A veces, durante una ejecución prolongada de una consulta o un procedimiento, desea vincular el tiempo "actual" al registro. La falla espera a cualquiera que intente usar la función para hacer esto now(): devolverá el mismo valor durante toda la transacción .



Para obtener el tiempo "ahora mismo", hay una función clock_timestamp()(y otro grupo de sus hermanos). La diferencia entre el comportamiento de estas funciones se puede ver en el ejemplo de una consulta simple: 



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