Re: PG 8.1, dump/restore not consistent for sequence names

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, April 07, 2009 4:58 PM
To: Parks, Aaron B.
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] PG 8.1, dump/restore not consistent for sequence
names

"Parks, Aaron B." <aparks(at)rti(dot)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