Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump sequence problem

pg_dump sequence problem

From
Q Beukes
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hey,

Is this a bug or a feature (PG 8.1.3)?

If have a two schemas called: cbt and core.
I have a sequence: core.invoicesids_seq.
I have a couple of tables in cbt having columns with defaults:
nextval('core.invoicesids_seq')

When I dump the database, the tables dumped for "cbt" dont have alter
commands to set the default values to
"nextval('core.invoicesids_seq')" again. Those columns are simply
created as serial fields, and their values set to "1, false".

So when I restore the database, it is not what it was, which makes
restoring backups quite an effort.


regards
Q Beukes
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQEVAwUBRNM+lLEDZDQ16UzTAQIBIggAv3XxXa2HZ4ZU0i0Zu738r4567cgk5trr
/ZGLCdXOSY2wvOtSDtsAVD/rMZXwPEsPfy4M2u0inllr0Uq2uQ1pA4/+fohtqPq5
XPCv5G3wLFcOJR7NpjKAjRC5sl+1/xesskPf174W64RC+iZJJr/Y5GSFffUvkcQY
hTpEC/GhENXEgnMovZTlOyXu+b/VCQt0gndpbGPObP1+XYAbN8QZYwe29MmKxMLK
aIhL/7yV/vfddozjdWVaQzj0RH4ZuZ4JwbGP5iqGohhACrUCuy26qJJOAH1gYXh5
vH3JlLZ3mRyF/0GDNWNISjOzGFIVcrQSwNO0o6SRPyd+m0Og2oC+8Q==
=4eC2
-----END PGP SIGNATURE-----

Re: pg_dump sequence problem

From
"Nikolay Samokhvalov"
Date:
On 8/4/06, Q Beukes <pgsql-general@list.za.net> wrote:
[...]
> When I dump the database, the tables dumped for "cbt" dont have alter
> commands to set the default values to
> "nextval('core.invoicesids_seq')" again. Those columns are simply
> created as serial fields, and their values set to "1, false".
>
> So when I restore the database, it is not what it was, which makes
> restoring backups quite an effort.

We've (in my company) encountered with this issue several times, this
is quite painful. After all, we decided to get rid of SERIAL at all.

The thing is that SERIAL is a kind of macros now. When columns has
default expression "nextval('some_seq')", pg_dump     erroneously thinks
that this column has SERIAL type.

Another 'way to catch the troubles' is as following: you create serial
column, than make "\d your_table", see that there is "INT4 DEFAULT
nextval('...')" there and make a conclusion that you may adjust that
DEFAULT expr... E.g., "nextval(...) * 7^9 % 9^7" - Knuth shuffle
algorithm, useful for hidding real order number of the row. That is
bad way to! pg_dump will make SERIAL for you and you will lose your
nice expression and some hair :-)

You can find many discussions concerning SERIAL gotchas in mail
archives. (Including my trials to prove to community that there are
real gotchas and difficulties for novices, but... There is no strong
opinion on what is SERIAL at all.)

My suggestions are:
 - do not use SERIAL at all. Always create sequence manually and then
write DEFAULT expr.
 - when DEFAULT expr is simple nextval('...') you should make fool
from pg_dump - write "DEFAULT nextval('...') + 0" - that dummy "+ 0"
will prevent pg_dump from making conclusion that this is SERIAL...

--
Best regards,
Nikolay

Re: pg_dump sequence problem

From
Tom Lane
Date:
Q Beukes <pgsql-general@list.za.net> writes:
> Is this a bug or a feature (PG 8.1.3)?

> If have a two schemas called: cbt and core.
> I have a sequence: core.invoicesids_seq.
> I have a couple of tables in cbt having columns with defaults:
> nextval('core.invoicesids_seq')

> When I dump the database, the tables dumped for "cbt" dont have alter
> commands to set the default values to
> "nextval('core.invoicesids_seq')" again. Those columns are simply
> created as serial fields, and their values set to "1, false".

Let me guess: those columns were originally defined as "serial"s,
and then you hand-modified their default expressions to reference
a different sequence?

pg_dump can still see that they're supposed to be serials (there's
still a dependency to their original sequence in pg_depend), and
so it dumps them that way without noticing that you've messed with
the default.

There are several schools of thought on what to do about this.  One says
that a serial column is a black box and you shouldn't be allowed to
change its default.  Another thinks that we should try to get rid of the
magic behaviors of serials, rather than add more.  And some have
proposed just trying to move the dependency from the column itself to
the default expression, which might or might not make everything work
nicely.  It's not been resolved yet, but in the meantime I counsel not
messing with the default of a serial column.

To get out of your immediate problem you could delete the rows in
pg_depend that link those columns to their original sequences.  Look for
rows with objid = the table's OID, objsubid = the column's number, and
refobjid = the original sequence's OID.  With those gone, pg_dump should
go back to dumping the columns as regular columns.

            regards, tom lane

Re: [NOVICE] pg_dump sequence problem

From
Tom Lane
Date:
"Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:
> My suggestions are:
>  - do not use SERIAL at all. Always create sequence manually and then
> write DEFAULT expr.

Not an unreasonable suggestion.

>  - when DEFAULT expr is simple nextval('...') you should make fool
> from pg_dump - write "DEFAULT nextval('...') + 0" - that dummy "+ 0"
> will prevent pg_dump from making conclusion that this is SERIAL...

This is completely silly, however.  pg_dump does not pay any attention
to the contents of the default when determining if something is a SERIAL.
(One could argue that that's exactly the problem ...)

            regards, tom lane