Thread: Code to automatically refresh sequences after loading data?
Has anyone got any code that will automatically carry out setvals for serial
fields after loading data. I'm trying to write a function that will
identify and go through all sequences within the current database and 'fix'
the sequences to be set as the correct next value.
I cannot find any way of consistently identifiying the table/field that the
sequence has been defined over by interrogating the system catalogues.
The function so far is:
create function sys_refresh_sequences () returns integer as '
DECLARE
myfield RECORD;
tblname text;
BEGIN
FOR myseq IN SELECT * FROM pg_class WHERE relkind=''S'' ORDER BY
relname LOOP
myfield := substring(myseq.relname, 1,
char_length(myseq.relname)-4);
....
....
RAISE NOTICE ''REFRESHING SEQUENCE % ON %'', myseq.relname,
myfield;
END LOOP;
return 1;
END;
' language 'plpgsql';
Any pointers would be appreciated
Many thanks
Steve Boyle
boylesa@dial.pipex.com
On Sat, Oct 27, 2001 at 06:36:16PM +0100, steve boyle wrote:
> Has anyone got any code that will automatically carry out setvals for serial
> fields after loading data. I'm trying to write a function that will
> identify and go through all sequences within the current database and 'fix'
> the sequences to be set as the correct next value.
>
> I cannot find any way of consistently identifiying the table/field that the
> sequence has been defined over by interrogating the system catalogues.
You need to go grovelling through pg_attrdef, looking for the sequence name
in the adsrc field. Then the adrelid field gives you the oid from pg_class
of the table, and the adnum gives you the ordinal for the column that has
this default, which is in pg_attribute.attnum.
Be careful of other tricky uses of sequences: recent discussion on one of
these lists has been about isung one sequence to generate unique ids across
_multiple_ tables.
I usually keep a hand edited file around with my (also hand-edited) defining
schema, in which I have a bunch of:
SELECT setval('sequence_name_here',max(column_name)) from tablename;
Generated by a little awk/sed/grep of the schema, or from some SQL on the db.
Ah, this should be useful:
select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'") FROM "'||c.relname||'";' from pg_class c, pg_class
cs,pg_attribute a, pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid = a.attrelid and c.oid =
d.adrelidand d.adnum = a.attnum;
Here's what it does on a simple db of mine (excuse the wrap) all those
quotes are to make it MixEdCase proof.
?column?
---------------------------------------------------------------------------------------
SELECT setval('"people_peid_seq"', max("peid") FROM "people";
SELECT setval('"other_programs_prog_id_seq"', max("prog_id") FROM "other_programs";
SELECT setval('"other_courses_course_id_seq"', max("course_id") FROM "other_courses";
Ross
>
> The function so far is:
>
> create function sys_refresh_sequences () returns integer as '
> DECLARE
> myfield RECORD;
> tblname text;
> BEGIN
> FOR myseq IN SELECT * FROM pg_class WHERE relkind=''S'' ORDER BY
> relname LOOP
> myfield := substring(myseq.relname, 1,
> char_length(myseq.relname)-4);
> ....
> ....
> RAISE NOTICE ''REFRESHING SEQUENCE % ON %'', myseq.relname,
> myfield;
> END LOOP;
>
> return 1;
> END;
> ' language 'plpgsql';
>
> Any pointers would be appreciated
>
> Many thanks
>
> Steve Boyle
> boylesa@dial.pipex.com
>
>
>
> ---------------------------(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
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
On Mon, Oct 29, 2001 at 01:18:15PM -0600, Ross J. Reedstrom wrote:
Need to fix that: I was short a right paren:
select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'"))
FROM "'||c.relname||'";' from pg_class c, pg_class cs, pg_attribute a,
pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid =
a.attrelid and c.oid = d.adrelid and d.adnum = a.attnum;
now you can do (in psql):
bioinfo=# \t
Showing only tuples.
bioinfo=# select 'SELECT setval(''"'||cs.relname||
'"'', max("'||attname||'")) FROM "'||c.relname||'";'
from pg_class c, pg_class cs, pg_attribute a, pg_attrdef d
where cs.relkind = 'S' and d.adsrc ~ cs.relname
and c.oid = a.attrelid and c.oid = d.adrelid
and d.adnum = a.attnum \g fix-serial.sql
bioinfo=# \i fix-serial.sql
77
78
17
bioinfo=#
i.e. use SQL to generate the SQL into a file, then read the commands back
in from that file.
Note that since this grovels around in systemtables, it can break with version
changes.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
Ross, I've used your code and created a function that seems to do the job.
The function is shown below.
--
-- Function: sys_refresh_sequences()
-- Purpose: Refreshes all sequences in the current database after data
load.
-- Notes: Code for sequence_setvals provided by Ross J Reedstorm
-- Wrapper function added by Steve Boyle 30/10/2001
--
drop view sequence_setvals;
create view sequence_setvals as
select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'")) FROM
"'||c.relname||'";' AS expr from pg_class c, pg_class cs, pg_attribute a,
pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid =
a.attrelid and c.oid = d.adrelid and d.adnum = a.attnum;
drop function sys_refresh_sequences();
create function sys_refresh_sequences () returns integer as '
DECLARE
myseq RECORD;
BEGIN
FOR myseq IN select * from sequence_setvals LOOP
RAISE NOTICE ''Executing - %'', myseq.expr;
EXECUTE myseq.expr;
END LOOP;
return 1;
END;
' language 'plpgsql';
""Ross J. Reedstrom"" <reedstrm@rice.edu> wrote in message
news:20011029171627.B24888@rice.edu...
> On Mon, Oct 29, 2001 at 01:18:15PM -0600, Ross J. Reedstrom wrote:
>
> Need to fix that: I was short a right paren:
>
> select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'"))
> FROM "'||c.relname||'";' from pg_class c, pg_class cs, pg_attribute a,
> pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid =
> a.attrelid and c.oid = d.adrelid and d.adnum = a.attnum;
>
> now you can do (in psql):
>
> bioinfo=# \t
> Showing only tuples.
> bioinfo=# select 'SELECT setval(''"'||cs.relname||
> '"'', max("'||attname||'")) FROM "'||c.relname||'";'
> from pg_class c, pg_class cs, pg_attribute a, pg_attrdef d
> where cs.relkind = 'S' and d.adsrc ~ cs.relname
> and c.oid = a.attrelid and c.oid = d.adrelid
> and d.adnum = a.attnum \g fix-serial.sql
>
> bioinfo=# \i fix-serial.sql
> 77
>
> 78
>
> 17
>
> bioinfo=#
>
> i.e. use SQL to generate the SQL into a file, then read the commands back
> in from that file.
>
> Note that since this grovels around in systemtables, it can break with
version
> changes.
>
> Ross
>
> --
> Ross Reedstrom, Ph.D. reedstrm@rice.edu
> Executive Director phone: 713-348-6166
> Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
Howdy,
At least 2 years ago, when I created my original PostgreSQL
database, I set the postmaster to have a default time zone of 'CDT6CST5'.
And it has happily given me all of my dates formatted like that ever
since.
Last night I setup a new database server and transferred one of
my more active databases from the old server to the new. Unfortunately,
I don't remember how to set the time zone. And, due to apparent brain
damage on my part, I cannot find it in the idocs either. Can anyone give
me a hand with this? I don't like thinking in GMT, it gives me a headache
(and makes me 5-6 hours early for all of my appointments :).
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"