Обсуждение: Re: [GENERAL] Bug with sequence

It seems worth pointing out, too, that some SQL purists propose not 
relying on product-specific methods of auto-incrementing.

I.e., it is possible to do something like:

insert into foo( col, ... )
values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );

and this is easily placed in a trigger.

-tfo

In article <7017.1037851915@sss.pgh.pa.us>,tgl@sss.pgh.pa.us (Tom Lane) wrote:

> Justin Clift <justin@postgresql.org> writes:
> > Oliver Elphick wrote:
> >> I created a sequence using SERIAL when I created a table.  I used the
> >> same sequence for another table by setting a column default to
> >> nextval(sequence).
> >> 
> >> I deleted the first table.  The sequence was deleted too, leaving the
> >> default of the second table referring to a non-existent sequence.
> 
> > This sounds like a serious bug in our behaviour, and not something we'd
> > like to release.
> 
> We will be releasing it whether we like it or not, because
> nextval('foo') doesn't expose any visible dependency on sequence foo.
> 
> (If you think it should, how about nextval('fo' || 'o')?  If you think
> that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
> 
> The long-term answer is to do what Rod alluded to: support the
> Oracle-style syntax foo.nextval, so that the sequence reference is
> honestly part of the parsetree and not buried inside a string
> expression.
> 
> In the meantime, I consider that Oliver was misusing the SERIAL
> feature.  If you want multiple tables fed by the same sequence object,
> you should create the sequence as a separate object and then create
> the tables using explicit "DEFAULT nextval('foo')" clauses.  Doing what
> he did amounts to sticking his fingers under the hood of the SERIAL
> implementation; if he gets his fingers burnt, it's his problem.
> 
> > Specifically in relation to people's existing scripts, and also to
> > people who are doing dump/restore of specific tables (it'll kill the
> > sequences that other tables depend on too!)
> 
> 7.3 breaks no existing schemas, because older schemas will be dumped
> as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> commands.
> 
>                         regards, tom lane

Of course, those would be SQL purists who _don't_ understand
concurrency issues.  ;-)

---------------------------------------------------------------------------

Thomas O'Connell wrote:
> It seems worth pointing out, too, that some SQL purists propose not 
> relying on product-specific methods of auto-incrementing.
> 
> I.e., it is possible to do something like:
> 
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
> 
> and this is easily placed in a trigger.
> 
> -tfo
> 
> In article <7017.1037851915@sss.pgh.pa.us>,
>  tgl@sss.pgh.pa.us (Tom Lane) wrote:
> 
> > Justin Clift <justin@postgresql.org> writes:
> > > Oliver Elphick wrote:
> > >> I created a sequence using SERIAL when I created a table.  I used the
> > >> same sequence for another table by setting a column default to
> > >> nextval(sequence).
> > >> 
> > >> I deleted the first table.  The sequence was deleted too, leaving the
> > >> default of the second table referring to a non-existent sequence.
> > 
> > > This sounds like a serious bug in our behaviour, and not something we'd
> > > like to release.
> > 
> > We will be releasing it whether we like it or not, because
> > nextval('foo') doesn't expose any visible dependency on sequence foo.
> > 
> > (If you think it should, how about nextval('fo' || 'o')?  If you think
> > that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
> > 
> > The long-term answer is to do what Rod alluded to: support the
> > Oracle-style syntax foo.nextval, so that the sequence reference is
> > honestly part of the parsetree and not buried inside a string
> > expression.
> > 
> > In the meantime, I consider that Oliver was misusing the SERIAL
> > feature.  If you want multiple tables fed by the same sequence object,
> > you should create the sequence as a separate object and then create
> > the tables using explicit "DEFAULT nextval('foo')" clauses.  Doing what
> > he did amounts to sticking his fingers under the hood of the SERIAL
> > implementation; if he gets his fingers burnt, it's his problem.
> > 
> > > Specifically in relation to people's existing scripts, and also to
> > > people who are doing dump/restore of specific tables (it'll kill the
> > > sequences that other tables depend on too!)
> > 
> > 7.3 breaks no existing schemas, because older schemas will be dumped
> > as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> > commands.
> > 
> >                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

"Thomas O'Connell" <tfo@monsterlabs.com> writes:
> It seems worth pointing out, too, that some SQL purists propose not 
> relying on product-specific methods of auto-incrementing.
> I.e., it is possible to do something like:
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
> and this is easily placed in a trigger.

... but that approach is entirely unworkable if you want any concurrency
of insert operations.  (Triggers are a tad product-specific, too :-()
        regards, tom lane

On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> Of course, those would be SQL purists who _don't_ understand
> concurrency issues.  ;-)

Or they're the kind that locks the entire table for any given insert.

-- 
Rod Taylor <rbt@rbt.ca>

On 21 Nov 2002, Rod Taylor wrote:

> On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> > Of course, those would be SQL purists who _don't_ understand
> > concurrency issues.  ;-)
> 
> Or they're the kind that locks the entire table for any given insert.

Isn't that what Bruce just said?  ;^)