Secuencias sin espacios PostgreSQL

Me estoy moviendo de MySql a Postgres, y me di cuenta de que cuando eliminas filas de MySql, los identificadores únicos para esas filas se vuelven a usar cuando creas nuevas. Con Postgres, si crea filas y las elimina, los identificadores únicos no se volverán a utilizar.

¿Hay alguna razón para este comportamiento en Postgres? ¿Puedo hacerlo actuar más como MySql en este caso?

Las secuencias tienen espacios para permitir inserciones concurrentes. Intentar evitar huecos o volver a usar ID eliminados crea problemas de rendimiento horribles. Vea las preguntas frecuentes de la wiki de PostgreSQL .

Los SEQUENCE s de PostgreSQL se usan para asignar identificadores. Estos solo se incrementan y están exentos de las reglas habituales de reversión de transacciones para permitir que múltiples transacciones capturen nuevas ID al mismo tiempo. Esto significa que si una transacción se revierte, esas identificaciones se “descartarán”; no hay una lista de ID “gratis” guardadas, solo el contador de ID actual. Las secuencias también suelen incrementarse si la base de datos se cierra imprudentemente.

Las claves sintéticas (ID) no tienen sentido de todos modos. Su orden no es significativo, su única propiedad de importancia es la singularidad. No se puede medir de manera significativa qué tan “distantes” están dos ID, ni se puede decir de manera significativa si uno es mayor o menor que otro. Todo lo que puedes hacer es decir “igual” o “no igual”. Cualquier otra cosa es inseguro. No deberías preocuparte por las lagunas.

Si necesita una secuencia sin intervalo que reutilice identificadores eliminados, puede tener uno, solo tiene que renunciar a una gran cantidad de rendimiento, en particular, no puede tener ninguna concurrencia en INSERT s, porque tiene que escanee la tabla para obtener la ID libre más baja, bloquee la tabla para escribir, de modo que ninguna otra transacción pueda reclamar la misma ID. Intenta buscar “secuencia sin salida postgresql”.

El enfoque más simple es usar una tabla contraria y una función que obtenga la siguiente identificación. Aquí hay una versión generalizada que usa una tabla contraria para generar identificaciones gapless consecutivas; sin embargo, no reutiliza los ID.

 CREATE TABLE thetable_id_counter ( last_id integer not null ); INSERT INTO thetable_id_counter VALUES (0); CREATE OR REPLACE FUNCTION get_next_id(countertable regclass, countercolumn text) RETURNS integer AS $$ DECLARE next_value integer; BEGIN EXECUTE format('UPDATE %s SET %I = %I + 1 RETURNING %I', countertable, countercolumn, countercolumn, countercolumn) INTO next_value; RETURN next_value; END; $$ LANGUAGE plpgsql; COMMENT ON get_next_id(countername regclass) IS 'Increment and return value from integer column $2 in table $1'; 

Uso:

 INSERT INTO dummy(id, blah) VALUES ( get_next_id('thetable_id_counter','last_id'), 42 ); 

Tenga en cuenta que cuando una transacción abierta ha obtenido una ID, todas las demás transacciones que intentan llamar a get_next_id se bloquearán hasta que la primera transacción se get_next_id o se retrotraiga. Esto es inevitable y para identificaciones gapless y es por diseño.

Si desea almacenar varios contadores para diferentes propósitos en una tabla, solo agregue un parámetro a la función anterior, agregue una columna a la tabla de contador y agregue una cláusula WHERE a la UPDATE que coincida con el parámetro a la columna agregada. De esta forma puede tener múltiples filas de contador bloqueadas independientemente. No solo agregue columnas adicionales para contadores nuevos.

Esta función no reutiliza los ID eliminados, solo evita la introducción de espacios vacíos.

Para volver a usar los IDs, les aconsejo … que no vuelvan a usar los ID.

Si realmente debe hacerlo, puede hacerlo agregando un desencadenador ON INSERT OR UPDATE OR DELETE en la tabla de interés que agrega identificadores eliminados a una tabla lateral de lista libre, y los elimina de la tabla de lista libre cuando están INSERT ed. Trate una UPDATE como un DELETE seguido de un INSERT . Ahora modifique la función de generación de ID arriba para que haga un SELECT free_id INTO next_value FROM free_ids FOR UPDATE LIMIT 1 y si lo encuentra, DELETE esa fila. IF NOT FOUND obtiene una nueva ID de la tabla del generador de forma normal. Aquí hay una extensión no probada de la función anterior para admitir la reutilización:

 CREATE OR REPLACE FUNCTION get_next_id_reuse(countertable regclass, countercolumn text, freelisttable regclass, freelistcolumn text) RETURNS integer AS $$ DECLARE next_value integer; BEGIN EXECUTE format('SELECT %I FROM %s FOR UPDATE LIMIT 1', freelistcolumn, freelisttable) INTO next_value; IF next_value IS NOT NULL THEN EXECUTE format('DELETE FROM %s WHERE %I = %L', freelisttable, freelistcolumn, next_value); ELSE EXECUTE format('UPDATE %s SET %I = %I + 1 RETURNING %I', countertable, countercolumn, countercolumn, countercolumn) INTO next_value; END IF; RETURN next_value; END; $$ LANGUAGE plpgsql;