Re: Is a SERIAL column a "black box", or not? - Mailing list pgsql-hackers

> 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.

I think it's a matter of user-friendliness for *who*. A black box would
definitly be a lot more user-friendly for a beginner, or someone who
really doesn't care for more than just an auto-numbering column (which
I'm sure is a lot of cases).

For example, I've lost count of the number of times I've had to explain
to people "yes, I know you just created a table with a column, but when
you need to GRANT permissions you need to do it twice - once for the
column and once for the sequence you didn't know you created". I don't
recall any of these cases ending with "hey, what a handy feature that I
can tweak the sequence independently".

For an expert user it's certainly handy, though.


> 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.

If it was a "proper black box", that wouldn't happen, since there would
be no way to make those changes, right? So that argument would really be
helped in either direction, with the problem mainly showing in the
"middle ground" where we are now.


> 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.

I bet loads of databases would be filled with no-longer-used sequences
in this case. But that may not really be a problem, since they don't
exactly occupy loads of space when they just sit there...


> 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.

That certainly does have the merit of being very predictable behaviour -
which is good.

Another note is that it's definitly going to make it harder for people
coming in from other databases, that have IDENTITY or AUTO_NUMBER or
whatever the feature is called there. They're likely to go even more
"what?!" than now...

If it's not obvious yet :-P, I'd be in favour of having SERIAL as
black-box as possible, and then just use manual CREATE SEQUENCE and
DEFAULT nextval() for when you need a more advanced case. But that's as
seen from a user perspective, without regard for backend complexity.

//Magnus