Re: update sequence conversion script

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Sim Zacks <sim(at)compulab(dot)co(dot)il>, pgsql-general(at)postgresql(dot)org
Subject: Re: update sequence conversion script
Date: 2004-10-15 15:40:35
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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,
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 MAX(fooid) FROM foo
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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jerry LeVan 2004-10-15 16:10:07 Any Show Stoppers for v8 libpq talking to v7.x db?
Previous Message Scott Cain 2004-10-15 15:27:08 Re: creating audit tables