Thread: update sequence conversion script

I am in the process of converting an existing database to PostGreSQL
and wrote a generic script to update all of the sequences as they default at 1.
I thought it would be useful to other people who are converting their
databases.

If anyone can write this script in using plpythonu, I would love to
see how it is done.

create or replace function UpdateSequences() returns varchar(50) as
$$
declare
        seqrecord record;
        tblname varchar(50);
        fieldname varchar(50);
        maxrecord record;
        maxvalue integer;
begin
        for seqrecord in select relname from pg_statio_user_sequences Loop
                tblname:=split_part(seqrecord.relname,'_',1);
                fieldname:=split_part(seqrecord.relname,'_',2);
                for maxrecord in execute 'select max(' || fieldname || ') as f1 from ' || tblname LOOP
                        maxvalue:=maxrecord.f1;
                end loop;
                execute 'alter sequence ' || seqrecord.relname || ' restart with ' || coalesce(maxvalue,0)+1 ;
        End LOOP;
        return 1;
end
$$
 language plpgsql

Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

Sim Zacks wrote:
> I am in the process of converting an existing database to PostGreSQL
> and wrote a generic script to update all of the sequences as they default at 1.
> I thought it would be useful to other people who are converting their
> databases.

Very nice.

> create or replace function UpdateSequences() returns varchar(50) as
> $$

For those that are puzzled, 8.0 allows you to use "dollar quoting" so
you can avoid \'\'\' in your plpgsql functions.

> declare
>         seqrecord record;
>         tblname varchar(50);
>         fieldname varchar(50);
>         maxrecord record;
>         maxvalue integer;
> begin
>         for seqrecord in select relname from pg_statio_user_sequences Loop
>                 tblname:=split_part(seqrecord.relname,'_',1);
>                 fieldname:=split_part(seqrecord.relname,'_',2);
>                 for maxrecord in execute 'select max(' || fieldname || ') as f1 from ' || tblname LOOP
>                         maxvalue:=maxrecord.f1;
>                 end loop;
>                 execute 'alter sequence ' || seqrecord.relname || ' restart with ' || coalesce(maxvalue,0)+1 ;

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.

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.

--
   Richard Huxton
   Archonet Ltd

Question:

When one moves from version 7.x to 8.x, will my old pgplsql functions continue
to work with the single quotes or will everything have to be changed to the
"dollar quoting" functionality?

Thanks...

On Monday 11 October 2004 05:28 am, Richard Huxton saith:
>
> For those that are puzzled, 8.0 allows you to use "dollar quoting" so
> you can avoid \'\'\' in your plpgsql functions.
>
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
Quote: 10
"The abandonment of original understanding in modern times means the
 transportation into the Constitution of the principles of a liberal
 culture that cannot achieve those results democratically."

 --Judge Robert Bork

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Terry Lee Tucker wrote:
> When one moves from version 7.x to 8.x, will my old pgplsql functions
> continue to work with the single quotes

Of course.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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/