Skip site navigation (1) Skip section navigation (2)

Is a SERIAL column a "black box", or not?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Is a SERIAL column a "black box", or not?
Date: 2006-04-29 21:54:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
In some recent activity on the patches list about responding to bug #2073,
we've been discussing various possible tweaks to the behavior of dropping
or modifying a serial column.  The hacks involved with SERIAL seem to me
to be getting uglier and uglier, and I think it's time to take a step
back and consider what we really want SERIAL to act like.

It seems to me there are two basic philosophies at war here:

1. A serial column is a "black box" that you're not supposed to muck with
the innards of.  This philosophy leads to the proposal that we disallow
modifying the column default expression of a serial column, and will
ultimately lead to thoughts like trying to hide the associated sequence
from direct access at all.

2. A serial declaration is just a "macro" for setting up a sequence and a
column default expression.  This was the original viewpoint and indeed is
still what it says in the documentation:
This is nice and simple and easy to understand, but it leads to
usually-undesirable behaviors like having the sequence still be there if
the column is dropped.

We started with #2 and have been moving slowly towards #1, but I think
there's a limit to how far we want to go in that direction.  A black box
approach isn't especially user-friendly in my opinion; it's not solving
any problems, it's just refusing to deal with the implications of ALTER
TABLE and friends.  What's more, the further we go in that direction the
more problems we'll have in pg_dump.  We've already got issues there;
for example, if someone renames a serial's sequence or tweaks its
sequence parameters, this will not be preserved by dump/restore.

I'm wondering if we shouldn't reverse this trend and try to go back to
a fairly pure version of philosophy #2.  It'd certainly make pg_dump's
life a lot easier if it could dump a serial sequence as just an ordinary
sequence, instead of having to make sure it's created via SERIAL.

One original argument for putting in a hidden dependency centered around
the fact that if you dropped the sequence, you'd break the column
default.  But we have a much better answer to that as of PG 8.1: the
nextval() invocation is itself dependent on the sequence by means of the
regclass-literal mechanism.  We don't need the extra dependency to prevent

The other concern the hidden dependency addresses is the idea that the
sequence ought to be silently dropped if the table (or just the column)
is dropped.  I wonder though if that behavior is really worth the
increasing contortions we're going through to try to make things work
conveniently/transparently in other respects.  We're buying simplicity
for one case at the cost of tremendous complication for other cases.

In short, I think there's a reasonably good case to be made for losing the
hidden dependency and re-adopting the viewpoint that saying SERIAL is
*exactly* the same as making a sequence and then making a default
expression that uses the sequence.  Nothing behind the curtain.

Comments, other opinions?

			regards, tom lane


pgsql-hackers by date

Next:From: markDate: 2006-04-29 23:41:15
Subject: Re: Is a SERIAL column a "black box", or not?
Previous:From: Magnus HaganderDate: 2006-04-29 20:14:04
Subject: Re: Exports from postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group