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

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
bindthem...
 
                          http://mark.mielke.cc/