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

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