PostgreSQL sequences need to be reset after subsetter is completed

[binarydev]

Hey Tonic devs,

I've been using your awesome tool for a couple of months now, and it's been so great to use! Something I noticed though is that after a subset has been generated, I need to run some SQL to reset DB sequences to their max value in the resulting table before I can generate a backup via pg_dump. Otherwise, the sequences are all reset to 1. Is this intentional on your part for some reason?

[acolombi]

Good catch! No that's not intentional, and it's definitely a bug. As a work around, are you aware of the post_subset_sql configuration setting? You can stuff you sequence fixing SQL in there to make it a little easier for you, if you aren't already doing that already.

[binarydev]

Missed this comment from last month, but yes we do something similar we just don't use this configuration setting to do so. After the subsetting is completed, we use psql to run the following SQL against the targeted DB that has the subset imported. It catches all of our sequences and generates a SQL file with a reset per sequence that we pass to a 2nd psql command for execution:

-- Outputs a SQL command for each sequence in the schema, which should all be saved to a file and then executed
-- The generated SQL will reset each sequence to the maximum ID in each table + 1
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;