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

I have read this thread and I agree with Magnus that we have both new
and experienced users, and we need something simple like SERIAL for new
users.

I agree that having SERIAL be a macro is probably less that useless ---
you can create SERIAL easily, but to remove a table you have to
understand the underlying system, so we actually add complexity by
having SERIAL as a macro --- if we did that, we might as well just
remove SERIAL if all it does is expand to DEFAULT nextval().

My idea is to create a new SECURITY DEFINER function called
serial_nextval(), and use that for SERIAL defaults. That will fix the
sequence permission issue Magnus mentioned, and better document for new
users what the DEFAULT does (it is related to SERIAL). It might also
help us flag cases where we should be modifying things during ALTER.

---------------------------------------------------------------------------

Magnus Hagander wrote:
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +