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

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

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(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/

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(at)rbt(dot)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(at)rbt(dot)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.

--

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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(at)mark(dot)mielke(dot)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(at)mark(dot)mielke(dot)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(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/

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

mark(at)mark(dot)mielke(dot)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(at)wolff(dot)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(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/

mark(at)mark(dot)mielke(dot)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

On Mon, May 01, 2006 at 11:18:13AM -0400, Tom Lane wrote:
> mark(at)mark(dot)mielke(dot)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().

Ah. I was wondering about that. When I saw the first poster tag
'SECURITY DEFINER' on the end of the expression I assumed it was
something that I didn't know you could do... :-)

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/

mark(at)mark(dot)mielke(dot)cc writes:
> Ah. I was wondering about that. When I saw the first poster tag
> 'SECURITY DEFINER' on the end of the expression I assumed it was
> something that I didn't know you could do... :-)

No, he was inventing syntax that doesn't exist.

regards, tom lane

I went back to see if I could find the discussion about this in the past.
It was less than I thought. Most it was me posting with some feedback from
Rod Taylor. The thread started with the subject "What user to defaults execute
as?" on general, but I mutated the subject to "setuid for defaults, constraints
and triggers (Was: What user to [sic] defaults execute as?)".

The summary is that I was suggesting that default expressions, triggers and
constraints should all run as the table owner instead of the invoker as
there was little use for them to need the access of the invoker, while there
was benefit in having them run as the owner. In addition there is a mild
security issue in that default expressions and constraints could be used as
trojans so that inserting data into a table could allow that table owner the
ability to do things they shouldn't be doing to the invoker's table. Though
in practice anyone granted to the ability to create functions (which you need
to exploit this) is already very highly trusted.

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> The summary is that I was suggesting that default expressions, triggers and
> constraints should all run as the table owner instead of the invoker as
> there was little use for them to need the access of the invoker, while there
> was benefit in having them run as the owner.

I can see doing this for defaults and constraints, but there is a serious
objection for triggers: you could not use a trigger with
new.blame_me := current_user;
as a more-bulletproof implementation of the tracking column I was on
about before. Furthermore, there already is a way to express the
desired behavior for triggers (when it is in fact the desired behavior):
make the trigger function SECURITY DEFINER.

> In addition there is a mild security issue in that default expressions
> and constraints could be used as trojans so that inserting data into a
> table could allow that table owner the ability to do things they
> shouldn't be doing to the invoker's table.

This risk would exist anyway unless you put strange constraints on
RESET ROLE. It's in general no problem to get back to the outermost
level's security settings.

regards, tom lane

On Sun, Apr 30, 2006 at 01:42:37PM +0300, Hannu Krosing wrote:
> > 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.

I think a big point that's being missed here is that SERIAL *is* trying
to be simple. If you need something more sophisticated or complex you
shouldn't be using SERIAL at all, you should be doing the stuff
yourself, by hand. 99% of the time people just need a nice, simple
autonumber field that behaves as expected. Namely, you don't have to
assign seperate permissions for it, and when you drop the table or
column, you don't end up with some other 'hidden' thing still hanging
around.

In other words, I think we should actually be moving towards #1, and
only allow 'tweaking under the hood' that makes good sense.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Sun, Apr 30, 2006 at 11:45:14AM +0200, Svenne Krap wrote:
> 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.

Sure, but there's no reason that would couldn't allow that with a true
black-box SERIAL, either. In fact, you can do it today if you want,
just by creating a wrapper around nextval(pg_get_serial_sequence()).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:
> mark(at)mark(dot)mielke(dot)cc writes:
> > Ah. I was wondering about that. When I saw the first poster tag
> > 'SECURITY DEFINER' on the end of the expression I assumed it was
> > something that I didn't know you could do... :-)
>
> No, he was inventing syntax that doesn't exist.

Which begs the question, how hard would it be to add that syntax? I
suspect it would be useful in cases besides sequences, and certainly
seems to be a lot less of a hassle than having to wrap stuff in an extra
function just to get that capability...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:
> On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:
>> mark(at)mark(dot)mielke(dot)cc writes:
>>> Ah. I was wondering about that. When I saw the first poster tag
>>> 'SECURITY DEFINER' on the end of the expression I assumed it was
>>> something that I didn't know you could do... :-)
>> No, he was inventing syntax that doesn't exist.
>
> Which begs the question, how hard would it be to add that syntax? I
> suspect it would be useful in cases besides sequences, and certainly
> seems to be a lot less of a hassle than having to wrap stuff in an extra
> function just to get that capability...

In all the internal purity and technical concerns it helps PostGreSQL to
have an easy migration path for MySQL refugees. Anyways I think its
quite clear that there is more need for a black box than a macro.

regards,
Lukas

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> I think a big point that's being missed here is that SERIAL *is* trying
> to be simple. If you need something more sophisticated or complex you
> shouldn't be using SERIAL at all, you should be doing the stuff
> yourself, by hand.

I agree with this point in the abstract, but one important proviso is
that it has to be *possible* to do it by hand. One good thing about
the "SERIAL is just a macro" approach is that it keeps us honest about
making sure that SERIAL isn't exploiting any weird internal behaviors
that are hard to duplicate for handmade sequence defaults. We've
already broken that to some extent by having the hidden dependency,
and that in turn means that fairly-reasonable expectations like
"pg_get_serial_sequence should find the column's associated sequence"
don't work on handmade sequences. I don't want to go much further in
that direction. If there's a usability problem we're trying to solve
for SERIALs, we should make sure the problem gets solved for handmade
sequences too.

regards, tom lane

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:
>> mark(at)mark(dot)mielke(dot)cc writes:
>>> Ah. I was wondering about that. When I saw the first poster tag
>>> 'SECURITY DEFINER' on the end of the expression I assumed it was
>>> something that I didn't know you could do... :-)
>>
>> No, he was inventing syntax that doesn't exist.

> Which begs the question, how hard would it be to add that syntax?

Well, we could. The arguments against would come down to (a) nonstandard
syntax, and (b) possibly needing to make SECURITY a more-reserved word.
(We could avoid point (b) by using something that's already pretty
reserved --- one idea that comes to mind is DEFAULT ... AS OWNER.)

The discussion I was having with Bruno this morning essentially amounted
to doing this automatically, rather than having syntax to enable it.
I guess that backwards compatibility and spec compatibility might be
good arguments for not doing it automatically, though.

I could live with something like this if there's not major objections
out there.

BTW, has anyone looked into whether any of the other major DBs have
something similar? You'd think anyone with sequence-like objects
would have run into this issue. If there is precedent we might want
to follow it.

regards, tom lane

> Sure, but there's no reason that would couldn't allow that with a true
> black-box SERIAL, either. In fact, you can do it today if you want,
> just by creating a wrapper around nextval(pg_get_serial_sequence()).

Or just use lastval()

Chris

On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > I think a big point that's being missed here is that SERIAL *is* trying
> > to be simple. If you need something more sophisticated or complex you
> > shouldn't be using SERIAL at all, you should be doing the stuff
> > yourself, by hand.
>
> I agree with this point in the abstract, but one important proviso is
> that it has to be *possible* to do it by hand. One good thing about
> the "SERIAL is just a macro" approach is that it keeps us honest about
> making sure that SERIAL isn't exploiting any weird internal behaviors
> that are hard to duplicate for handmade sequence defaults. We've
> already broken that to some extent by having the hidden dependency,
> and that in turn means that fairly-reasonable expectations like
> "pg_get_serial_sequence should find the column's associated sequence"
> don't work on handmade sequences. I don't want to go much further in
> that direction. If there's a usability problem we're trying to solve
> for SERIALs, we should make sure the problem gets solved for handmade
> sequences too.
>
> regards, tom lane

I agree with Tom's proviso and add one of my own, mentioned earlier.
It should be easy to use a sequence w/alter sequence almost all of
the time. The majority of the crowd should be able to use SERIAL in
the majority of cases. One reason I am adamant about this is the
v. useful dependencies that are (should be) set between the table
and the sequence when it is declared as a SERIAL.

--elein

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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

No reason there couldn't be a separate function that returns the _actual_ user
rather than the effective user. Sort of like -- well actually the unix
precedents here are more confusing than helpful.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> 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

> No reason there couldn't be a separate function that returns the _actual_ user
> rather than the effective user.

... except that the SQL spec says the above should work. In any case,
if you're thinking of SESSION_USER, that's not really the right thing
either. Imagine that the INSERT is being executed by a SECURITY DEFINER
function --- the owner of the function, not the session user, is really
the one that should be "blamed" with the insert.

regards, tom lane

On Mon, May 01, 2006 at 06:43:00PM -0700, elein wrote:
> On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > I think a big point that's being missed here is that SERIAL *is* trying
> > > to be simple. If you need something more sophisticated or complex you
> > > shouldn't be using SERIAL at all, you should be doing the stuff
> > > yourself, by hand.
> >
> > I agree with this point in the abstract, but one important proviso is
> > that it has to be *possible* to do it by hand. One good thing about
> > the "SERIAL is just a macro" approach is that it keeps us honest about
> > making sure that SERIAL isn't exploiting any weird internal behaviors
> > that are hard to duplicate for handmade sequence defaults. We've
> > already broken that to some extent by having the hidden dependency,
> > and that in turn means that fairly-reasonable expectations like
> > "pg_get_serial_sequence should find the column's associated sequence"
> > don't work on handmade sequences. I don't want to go much further in
> > that direction. If there's a usability problem we're trying to solve
> > for SERIALs, we should make sure the problem gets solved for handmade
> > sequences too.
> >
> > regards, tom lane
>
> I agree with Tom's proviso and add one of my own, mentioned earlier.
> It should be easy to use a sequence w/alter sequence almost all of
> the time. The majority of the crowd should be able to use SERIAL in
> the majority of cases. One reason I am adamant about this is the
> v. useful dependencies that are (should be) set between the table
> and the sequence when it is declared as a SERIAL.

I agree that we shouldn't be arbitrarily removing functionality from
SERIALs that would exist with a hand-grown sequence unless there's good
reason.

I'm wondering if it would be best to essentially promote SERIALs to
being their own type of object? So instead of relying on a naming
convention or pg_get_serial_sequence to then make calls that touch the
underlying sequence (which probably shouldn't be directly accessible),
create functions/syntax that allows the required operations on a SERIAL
itself, such as table.column.nextval(), or nextval(table.column).

Another way to look at this is how we handle VIEWS. Viwes are
implimented under-the-covers as a rule and some hidden table, yet we
don't support (or even allow?) people mucking with the stuff that's
under the hood. I think it would be best from a user standpoint if we
took the same approach with SERIAL, as long as we provide most of the
power that users would have from going the manual sequence route (I say
most because there's probably some oddball cases that wouldn't make
sense supporting, such as two SERIALS operating off the same sequence).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tue, May 02, 2006 at 12:00:42PM -0500, Jim C. Nasby wrote:
> On Mon, May 01, 2006 at 06:43:00PM -0700, elein wrote:
> > On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> > > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > > I think a big point that's being missed here is that SERIAL *is* trying
> > > > to be simple. If you need something more sophisticated or complex you
> > > > shouldn't be using SERIAL at all, you should be doing the stuff
> > > > yourself, by hand.
> > >
> > > I agree with this point in the abstract, but one important proviso is
> > > that it has to be *possible* to do it by hand. One good thing about
> > > the "SERIAL is just a macro" approach is that it keeps us honest about
> > > making sure that SERIAL isn't exploiting any weird internal behaviors
> > > that are hard to duplicate for handmade sequence defaults. We've
> > > already broken that to some extent by having the hidden dependency,
> > > and that in turn means that fairly-reasonable expectations like
> > > "pg_get_serial_sequence should find the column's associated sequence"
> > > don't work on handmade sequences. I don't want to go much further in
> > > that direction. If there's a usability problem we're trying to solve
> > > for SERIALs, we should make sure the problem gets solved for handmade
> > > sequences too.
> > >
> > > regards, tom lane
> >
> > I agree with Tom's proviso and add one of my own, mentioned earlier.
> > It should be easy to use a sequence w/alter sequence almost all of
> > the time. The majority of the crowd should be able to use SERIAL in
> > the majority of cases. One reason I am adamant about this is the
> > v. useful dependencies that are (should be) set between the table
> > and the sequence when it is declared as a SERIAL.
>
> I agree that we shouldn't be arbitrarily removing functionality from
> SERIALs that would exist with a hand-grown sequence unless there's good
> reason.
>
> I'm wondering if it would be best to essentially promote SERIALs to
> being their own type of object? So instead of relying on a naming
> convention or pg_get_serial_sequence to then make calls that touch the
> underlying sequence (which probably shouldn't be directly accessible),
> create functions/syntax that allows the required operations on a SERIAL
> itself, such as table.column.nextval(), or nextval(table.column).
>
> Another way to look at this is how we handle VIEWS. Viwes are
> implimented under-the-covers as a rule and some hidden table, yet we
> don't support (or even allow?) people mucking with the stuff that's
> under the hood. I think it would be best from a user standpoint if we
> took the same approach with SERIAL, as long as we provide most of the
> power that users would have from going the manual sequence route (I say
> most because there's probably some oddball cases that wouldn't make
> sense supporting, such as two SERIALS operating off the same sequence).

This is not what I meant. I meant that most things should be able to be
done by a combination of a SERIAL column definition plus ALTER SERIAL.
But there are other reasons to have sequences as stand alone objects.

And don't get me started on how you cannot create a select rule.
In that case the code to prevent proper use of create rules is probably
as extensive as the code to implement views.

--elein

> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

On Tue, May 02, 2006 at 07:45:13PM -0700, elein wrote:
> On Tue, May 02, 2006 at 12:00:42PM -0500, Jim C. Nasby wrote:
> > On Mon, May 01, 2006 at 06:43:00PM -0700, elein wrote:
> > > On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> > > > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > > > I think a big point that's being missed here is that SERIAL *is* trying
> > > > > to be simple. If you need something more sophisticated or complex you
> > > > > shouldn't be using SERIAL at all, you should be doing the stuff
> > > > > yourself, by hand.
> > > >
> > > > I agree with this point in the abstract, but one important proviso is
> > > > that it has to be *possible* to do it by hand. One good thing about
> > > > the "SERIAL is just a macro" approach is that it keeps us honest about
> > > > making sure that SERIAL isn't exploiting any weird internal behaviors
> > > > that are hard to duplicate for handmade sequence defaults. We've
> > > > already broken that to some extent by having the hidden dependency,
> > > > and that in turn means that fairly-reasonable expectations like
> > > > "pg_get_serial_sequence should find the column's associated sequence"
> > > > don't work on handmade sequences. I don't want to go much further in
> > > > that direction. If there's a usability problem we're trying to solve
> > > > for SERIALs, we should make sure the problem gets solved for handmade
> > > > sequences too.
> > >
> > > I agree with Tom's proviso and add one of my own, mentioned earlier.
> > > It should be easy to use a sequence w/alter sequence almost all of
> > > the time. The majority of the crowd should be able to use SERIAL in
> > > the majority of cases. One reason I am adamant about this is the
> > > v. useful dependencies that are (should be) set between the table
> > > and the sequence when it is declared as a SERIAL.
> >
> > I agree that we shouldn't be arbitrarily removing functionality from
> > SERIALs that would exist with a hand-grown sequence unless there's good
> > reason.
> >
> > I'm wondering if it would be best to essentially promote SERIALs to
> > being their own type of object? So instead of relying on a naming
> > convention or pg_get_serial_sequence to then make calls that touch the
> > underlying sequence (which probably shouldn't be directly accessible),
> > create functions/syntax that allows the required operations on a SERIAL
> > itself, such as table.column.nextval(), or nextval(table.column).
> >
> > Another way to look at this is how we handle VIEWS. Viwes are
> > implimented under-the-covers as a rule and some hidden table, yet we
> > don't support (or even allow?) people mucking with the stuff that's
> > under the hood. I think it would be best from a user standpoint if we
> > took the same approach with SERIAL, as long as we provide most of the
> > power that users would have from going the manual sequence route (I say
> > most because there's probably some oddball cases that wouldn't make
> > sense supporting, such as two SERIALS operating off the same sequence).
>
> This is not what I meant. I meant that most things should be able to be
> done by a combination of a SERIAL column definition plus ALTER SERIAL.
> But there are other reasons to have sequences as stand alone objects.

I'm certainly not suggesting we remove sequences. What I'm saying is
that because a serial is intended to be a time saver, it should act like
one. That means no need to grant seperate permissions, and when you drop
the table or column, the serial should go away as well.

> And don't get me started on how you cannot create a select rule.
> In that case the code to prevent proper use of create rules is probably
> as extensive as the code to implement views.

Uhm, according to the docs you can create select rules. Or are you
suggesting that people should be able to muck around with the internals
of a view?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Wed, May 03, 2006 at 10:12:28AM -0500, Jim C. Nasby wrote:
> On Tue, May 02, 2006 at 07:45:13PM -0700, elein wrote:
> > On Tue, May 02, 2006 at 12:00:42PM -0500, Jim C. Nasby wrote:
> > > On Mon, May 01, 2006 at 06:43:00PM -0700, elein wrote:
> > > > On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> > > > > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > > > > I think a big point that's being missed here is that SERIAL *is* trying
> > > > > > to be simple. If you need something more sophisticated or complex you
> > > > > > shouldn't be using SERIAL at all, you should be doing the stuff
> > > > > > yourself, by hand.
> > > > >
> > > > > I agree with this point in the abstract, but one important proviso is
> > > > > that it has to be *possible* to do it by hand. One good thing about
> > > > > the "SERIAL is just a macro" approach is that it keeps us honest about
> > > > > making sure that SERIAL isn't exploiting any weird internal behaviors
> > > > > that are hard to duplicate for handmade sequence defaults. We've
> > > > > already broken that to some extent by having the hidden dependency,
> > > > > and that in turn means that fairly-reasonable expectations like
> > > > > "pg_get_serial_sequence should find the column's associated sequence"
> > > > > don't work on handmade sequences. I don't want to go much further in
> > > > > that direction. If there's a usability problem we're trying to solve
> > > > > for SERIALs, we should make sure the problem gets solved for handmade
> > > > > sequences too.
> > > >
> > > > I agree with Tom's proviso and add one of my own, mentioned earlier.
> > > > It should be easy to use a sequence w/alter sequence almost all of
> > > > the time. The majority of the crowd should be able to use SERIAL in
> > > > the majority of cases. One reason I am adamant about this is the
> > > > v. useful dependencies that are (should be) set between the table
> > > > and the sequence when it is declared as a SERIAL.
> > >
> > > I agree that we shouldn't be arbitrarily removing functionality from
> > > SERIALs that would exist with a hand-grown sequence unless there's good
> > > reason.
> > >
> > > I'm wondering if it would be best to essentially promote SERIALs to
> > > being their own type of object? So instead of relying on a naming
> > > convention or pg_get_serial_sequence to then make calls that touch the
> > > underlying sequence (which probably shouldn't be directly accessible),
> > > create functions/syntax that allows the required operations on a SERIAL
> > > itself, such as table.column.nextval(), or nextval(table.column).
> > >
> > > Another way to look at this is how we handle VIEWS. Viwes are
> > > implimented under-the-covers as a rule and some hidden table, yet we
> > > don't support (or even allow?) people mucking with the stuff that's
> > > under the hood. I think it would be best from a user standpoint if we
> > > took the same approach with SERIAL, as long as we provide most of the
> > > power that users would have from going the manual sequence route (I say
> > > most because there's probably some oddball cases that wouldn't make
> > > sense supporting, such as two SERIALS operating off the same sequence).
> >
> > This is not what I meant. I meant that most things should be able to be
> > done by a combination of a SERIAL column definition plus ALTER SERIAL.
> > But there are other reasons to have sequences as stand alone objects.
>
> I'm certainly not suggesting we remove sequences. What I'm saying is
> that because a serial is intended to be a time saver, it should act like
> one. That means no need to grant seperate permissions, and when you drop
> the table or column, the serial should go away as well.
>
> > And don't get me started on how you cannot create a select rule.
> > In that case the code to prevent proper use of create rules is probably
> > as extensive as the code to implement views.
>
> Uhm, according to the docs you can create select rules. Or are you
> suggesting that people should be able to muck around with the internals
> of a view?

I warned you not to get me started :) I retract my little side swipe
to avoid going into that discussion here and now. This is not the
appropriate thread. (But that does not mean I do not have opinions
about the limitations of select rules, etc. :)

~elein

> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>