Someone posted a dilemma to the pgsql-sql list today that involved many if not all of his sequences getting out of sync with their respective "serial" columns. In other words, something like "SELECT max(id) FROM sometable" yields 42, but the sequence nextval for sometable.id is currently set to 36. This is obviously bad (for reasons left as an exercise for the reader). So besides trying to figure out how the database ended up in this state, he needed a script to reset all of his sequences to the correct next value. I had run into a similar need not too long ago. Namely, when setting up multi-master replication with Bucardo you need your sequences to draw different values on either master so as not to conflict. One solution is to set up all your sequences to jump by 2, and use even numbers on one master and odd numbers on the other. Again, a script makes this easier to deal with, and I had developed one for this situation. So I modified it for the problem mentioned above. Both versions follow:
-- create "odd" and "even" sequences in multi-master scenario CREATE OR REPLACE FUNCTION adjust_seqs(namespace text, starteven bool) RETURNS text AS $$ DECLARE rec record; startval bigint; sql text; fqname text; BEGIN FOR rec in EXECUTE 'select relname from pg_class where relkind = ''S'' and relnamespace = (select oid from pg_namespace where nspname=''' || namespace || ''')' LOOP fqname := namespace || '.' || rec.relname; IF starteven THEN EXECUTE 'SELECT ((last_value / 2) * 2) + 2 from ' || fqname INTO startval; ELSE EXECUTE 'SELECT ((last_value / 2) * 2) + 1 from ' || fqname INTO startval; END If; sql := 'ALTER SEQUENCE ' || fqname || ' INCREMENT BY 2 RESTART WITH ' || startval; EXECUTE sql; RAISE NOTICE '%', sql; END LOOP; RETURN 'OK'; END; $$ LANGUAGE plpgsql STRICT; SELECT adjust_seqs('public', true); -- in master1 (even) SELECT adjust_seqs('public', false); -- in master2 (odd)
-- update sequences that have gotten out-of-sync with the -- PK field for which they normally provide the default CREATE OR REPLACE FUNCTION adjust_seqs(namespace text) RETURNS text AS $$ DECLARE rec record; startval bigint; sql text; seqname text; BEGIN FOR rec in EXECUTE 'select table_name, column_name, column_default from information_schema.columns where table_schema = ''' || namespace || ''' and column_default like ''nextval%''' LOOP seqname := pg_get_serial_sequence(rec.table_name, rec.column_name); sql := 'select max(' || rec.column_name || ') + 1 from ' || rec.table_name; EXECUTE sql INTO startval; IF startval IS NOT NULL THEN sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' || startval; EXECUTE sql; RAISE NOTICE '%', sql; END IF; END LOOP; RETURN 'OK'; END; $$ LANGUAGE plpgsql STRICT; select adjust_seqs('public');
Neither of these is heavily tested, and both make certain assumptions, so please test and modify to suit your own needs. Caveat emptor!