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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Is a SERIAL column a "black box", or not?
Date: 2006-05-01 14:29:12
Message-ID: 5503.1146493752@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-05-01 14:40:07 Re: RELKIND_SPECIAL
Previous Message Jonah H. Harris 2006-05-01 13:55:34 Re: WITH/WITH RECURSIVE implementation discussion