Re: update sequence conversion script

On Mon, Oct 11, 2004 at 10:28:22AM +0100, Richard Huxton wrote:

> One thing you might want to test is what happens when you manually
> create a sequence separate from a table, i.e. no such table-name exists.

Instead of querying pg_statio_user_sequences, you could get the
sequences from pg_attrdef if you want to update only sequences that
are used in a DEFAULT expression. I'd also improve on the original
by joining against pg_class and pg_attribute to get the actual table
and column names instead of parsing them from the sequence name,
which might yield bogus results if a table or column has been
renamed. Here's an attempt at the query I'd make:

SELECT n.nspname,
c.relname,
a.attname,
SUBSTRING(d.adsrc FROM 'nextval\\(''([^'')]+)''') AS seqname
FROM pg_attrdef AS d
JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
JOIN pg_class AS c ON c.oid = d.adrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE adsrc LIKE 'nextval(''%'
ORDER BY seqname;

This query should return all sequences used in a DEFAULT expression,
whether implicitly via a SERIAL type or via an explicit nextval().
It should also return the correct schema, table, and column names.

> Also, you can have more than one table relying on a single sequence (and
> I have in one of my systems). Not sure there's anything useful you can
> do in such a case, or how you'd detect such a situation.

The above query should return all tables and columns that reference
the sequence. You could get the MAX of all of them by building a
UNION query:

SELECT COALESCE(MAX(MAX), 0) AS maxall FROM (
SELECT MAX(fooid) FROM foo
UNION
SELECT MAX(barid) FROM bar
) AS s;

Building such a query would be easy in Perl or Python. The OP said
he'd like to see a plpythonu implementation so maybe I'll whip one
up if I get time. I'd be inclined to just write an ordinary Python
script instead of a stored procedure, however, so it could be used
on systems that didn't have plpythonu.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/