Re: pg_dump sequence problem

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