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

On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote:

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?

I find it user-unfriendly that I must grant select/update to the
SERIAL, separate than from the table. I don't really see anything
friendly about treating the object as separate.

I do see the benefits with regard to simplified implementation, and
flexibility.

As a compromise, I could see either choice being correct. I don't
see either direction as being both user friendly and simple.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.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/

On Sat, 2006-04-29 at 17:54 -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.

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:

Comments, other opinions?

Do both. Return SERIAL to being a macro and implement the SQL IDENTITY
construct as the black box version.

CREATE TABLE foo (bar integer PRIMARY KEY GENERATED BY DEFAULT
AS IDENTITY);

INSERT ... RETURNS needs to be implemented before SERIAL can become a
black box. Until that time we will still need some knowledge of the
sequence involved.

--

Rod Taylor <pg@rbt.ca> writes:

Do both. Return SERIAL to being a macro and implement the SQL IDENTITY
construct as the black box version.

Doesn't SQL IDENTITY have a number of properties that are significantly
different from serial/nextval? I wasn't really volunteering to
implement a large new feature to make this happen ;-)

Also, I'm not sure how "black boxy" it can be without buying right back
into the pg_dump problems. pg_dump has to be able to see what's inside,
I think.

regards, tom lane

On Sat, 2006-04-29 at 23:15 -0400, Tom Lane wrote:

Rod Taylor <pg@rbt.ca> writes:

Do both. Return SERIAL to being a macro and implement the SQL IDENTITY
construct as the black box version.

Doesn't SQL IDENTITY have a number of properties that are significantly
different from serial/nextval? I wasn't really volunteering to
implement a large new feature to make this happen ;-)

Yes. Including a few really nice properties and a really ugly
workaround.

I didn't mean to imply that you should write it. I just meant that the
spec already has an automatic sequence generator which is black-box.

If SERIAL is going to be kept long term, then it should be the macro
version so it doesn't appear too duplicated.

Also, I'm not sure how "black boxy" it can be without buying right back
into the pg_dump problems. pg_dump has to be able to see what's inside,
I think.

Not sure which pg_dump problem you're referring to. A fully black box
generator would completely hide the column default and sequence. Pg_dump
and users can create and modify foreign keys without knowledge of the
trigger implementation, the same would be true here.

For the spec, the ugly workaround is "OVERRIDING SYSTEM VALUE" which
allows a table owner to override the ALWAYS GENERATE designation --
essentially the same as a trigger bypass switch for bulk data loads.

--

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

Tom Lane wrote:

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.

I speak more as a user than a hacker, but I do still lurk here ;)

The way sequences are handled is imho one of the strongest features. The
possiblity to query nextval is bordering on divine.

I have however stopped using serials for anything else than quick mockup
examples. The work of defining the sequence itself and setting acl's is
imho trivial compared to consistency.

I would actually suggest throwing a warning, that sequences are the
proper way of doing it when people use serials - maybe even mark
serial-types as obsolete in the docs.

I strongly subscribe to the principle of least astonishment, and that
means either pure sequences, a mysqlesqe auto_increment or both - but I
fail to see, how the "macro"thing serial will ever work that way. It
goes without saying, that I dislike auto_increment.

Svenne

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:

Since a real stumbling block with the macro approach seems to be the
granting of permissions maybe we should work on that problem. For
example, making SERIAL be a macro that expands to:

id integer default nextval(sequence) SECURITY DEFINER,

Which would mean that the default expression would be executed as the
creator of the table, thus obviating the need to grant explicit
permission to the sequence.

If you wanted to be tricky you could also add something like:

ON DROP CASCADE SEQUENCE sequence

This pretty much turns default expressions into actual objects. I don't
know if we want to do that. That would imply creating a CREATE DEFAULT
command, which is probably going too far (though it would be nice and
easy for pg_dump).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Rod Taylor wrote:

If SERIAL is going to be kept long term, then it should be the macro
version so it doesn't appear too duplicated.

I concur with this. But to really break out from the current middle ground, you must
implement the IDENTITY and also document the SERIAL macro as deprecated.

Regards,
Thomas Hallgren

Ühel kenal päeval, L, 2006-04-29 kell 19:41, kirjutas
mark@mark.mielke.cc:

On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote:

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?

I find it user-unfriendly that I must grant select/update to the
SERIAL, separate than from the table. I don't really see anything
friendly about treating the object as separate.

just define nextval() as SECURITY DEFINER

I do see the benefits with regard to simplified implementation, and
flexibility.

As a compromise, I could see either choice being correct. I don't
see either direction as being both user friendly and simple.

You can be user friendly and simple only if the user wants to do simple
things, or if you can exactly predict what a user wants, else you have
to grant some power to the user, and that involves complexity or at
least a learning curve.

-------------
Hannu

On Sun, Apr 30, 2006 at 01:42:37PM +0300, Hannu Krosing wrote:

�hel kenal p�eval, L, 2006-04-29 kell 19:41, kirjutas
mark@mark.mielke.cc:

On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote:

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?

I find it user-unfriendly that I must grant select/update to the
SERIAL, separate than from the table. I don't really see anything
friendly about treating the object as separate.

just define nextval() as SECURITY DEFINER

If I understand correctly - I think that hides the problem, rather
than solving it. :-)

Shouldn't the SERIAL have the same permissions as the TABLE in the
general case? SECURITY DEFINER would give everybody full access?

I do see the benefits with regard to simplified implementation, and
flexibility.
As a compromise, I could see either choice being correct. I don't
see either direction as being both user friendly and simple.

You can be user friendly and simple only if the user wants to do simple
things, or if you can exactly predict what a user wants, else you have
to grant some power to the user, and that involves complexity or at
least a learning curve.

Yes.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.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/

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@mielke.cc / markm@ncf.ca / markm@nortel.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/

On Sun, Apr 30, 2006 at 12:28:50 +0200,

Since a real stumbling block with the macro approach seems to be the
granting of permissions maybe we should work on that problem. For
example, making SERIAL be a macro that expands to:

id integer default nextval(sequence) SECURITY DEFINER,

Which would mean that the default expression would be executed as the
creator of the table, thus obviating the need to grant explicit
permission to the sequence.

I suggested a long time ago that default expressions should always be
executed as the owner of the table. This got shot down, but I don't remember
if it was because people thought the idea was bad in itself or if it was
the work involved (which I wasn't in a position to do).

Tom Lane wrote:

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.

It would be madness to prevent people from accessing the associated sequence.
Assume the following schema:

CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...);
CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...);

Now, if I need to insert into both tables a and b, how do I do it? After
inserting into table a, if I can't access the sequence to get currval, I'll need
to do a select against the table to find the row that I just inserted (which
could be slow), and if the columns other than a_id do not uniquely identify a
single row, then I can't do this at all.

mark

On Sun, Apr 30, 2006 at 09:14:53AM -0700, Mark Dilger wrote:

Tom Lane wrote:

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.

It would be madness to prevent people from accessing the associated sequence.
Assume the following schema:

CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...);
CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...);

Now, if I need to insert into both tables a and b, how do I do it? After
inserting into table a, if I can't access the sequence to get currval, I'll need
to do a select against the table to find the row that I just inserted (which
could be slow), and if the columns other than a_id do not uniquely identify a
single row, then I can't do this at all.

Not madness. Just evidence of another problem, which is where the insert
that returns results comes in...

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.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 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

mark@mark.mielke.cc wrote:

On Sun, Apr 30, 2006 at 09:14:53AM -0700, Mark Dilger wrote:

Tom Lane wrote:

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.

It would be madness to prevent people from accessing the associated sequence.
Assume the following schema:

CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...);
CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...);

Now, if I need to insert into both tables a and b, how do I do it? After
inserting into table a, if I can't access the sequence to get currval, I'll need
to do a select against the table to find the row that I just inserted (which
could be slow), and if the columns other than a_id do not uniquely identify a
single row, then I can't do this at all.

Not madness. Just evidence of another problem, which is where the insert
that returns results comes in...

That might help in the above situation but seriously restricts the way in which
a user can organize their code. Personally, I don't use the currval solution
above, but rather call nextval first, cache the answer, and use it for both the
insertion in table a and in table b. If I don't get the value from the sequence
until the insertion is performed on table a, I have to structure my code for
that. Lots of people might have to rework their code to handle such a change.

Of course, you can argue that if I don't like this I should skip using SERIAL
and just explicitly use sequences. But the person coding against the schema may
not be the same person who defined it. (And yes, I stopped using SERIAL in any
schema I define a long time ago -- but I still run into it.)

mark

Bruno Wolff III <bruno@wolff.to> writes:

I suggested a long time ago that default expressions should always be
executed as the owner of the table. This got shot down, but I don't remember
if it was because people thought the idea was bad in itself or if it was
the work involved (which I wasn't in a position to do).

The more I think about it the better I like that idea. It seems like a
natural and unsurprising semantics, whereas ideas involving implicit
GRANTs seem to me to violate the principle of least surprise. It fixes
the problem for both serial and handmade sequences --- indeed, it fixes
related problems for functions other than nextval(). And it doesn't
require introduction of any new syntax.

One argument against it is that it'd break trying to log who-did-what
by the expedient of having a column default CURRENT_USER:
blame_me text default current_user
You could still make use of session_user for this, but that's not really
the right thing if the INSERT is being done from a security-definer
function. I don't find this objection very compelling, because such a
default is pretty fragile anyway: it could be broken just by assigning
explicitly to the column. You'd be better off doing the logging by
having a BEFORE trigger that sets the column value. However, I suspect
that the SQL spec demands that such a default behave as it currently
does, which means that changing this would violate spec.

A cheesy compromise would be to switch userid for default-evaluation
only if the expression contains any volatile functions. I find this
idea pretty ugly, but it would allow us to still behave per-spec
for CURRENT_USER while getting the results we want for nextval().
(current_user() is marked "stable".)

regards, tom lane

On Mon, May 01, 2006 at 10:29:12AM -0400, Tom Lane wrote:

A cheesy compromise would be to switch userid for default-evaluation
only if the expression contains any volatile functions. I find this
idea pretty ugly, but it would allow us to still behave per-spec
for CURRENT_USER while getting the results we want for nextval().
(current_user() is marked "stable".)

If the user is specifying the default expression, they can specify
SECURITY DEFINER themselves, yes?

So it's really only the default definition of 'SERIAL' columns for
new tables. SERIAL isn't per-spec, yes? So it could change in 8.2
without problem?

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.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/

mark@mark.mielke.cc writes:

If the user is specifying the default expression, they can specify
SECURITY DEFINER themselves, yes?

Not unless they write a wrapper function to be a security definer
and call nextval().

regards, tom lane