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

J'ai passé une heure à essayer de faire fonctionner la réponse de djsnowsill avec une base de données utilisant des tables et des colonnes en casse mixte, puis je suis finalement tombé sur la solution grâce à un commentaire de Manuel Darveau, mais j'ai pensé que je pourrais la rendre un peu plus claire pour tout le monde :

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
        (SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
END;
$body$  LANGUAGE 'plpgsql';

SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
FROM information_schema.columns WHERE column_default like 'nextval%';

Cela présente l'avantage de :

• ne pas supposer que la colonne ID s'écrit d'une façon particulière.
• sans supposer que toutes les tables ont une séquence.
• fonctionne pour les noms de table/colonne en casse mixte.
• en utilisant le format pour être plus concis.

Pour expliquer, le problème était que pg_get_serial_sequence prend des ficelles pour comprendre à quoi vous faites référence, donc si vous le faites :

"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!

Pour ce faire, on utilise ''%1$I'' dans la chaîne de format, '' fait une apostrophe 1$ signifie premier arg, et I signifie entre guillemets

Answer 2

Une méthode pour mettre à jour toutes les séquences dans votre schéma qui sont utilisées comme un ID :

DO $$ DECLARE
  r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
          FROM pg_catalog.pg_tables
          WHERE schemaname='YOUR_SCHEMA'
          AND tablename IN (SELECT table_name 
                            FROM information_schema.columns 
                            WHERE table_name=tablename and column_name='id')
          order by tablename)
LOOP
EXECUTE
        'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
         FROM ' || r.tablename || ';';
END LOOP;
END $$;

Answer 3

Un hack moche pour le réparer en utilisant la magie de l'interpréteur de commandes, pas une grande solution mais qui pourrait inspirer d'autres personnes ayant des problèmes similaires :)

pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -

Answer 4

Il y a beaucoup de bonnes réponses ici. J'ai eu le même besoin après avoir rechargé ma base de données Django.

Mais j'en avais besoin :

• Fonction tout en un
• Possibilité de corriger un ou plusieurs schémas à la fois
• On peut réparer toutes les tables ou seulement une à la fois.
• Je voulais aussi un moyen agréable de voir exactement ce qui avait changé, ou pas changé.

Cela semble très similaire à ce que la demande initiale était pour.
Merci à Baldiry et Mauro de m'avoir mis sur la bonne voie.

drop function IF EXISTS reset_sequences(text[], text) RESTRICT;
CREATE OR REPLACE FUNCTION reset_sequences(
    in_schema_name_list text[] = '{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}',
    in_table_name text = '%') RETURNS text[] as
$body$
  DECLARE changed_seqs text[];
  DECLARE sequence_defs RECORD; c integer ;
  BEGIN
    FOR sequence_defs IN
        select
          DISTINCT(ccu.table_name) as table_name,
          ccu.column_name as column_name,
          replace(replace(c.column_default,'''::regclass)',''),'nextval(''','') as sequence_name
          from information_schema.constraint_column_usage ccu,
               information_schema.columns c
          where ccu.table_schema = ANY(in_schema_name_list)
            and ccu.table_schema = c.table_schema
            AND c.table_name = ccu.table_name
            and c.table_name like in_table_name
            AND ccu.column_name = c.column_name
            AND c.column_default is not null
          ORDER BY sequence_name
   LOOP
      EXECUTE 'select max(' || sequence_defs.column_name || ') from ' || sequence_defs.table_name INTO c;
      IF c is null THEN c = 1; else c = c + 1; END IF;
      EXECUTE 'alter sequence ' || sequence_defs.sequence_name || ' restart  with ' || c;
      changed_seqs = array_append(changed_seqs, 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c);
   END LOOP;
   changed_seqs = array_append(changed_seqs, 'Done');

   RETURN changed_seqs;
END
$body$ LANGUAGE plpgsql;

Ensuite, cliquez sur Exécuter et voyez les changements s'exécuter :

select *
from unnest(reset_sequences('{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}'));

Renvoie à

activity_id_seq                          restart at 22
api_connection_info_id_seq               restart at 4
api_user_id_seq                          restart at 1
application_contact_id_seq               restart at 20

Answer 5

Essayez réindexer .

MISE À JOUR : Comme indiqué dans les commentaires, il s'agissait d'une réponse à la question initiale.