Re: pg_dump sequence problem

From: "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com>
To: "Q Beukes" <pgsql-general(at)list(dot)za(dot)net>
Cc: "Postgresql General" <pgsql-general(at)postgresql(dot)org>, "Postgresql Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: pg_dump sequence problem
Date: 2006-08-04 13:59:12
Message-ID: e431ff4c0608040659x40eecadfrb936e49454919ec8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

On 8/4/06, Q Beukes <pgsql-general(at)list(dot)za(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rohit Prakash Khare 2006-08-04 14:04:31 Problem connecting to local host
Previous Message Wayne Conrad 2006-08-04 13:36:03 Re: PITR Questions

Browse pgsql-novice by date

  From Date Subject
Next Message Garcia, Joshua 2006-08-04 14:55:00 Re: The old pl/pgsql editor question back again
Previous Message Q Beukes 2006-08-04 12:33:24 pg_dump sequence problem