Обсуждение: PG 8.1, dump/restore not consistent for sequence names
I have PG SQL 8.1 running on a RHEL 4 system (64 bit).
I have numerous tables, all of which were created with
CREATE TABLE someTable (myID SERIAL, someField varchar . . .etc
Postgres created numerous sequences for me and it all seemed to work great. Elsewhere I have lots of java code that gets the values of the sequences out like:
"SELECT last_value FROM simulation_probability_of_occ_simulation_probability_of_occ_seq"
Which also seems to work great.
Now I did a dump/restore to send it to a client and I have the following problem:
In the first DB (the one that works) I have this in the table def for the table (from PGAdmin III):
simulation_probability_of_occurence_id integer NOT NULL DEFAULT nextval('simulation_probability_of_occ_simulation_probability_of_oc_seq4'::regclass),
And in the restored DB I have this:
simulation_probability_of_occurence_id integer NOT NULL DEFAULT nextval('simulation_probability_of_occ_simulation_probability_of_oc_seq3'::regclass),
This is wreaking all kinds of havoc with my Java code. I’ve tried exporting with and without OIDs (from PGADMIN III) with no apparent change.
Is there something I can do to keep those sequences in the right places?
Thanks
Aaron Parks
"Parks, Aaron B." <aparks@rti.org> writes:
> Is there something I can do to keep those sequences in the right places?
Update to something newer than 8.1 :-(. This is one of the reasons that
we changed pg_dump in 8.2 to not depend on the SERIAL shorthand anymore.
Unfortunately that required related changes in both pg_dump and the
server, so it wasn't practical to back-port.
Pre-8.2, the only reliable solution I can think of is to shorten the
table and/or column names so that the server doesn't have to resort to
tweaking the sequence names for uniqueness.
If you don't like that, you could also think about changing your client
code to not depend on the generated names of the sequences in the first
place --- use constructs like nextval(pg_get_serial_sequence(...)).
I don't think there's a one-query substitute for "SELECT lastval" in
that paradigm, but quite honestly an app that's depending on lastval is
probably broken anyway because of race conditions. Why aren't you using
nextval/currval?
regards, tom lane
Tom:
Thanks for the info. I will look into upgrading.
I used lastval mainly because I found that documented on the inet
somewhere and it seemed to work for me. If curval is better I'll switch
to that.
Thanks
Aaron Parks
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, April 07, 2009 4:58 PM
To: Parks, Aaron B.
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PG 8.1, dump/restore not consistent for sequence
names
"Parks, Aaron B." <aparks@rti.org> writes:
> Is there something I can do to keep those sequences in the right
places?
Update to something newer than 8.1 :-(. This is one of the reasons that
we changed pg_dump in 8.2 to not depend on the SERIAL shorthand anymore.
Unfortunately that required related changes in both pg_dump and the
server, so it wasn't practical to back-port.
Pre-8.2, the only reliable solution I can think of is to shorten the
table and/or column names so that the server doesn't have to resort to
tweaking the sequence names for uniqueness.
If you don't like that, you could also think about changing your client
code to not depend on the generated names of the sequences in the first
place --- use constructs like nextval(pg_get_serial_sequence(...)).
I don't think there's a one-query substitute for "SELECT lastval" in
that paradigm, but quite honestly an app that's depending on lastval is
probably broken anyway because of race conditions. Why aren't you using
nextval/currval?
regards, tom lane