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

> 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

On Sun, Apr 30, 2006 at 11:06:05AM +0200, Magnus Hagander wrote:
> 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.

That's where I sit as well.

SERIAL as a macro has no value to me. I'd rather write it out in full,
and make it obvious to the caller, what I'm doing. This way, I get to
choose the sequence name instead of having it generated for me, and
the GRANT expression makes more sense.

If SERIAL generated an 'anonymous' SEQUENCE, that was a real black
box, that had the same permissions as the table, I'd be tempted to use
it again.

I also see the db_dump example as proving more that the box isn't
black enough, than proving that the black box approach is wrong.

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> My idea is to create a new SECURITY DEFINER function called
> serial_nextval(), and use that for SERIAL defaults.

You haven't thought about this at all. Who will own that function?
Surely we don't want to create a new one for every SERIAL column.
And even if we did, what magic will cause its ownership to change
when the table's owner is changed?

I'm leaning towards the idea that we need special syntax, along the
lines of
DEFAULT nextval('some_seq') AS OWNER
which would result in generating a special expression node type at
the time the DEFAULT expression is inserted into a query plan (and
no earlier). At runtime this node would temporarily switch
current_user, just as we do for SECURITY_DEFINER functions --- but by
postponing the determination of which user to switch to until the plan
is built, we avoid trouble with ALTER TABLE OWNER.

Per Bruno's earlier comments, we probably need the same feature for
table CHECK constraints. Might be interesting to think about it for
domain check constraints too, though that's getting a bit far afield
unless someone has a convincing use-case.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > My idea is to create a new SECURITY DEFINER function called
> > serial_nextval(), and use that for SERIAL defaults.
>
> You haven't thought about this at all. Who will own that function?
> Surely we don't want to create a new one for every SERIAL column.
> And even if we did, what magic will cause its ownership to change
> when the table's owner is changed?

It would have to be a function that somehow grabbed the table owner and
internally did the permission checks based on that, but since CHECK
needs something similar, I think AS OWNER is probably best. Does that
solve all the SERIAL "black box" problems? TODO shows these SERIAL
issues:

* %Disallow changing default expression of a SERIAL column?
* %Disallow ALTER SEQUENCE changes for SERIAL sequences because pg_dump
does not dump the changes
* %Have ALTER TABLE RENAME rename SERIAL sequence names

> I'm leaning towards the idea that we need special syntax, along the
> lines of
> DEFAULT nextval('some_seq') AS OWNER
> which would result in generating a special expression node type at
> the time the DEFAULT expression is inserted into a query plan (and
> no earlier). At runtime this node would temporarily switch
> current_user, just as we do for SECURITY_DEFINER functions --- but by
> postponing the determination of which user to switch to until the plan
> is built, we avoid trouble with ALTER TABLE OWNER.
>
> Per Bruno's earlier comments, we probably need the same feature for
> table CHECK constraints. Might be interesting to think about it for
> domain check constraints too, though that's getting a bit far afield
> unless someone has a convincing use-case.

Added to TODO:

* Add DEFAULT .. AS OWNER so permission checks are done as the table
owner

This would be useful for SERIAL nextval() calls and CHECK constraints.

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

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