Skip site navigation (1) Skip section navigation (2)

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

From: Mark Dilger <pgsql(at)markdilger(dot)com>
To: mark(at)mark(dot)mielke(dot)cc
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Is a SERIAL column a "black box", or not?
Date: 2006-04-30 22:06:07
Message-ID: 445534CF.2030207@markdilger.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2006-05-01 05:02:12
Subject: RELKIND_SPECIAL
Previous:From: eleinDate: 2006-04-30 21:02:12
Subject: Re: Is a SERIAL column a "black box", or not?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group