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(dot)1037851915(at)sss(dot)pgh(dot)pa(dot)us>,
tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:

> Justin Clift <justin(at)postgresql(dot)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