Hoe de primaire sleutelreeks van postgres resetten wanneer deze niet meer synchroon loopt?

Ik kwam het probleem tegen dat mijn primaire sleutelreeks niet synchroon loopt met mijn tabelrijen.

Dat wil zeggen, wanneer ik een nieuwe rij invoeg, krijg ik een dubbele sleutelfout omdat de reeks geïmpliceerd in het seriële datatype een getal retourneert dat al bestaat.

Het lijkt te worden veroorzaakt door import/restores die de volgorde niet goed onderhouden.

Antwoord 1, autoriteit 100%

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

Bron – Ruby-forum

Antwoord 2, autoriteit 30%

pg_get_serial_sequencekan gebruikt om onjuiste veronderstellingen over de naam van de reeks te voorkomen. Dit reset de reeks in één keer:

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

Of beknopter:

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

Dit formulier kan echter niet correct met lege tabellen omgaan, aangezien max(id) null is, en u kunt ook geen waarde 0 instellen omdat dit buiten het bereik van de reeks zou zijn. Een oplossing hiervoor is om gebruik te maken van de syntaxis ALTER SEQUENCE, d.w.z.

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

Maar ALTER SEQUENCEis van beperkt nut omdat de reeksnaam en herstartwaarde geen uitdrukkingen kunnen zijn.

Het lijkt erop dat de beste universele oplossing is om setvalaan te roepen met false als de derde parameter, zodat we de “volgende waarde die moet worden gebruikt” kunnen specificeren:

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

Dit vinkt al mijn vakjes aan:

1. vermijdt het hard coderen van de eigenlijke sequentienaam
2. gaat correct om met lege tabellen
3. verwerkt tabellen met bestaande gegevens en laat geen
   gat in de reeks

Houd er ten slotte rekening mee dat pg_get_serial_sequencealleen werkt als de reeks eigendom is van de kolom. Dit is het geval als de oplopende kolom is gedefinieerd als een serial-type, maar als de reeks handmatig is toegevoegd, moet ervoor worden gezorgd dat ALTER SEQUENCE .. OWNED BYook is uitgevoerd.

d.w.z. als het type serialwerd gebruikt voor het maken van tabellen, zou dit allemaal moeten werken:

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;

Maar als reeksen handmatig zijn toegevoegd:

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;

Antwoord 3, autoriteit 19%

De kortste en snelstemanier:

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

tbl_idzijnde de serialkolom van tabel tbl, gebaseerd op de reeks tbl_tbl_id_seq(wat de standaard automatische naam is).

Alsu de naam van de bijgevoegde reeks niet weet (die niet in standaardvorm hoeft te zijn), gebruik dan pg_get_serial_sequence():

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

Er is hier geen fout-by-one fout. De handleiding:

Het formulier met twee parameters stelt het veld last_valuevan de reeks in op de
opgegeven waarde en stelt het veld is_calledin op true, wat betekent dat de
next nextvalgaat de reeks vooruitvoordat een waarde wordt geretourneerd.

Vette nadruk van mij.

Alsde tabel leeg mag zijn enom in dit geval vanaf 1 te beginnen:

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

We kunnen niet zomaar de 2-parametervorm gebruiken en beginnen met 0omdat de ondergrens van reeksen standaard 1is ( tenzij aangepast).

Gelijktijdigheid

Om u te beschermen tegen gelijktijdige reeksactiviteit of schrijven naar de tabel in de bovenstaande zoekopdrachten, vergrendelt u de tabelin de modus SHARE. Het voorkomt dat gelijktijdige transacties een hoger nummer schrijven (of wat dan ook).

Om ook rekening te houden met klanten die mogelijk van tevoren volgnummers hebben opgehaald zonder enige vergrendeling op de hoofdtafel, maar (kan gebeuren in bepaalde instellingen), verhoogalleen de huidige waarde van de reeks, verminder het nooit. Het lijkt misschien paranoïde, maar dat is in overeenstemming met de aard van sequenties en verdediging tegen gelijktijdigheidsproblemen.

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;

De modus

SHAREis hiervoor sterk genoeg. De handleiding:

Deze modus beschermt een tabel tegen gelijktijdige gegevenswijzigingen.

Het is in strijd met de modus ROW EXCLUSIVE.

De commando’s UPDATE, DELETEen INSERTverkrijgen deze vergrendelingsmodus op de doeltabel

Antwoord 4, autoriteit 8%

Hiermee worden alle reeksen van openbaar opnieuw ingesteld, waarbij geen aannames worden gedaan over tabel- of kolomnamen. Getest op versie 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%';

Antwoord 5

Ik stel voor deze oplossing te vinden op postgres wiki. Het werkt alle reeksen van uw tabellen bij.

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Hoe te gebruiken(van postgres wiki):

• Sla dit op in een bestand, zeg ‘reset.sql’
• Voer het bestand uit en sla de uitvoer op op een manier die niet de gebruikelijke koppen bevat, en voer vervolgens die uitvoer uit. Voorbeeld:

Voorbeeld:

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

Origineel artikel (ook met oplossing voor eigendom van sequenties) hier

Antwoord 6

Nog een plpgsql – reset alleen als 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;
$$
;

ook commentaar geven op de regel --execute format('alter sequencegeeft de lijst, niet echt de waarde resetten

Antwoord 7

Herstel alle reeksen van openbaar

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

Antwoord 8

Dit probleem doet zich voor bij mij wanneer ik het entiteitsframework gebruik om de database te maken en de database vervolgens te seeden met initiële gegevens, waardoor de reeks niet overeenkomt.

Ik heb het opgelost door een script te maken dat moet worden uitgevoerd nadat de database is geseed:

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$

Antwoord 9

Enkele echt hardcore antwoorden hier, ik neem aan dat het vroeger erg slecht was rond de tijd dat dit werd gevraagd, aangezien veel antwoorden van hier niet werken voor versie 9.3. De documentatiesinds versie 8.0 geeft een antwoord op deze vraag:

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

Als u hoofdlettergevoelige reeksnamen moet regelen, doet u dat ook zo:

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

Antwoord 10

Mijn versie gebruikt de eerste, met wat foutcontrole…

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;

11

Probeer reindex .

Update: Zoals opgemerkt in de opmerkingen, was dit een antwoord op de oorspronkelijke vraag.

Antwoord 12

Er zijn hier veel goede antwoorden. Ik had dezelfde behoefte na het herladen van mijn Django-database.

Maar ik had nodig:

• Alles in één functie
• Kan een of meer schema’s tegelijk repareren
• Kan alle of slechts één tafel tegelijk repareren
• Ik wilde ook een leuke manier om precies te zien wat er wel of niet was veranderd

Dit lijkt erg op de behoefte waar de oorspronkelijke vraag om was.
Dankzij Baldiry en Mauro ben ik op het goede spoor gezet.

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;

Vervolgens Uitvoeren en zien hoe de wijzigingen worden uitgevoerd:

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

Retourneren

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