PostgreSQL Sequence – How to Truncate Table on Cascade and Reset Hierarchic Sequences

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;

Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!

First, the real fix would be for the code to always use the sequence instead of the inconsistent mix of select 1+max(pk) and nextval('seqname').

That being said, as a band-aid solution before a real fix, you could have a trigger on INSERT for each row that always calls nextval on the sequence, so that you're sure that the sequence never lags behind, even when the INSERT itself is missing a nextval call.

If the column has a DEFAULT nextval('seqname') (either set manually or through a SERIAL declaration), and a trigger calls nextval in addition to the nextval of the DEFAULT, that will just advance the sequence by two instead of one. That should not be a problem for your app because sequences can have holes anyway, due to rollback or caching.

Calling setval with SELECT 1+max(pk) from table also works, but only when there aren't other transactions using the sequence concurrently. So doing that in a trigger doesn't sound like a good idea. Typically this sort of adjustement is only ever done after a bulk load (in fact, that's what pg_dump produces when the sequence is owned by the table).