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

I strongly agree with #2. The case at hand is where someone wants
a serial column with different defaults (wraparound, min, max) than
the standard serial. To achieve this an alter sequence is all that
is necessary. If it were not possible to do this so simply, then
the user would have to do #2 by hand. This is not hard for experienced
users but leaves out the middle group--just past beginners.

In general using our own tools to implement things such as sequences
for serials and rules for views is a postgres strength.

The dependencies seem to bear a closer look though. A drop table
cascade should probably drop the sequence. I think a link between a
sequence and a column is necessary. But it should be independent
of names, etc. I'm not sure how we mark those dependencies now.

Also permissions needs a closer look from the discussion that follows.
I don't have strong opinions on that issue.

--elein

On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote:
> In some recent activity on the patches list about responding to bug #2073,
> http://archives.postgresql.org/pgsql-bugs/2005-11/msg00303.php
> 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:
> http://developer.postgresql.org/docs/postgres/datatype-numeric.html#DATATYPE-SERIAL
> 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
> that.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>