|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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:
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
SELECT COALESCE(MAX(MAX), 0) AS maxall FROM (
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.
|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|