Thread: 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? ;^)