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

Tout mettre en place

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

fixera ' id' de la table donnée (comme cela est généralement nécessaire avec django par exemple).

Answer 2

Revérifier toutes les séquences dans la fonction schéma public

CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
  _table_name VARCHAR;
  _column_name VARCHAR;  
  _sequence_name VARCHAR;
BEGIN
  FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
    FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
        SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
        IF _sequence_name IS NOT NULL THEN 
            EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
        END IF;
    END LOOP;   
  END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Answer 3

Avant je n'avais pas encore essayé le code : dans le suivant je poste la version du code sql pour les deux solutions de Klaus et user457226 qui ont fonctionné sur mon ordinateur [Postgres 8.3], avec juste quelques petits ajustements pour celle de Klaus et de ma version pour celle de user457226.

Solution Klaus :

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      constraint_column_usage.table_name as tablename,
      constraint_column_usage.table_name as tablename, 
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

user457226 solution :

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
  DECLARE seqname character varying;
          c integer;
  BEGIN
    select tablename || '_' || columnname || '_seq' into seqname;
    EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
    if c is null then c = 0; end if;
    c = c+1; --because of substitution of setval with "alter sequence"
    --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
    EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
    RETURN nextval(seqname)-1;
  END;
$body$ LANGUAGE 'plpgsql';

select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
       reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname || '_seq'
where sequence_schema='public';

Answer 4

Cette réponse est une copie de mauro.

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null 
      ORDER BY sequencename
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

Answer 5

Je peux dire qu'il n'y a pas assez d'opinions ou de roues réinventées dans ce fil, alors j'ai décidé de pimenter les choses.

Vous trouverez ci-dessous une procédure qui :

• se concentre (n'affecte) que les séquences qui sont associées aux tables
• fonctionne à la fois pour les colonnes SERIAL et GENERATED AS IDENTITY
• fonctionne pour les noms good_column_names et les noms "BAD_column_123".
• affecte automatiquement la valeur de départ définie des séquences respectives si le tableau est vide.
• permet d'affecter uniquement une séquence spécifique (en notation schema.table.column)

• dispose d'un mode de prévisualisation

  CREATE OR REPLACE PROCEDURE pg_reset_all_table_sequences( IN commit_mode BOOLEAN DEFAULT FALSE , IN mask_in TEXT DEFAULT NULL ) AS $$ DECLARE sql_reset TEXT; each_sec RECORD; new_val TEXT; BEGIN

  sql_reset := $sql$ SELECT setval(pg_get_serial_sequence('%1$s.%2$s', '%3$s'), coalesce(max("%3$s"), %4$s), false) FROM %1$s.%2$s; $sql$ ;

  FOR each_sec IN (

  SELECT
      quote_ident(table_schema) as table_schema
  ,   quote_ident(table_name) as table_name
  ,   column_name
  ,   coalesce(identity_start::INT, seqstart) as min_val
  FROM information_schema.columns
  JOIN pg_sequence ON seqrelid = pg_get_serial_sequence(quote_ident(table_schema)||'.'||quote_ident(table_name) , column_name)::regclass
  WHERE
      (is_identity::boolean OR column_default LIKE 'nextval%') -- catches both SERIAL and IDENTITY sequences
  
  -- mask on column address (schema.table.column) if supplied
  AND coalesce( table_schema||'.'||table_name||'.'||column_name = mask_in, TRUE )

  ) LOOP

  IF commit_mode THEN EXECUTE format(sql_reset, each_sec.table_schema, each_sec.table_name, each_sec.column_name, each_sec.min_val) INTO new_val; RAISE INFO 'Resetting sequence for: %.% (%) to %' , each_sec.table_schema , each_sec.table_name , each_sec.column_name , new_val ; ELSE RAISE INFO 'Sequence found for resetting: %.% (%)' , each_sec.table_schema , each_sec.table_name , each_sec.column_name ; END IF ;

  END LOOP;

  END $$ LANGUAGE plpgsql ;

pour la prévisualisation :

call pg_reset_all_table_sequences();

de s'engager :

call pg_reset_all_table_sequences(true);

pour spécifier uniquement votre table cible :

call pg_reset_all_table_sequences('schema.table.column');