Comment réinitialiser la séquence de clés primaires de postgres lorsqu'elle n'est plus synchronisée ?

Question

J'ai rencontré le problème suivant : la séquence de ma clé primaire n'est pas synchronisée avec les lignes de ma table.

En d'autres termes, lorsque j'insère une nouvelle ligne, j'obtiens une erreur de clé dupliquée car la séquence impliquée dans le type de données série renvoie un numéro qui existe déjà.

Il semble être causé par l'importation/restauration qui ne maintient pas la séquence correctement.

Answer 1

-- 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;

Source - Ruby Forum

Answer 2

pg_get_serial_sequence peut être utilisé pour éviter toute supposition incorrecte sur le nom de la séquence. Cela permet de réinitialiser la séquence en une seule fois :

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

Ou de manière plus concise :

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

Cependant, ce formulaire ne peut pas gérer correctement les tables vides, puisque max(id) est nul, et vous ne pouvez pas non plus définir la valeur 0, car elle serait hors de portée de la séquence. Une solution de contournement consiste à recourir à la fonction ALTER SEQUENCE syntaxe, c'est-à-dire

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

Mais ALTER SEQUENCE est d'une utilité limitée car le nom de la séquence et la valeur de redémarrage ne peuvent pas être des expressions.

Il semble que la meilleure solution polyvalente soit d'appeler setval avec false comme 3ème paramètre, ce qui nous permet de spécifier la "prochaine valeur à utiliser" :

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

C'est tout ce que je recherche :

1. évite de coder en dur le nom réel de la séquence
2. gère correctement les tables vides
3. gère les tables avec des données existantes, et ne laisse pas un trou dans la séquence

Enfin, notez que pg_get_serial_sequence ne fonctionne que si la séquence est possédée par la colonne. Ce sera le cas si la colonne d'incrémentation a été définie en tant que serial Cependant, si la séquence a été ajoutée manuellement, il est nécessaire de s'assurer que ALTER SEQUENCE .. OWNED BY est également effectuée.

c'est-à-dire que si serial a été utilisé pour la création de la table, tout devrait fonctionner :

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;

Mais si les séquences ont été ajoutées manuellement :

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;

Answer 3

Le site le plus court et le plus rapide manière :

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

tbl_id étant le serial colonne du tableau tbl en tirant de la séquence tbl_tbl_id_seq (qui est le nom automatique par défaut).

Si vous ne connaissez pas le nom de la séquence jointe (qui ne doit pas nécessairement être sous forme par défaut), utilisez pg_get_serial_sequence() :

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

Il n'y a pas d'erreur ponctuelle ici. Le manuel :

La forme à deux paramètres permet de définir la séquence last_value à la valeur valeur spécifiée et définit son is_called à true, ce qui signifie que le suivant nextval fera avancer la séquence avant de renvoyer une valeur.

C'est moi qui souligne en gras.

Si le tableau peut être vide et pour commencer à partir de 1 dans ce cas :

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

Nous ne pouvons pas simplement utiliser la forme à 2 paramètres et commencer par 0 car la borne inférieure des séquences est 1 par défaut (sauf si elle est personnalisée).

Concurrence

Pour se défendre contre l'activité simultanée des séquences ou des écritures dans la table dans les requêtes ci-dessus, verrouiller la table sur SHARE mode. Il empêche les transactions concurrentes d'écrire un nombre plus élevé (ou quoi que ce soit).

Afin de prendre en compte les clients qui peuvent avoir récupéré des numéros de séquence à l'avance sans aucun verrou sur la table principale (ce qui peut se produire dans certaines configurations), seuls les éléments suivants sont pris en compte augmentation de la valeur actuelle de la séquence, sans jamais la diminuer. Cela peut sembler paranoïaque, mais c'est en accord avec la nature des séquences et la défense contre les problèmes de concurrence.

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 est assez fort pour cet objectif. Le manuel :

Ce mode protège une table contre les changements de données simultanés.

Il entre en conflit avec ROW EXCLUSIVE mode.

Les commandes UPDATE , DELETE et INSERT acquérir ce mode de verrouillage sur la table cible

Answer 4

Cela réinitialisera toutes les séquences à partir du public, sans présumer des noms de tables ou de colonnes. Testé sur la version 8.4

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) 
    RETURNS "pg_catalog"."void" AS 

    $body$  
      DECLARE 
      BEGIN 

      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || 
          ') FROM ' || tablename || ')' || '+1)';

      END;  

    $body$  LANGUAGE 'plpgsql';

SELECT table_name || '_' || column_name || '_seq', 
    reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') 
FROM information_schema.columns where column_default like 'nextval%';

Answer 5

ALTER SEQUENCE nom_séquence RESTART WITH (SELECT max(id) FROM nom_table) ; Ça ne marche pas.

Copié de la réponse de @tardate :

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