Cómo restablecer el primario de postgres key secuencia cuando se desincroniza?

Por fin después de mucho batallar hemos dado con la solución de este conflicto que algunos los lectores de nuestro espacio han presentado. Si tienes algo que aportar no dejes de aportar tu conocimiento.

Solución:

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Fuente – Ruby Foro

pg_get_serial_sequence se puede utilizar para evitar suposiciones incorrectas sobre el nombre de la secuencia. Esto restablece la secuencia de una sola vez:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

O más concisamente:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

Sin embargo, este formulario no puede manejar tablas vacías correctamente, ya que max(id) es null, y tampoco puede establecer el valor 0 porque estaría fuera del rango de la secuencia. Una solución para esto es recurrir a la ALTER SEQUENCE sintaxis, es decir

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

Pero ALTER SEQUENCE tiene un uso limitado porque el nombre de la secuencia y el valor de reinicio no pueden ser expresiones.

Parece que la mejor solución para todo uso es llamar setval con false como tercer parámetro, permitiéndonos especificar el “siguiente valor a usar”:

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

Esto marca todas mis casillas:

1. evita codificar el nombre de la secuencia real
2. maneja las mesas vacías correctamente
3. maneja tablas con datos existentes y no deja un hueco en la secuencia

Finalmente, tenga en cuenta que pg_get_serial_sequence solo funciona si la secuencia es propiedad de la columna. Este será el caso si la columna incremental se definió como un serial sin embargo, si la secuencia se agregó manualmente, es necesario asegurarse ALTER SEQUENCE .. OWNED BY también se realiza.

es decir, si serial type se usó para la creación de tablas, todo esto debería funcionar:

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);

SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

Pero si las secuencias se agregaron manualmente:

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);

CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);

ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence

SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

los más corto y más rápido manera:

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id siendo el serial columna de tabla tbla partir de la secuencia tbl_tbl_id_seq (que es el nombre automático predeterminado).

Si no conoce el nombre de la secuencia adjunta (que no tiene que estar en forma predeterminada), use pg_get_serial_sequence():

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

No hay ningún error de apagado por uno aquí. El manual:

La forma de dos parámetros establece la secuencia last_value campo al valor especificado y establece su is_called campo a truelo que significa que el
próximo nextval avanzará la secuencia antes de devolver un valor.

Énfasis en negrita mío.

Si la mesa puede estar vacia y para comenzar realmente desde 1 en este caso:

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
            , COALESCE(max(tbl_id) + 1, 1)
            , false)
FROM tbl;

No podemos simplemente usar la forma de 2 parámetros y comenzar con 0 porque el límite inferior de las sucesiones es 1 por defecto (a menos que se personalice).

concurrencia

Para defenderse contra la actividad de secuencia simultánea o escrituras en la tabla en las consultas anteriores, bloquear la mesa en SHARE modo. Evita que las transacciones concurrentes escriban un número más alto (o cualquier cosa).

Para tener en cuenta también a los clientes que pueden haber obtenido números de secuencia por adelantado sin ningún bloqueo en la tabla principal (puede ocurrir en ciertas configuraciones), solo aumento el valor actual de la secuencia, nunca lo reduzca. Puede parecer paranoico, pero está de acuerdo con la naturaleza de las secuencias y la defensa contra problemas de concurrencia.

BEGIN;

LOCK TABLE tbl IN SHARE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number

COMMIT;

SHARE el modo es lo suficientemente fuerte para el propósito. El manual:

Este modo protege una tabla contra cambios de datos simultáneos.

entra en conflicto con ROW EXCLUSIVE modo.

los comandos UPDATE, DELETEy INSERT adquirir este modo de bloqueo en la tabla de destino

Tienes la posibilidad recomendar esta división si te fue de ayuda.

---

---

Utiliza Nuestro Buscador