Fixing Sequences

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m (correction for the case where sequences have upper case characters in their names.)
m (Added support for schemas. Tested on 9.3)
Line 7: Line 7:
  
 
<source lang="sql">
 
<source lang="sql">
SELECT  'SELECT SETVAL(' ||quote_literal(quote_ident(S.relname))|| ', MAX(' ||quote_ident(C.attname)|| ') ) FROM ' ||quote_ident(T.relname)|| ';'
+
SELECT  'SELECT SETVAL(' ||quote_literal(quote_ident(PGT.schemaname)|| '.'||quote_ident(S.relname))|| ', MAX(' ||quote_ident(C.attname)|| ') ) 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
+
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'
 
WHERE S.relkind = 'S'
 
     AND S.oid = D.objid
 
     AND S.oid = D.objid
Line 14: Line 14:
 
     AND D.refobjid = C.attrelid
 
     AND D.refobjid = C.attrelid
 
     AND D.refobjsubid = C.attnum
 
     AND D.refobjsubid = C.attnum
 +
    AND T.relname = PGT.tablename
 
ORDER BY S.relname;
 
ORDER BY S.relname;
 
</source>
 
</source>
Line 28: Line 29:
  
 
There are a few limitations to this snippet of code you need to be aware of:
 
There are a few limitations to this snippet of code you need to be aware of:
* It might not work well with multiple schemas
 
 
* It only works on sequences that are owned by a table. If your sequences are not owned, run the following script first:
 
* It only works on sequences that are owned by a table. If your sequences are not owned, run the following script first:
  

Revision as of 19:00, 5 March 2014

Administrative Snippets

Fixing Sequences

Works with PostgreSQL

8.4+

Written in

SQL

Depends on

Nothing


Updating sequence values from table

A common problem when copying or recreating a database is that database sequences are not updated just by inserting records in the table that sequence is used in. If you want to make your sequences all start just after whatever values are already there, it's possible to do that for most common configurations like this:

SELECT  'SELECT SETVAL(' ||quote_literal(quote_ident(PGT.schemaname)|| '.'||quote_ident(S.relname))|| ', MAX(' ||quote_ident(C.attname)|| ') ) 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;

Usage would typically work like this:

  • Save this to a file, say 'reset.sql'
  • Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example:
psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

There are a few limitations to this snippet of code you need to be aware of:

  • It only works on sequences that are owned by a table. If your sequences are not owned, run the following script first:

Fixing sequence ownership

This script changes sequences with OWNED BY to the table and column they're referenced from. NB! Sequences that are referenced by multiple tables or columns are ignored.

(Parts of query shamelessly stolen from OmniTI's Tasty Treats repository by Robert Treat)

SELECT 'ALTER SEQUENCE '|| quote_ident(min(schema_name)) ||'.'|| quote_ident(min(seq_name))
       ||' OWNED BY '|| quote_ident(min(table_name)) ||'.'|| quote_ident(min(column_name)) ||';'
FROM (
    SELECT 
        n.nspname AS schema_name,
        c.relname AS table_name,
        a.attname AS column_name,
        substring(d.adsrc FROM E'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)') AS seq_name 
    FROM pg_class c 
    JOIN pg_attribute a ON (c.oid=a.attrelid) 
    JOIN pg_attrdef d ON (a.attrelid=d.adrelid AND a.attnum=d.adnum) 
    JOIN pg_namespace n ON (c.relnamespace=n.oid)
    WHERE has_schema_privilege(n.oid,'USAGE')
      AND n.nspname NOT LIKE 'pg!_%' escape '!'
      AND has_table_privilege(c.oid,'SELECT')
      AND (NOT a.attisdropped)
      AND d.adsrc ~ '^nextval'
) seq
GROUP BY seq_name HAVING count(*)=1;

This snippet finds orphaned sequences that aren't owned by any column. It can be helpful to run this, to double-check that the above query did its job right.

SELECT ns.nspname AS schema_name, seq.relname AS seq_name
FROM pg_class AS seq
JOIN pg_namespace ns ON (seq.relnamespace=ns.oid)
WHERE seq.relkind = 'S'
  AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=seq.oid AND deptype='a')
ORDER BY seq.relname;
Personal tools