So, after restoring a production dump with intermediate filtering, none of our sequences were set to the right value. I could have tried to review the process of filtering the dump here, but it’s a one-shot action and you know what that sometimes mean. With some pressure you don’t script enough of it and you just crawl more and more.
Still, I think how I solved it is worthy of a blog entry. Not that it’s
about a super unusual
clever trick, quite the contrary, because questions
involving this trick are often encountered on the support
The idea is to query the catalog for all sequences, and produce from there
SQL command you will have to issue for each of them. Once you have this
query, it’s quite easy to arrange from the
psql prompt as if you had dynamic
scripting capabilities. Of course in
9.0 you will have
#> \o /tmp/sequences.sql #> \t Showing only tuples. #> YOUR QUERY HERE #> \o #> \t Tuples only is off.
Once you have the
/tmp/sequences.sql file, you can ask
psql to execute its
command as you’re used to, that’s using
\i in an explicit transaction block.
Now, the interresting part if you got here attracted by the blog entry title
is in fact the query itself. A nice way to start is to
\set ECHO_HIDDEN then
describe some table, you now have a catalog example query to work with. Then
you tweak it somehow and get this:
SELECT 'select ' || trim(trailing ')' from replace(pg_get_expr(d.adbin, d.adrelid), 'nextval', 'setval')) || ', (select max( ' || a.attname || ') from only ' || nspname || '.' || relname || '));' FROM pg_class c JOIN pg_namespace n on n.oid = c.relnamespace JOIN pg_attribute a on a.attrelid = c.oid JOIN pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum and a.atthasdef WHERE relkind = 'r' and a.attnum > 0 and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval';
Coming next, a recode script in order to get from SQL_ASCII to UTF-8, and some strange looking queries too.
recode.sh [-npdf0TI] [-U user ] -s schema [-m mintable] pattern