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

Encore un autre plpgsql - réinitialise seulement si max(att) > then lastval

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

commentant également la ligne --execute format('alter sequence donnera la liste, sans réinitialiser la valeur.

Answer 2

Réinitialiser toutes les séquences du public

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

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';

Answer 3

Ce problème se produit lorsque j'utilise le cadre d'entité pour créer la base de données et ensuite ensemencer la base de données avec les données initiales, ce qui fait que la séquence ne correspond pas.

Je l'ai résolu en créant un script à exécuter après l'ensemencement de la base de données :

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$

Answer 4

Je suppose que la situation était vraiment mauvaise à l'époque où cette question a été posée, puisque beaucoup de réponses données ici ne fonctionnent pas pour la version 9.3. Le site documentation depuis la version 8.0 apporte une réponse à cette question :

SELECT setval('serial', max(id)) FROM distributors;

De même, si vous devez prendre en charge les noms de séquence sensibles à la casse, c'est ainsi que vous devez procéder :

SELECT setval('"Serial"', max(id)) FROM distributors;

Answer 5

Ma version utilise la première, avec quelques vérifications d'erreurs...

BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
 PERFORM 1
 FROM information_schema.sequences
 WHERE
  sequence_schema = _table_schema AND
  sequence_name = _sequence_name;
 IF FOUND THEN
  EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
 ELSE
  RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
 END IF;
END; 
$BODY$
 LANGUAGE 'plpgsql';

SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;