From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Clift <justin@postgresql.org>
Cc: Oliver Elphick <olly@lfix.co.uk>
Cc: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [GENERAL] Bug with sequence
Date: Wed, 20 Nov 2002 23:11:55 -0500
Message-ID: <7017.1037851915@sss.pgh.pa.us> (raw)
In-Reply-To: <3DDC47A9.4C0BC2DE@postgresql.org>
References: <92c0776e.0211180745.49911131@posting.google.com>
<1037782392.29678.88.camel@linda>
<1037828113.23925.101.camel@camel>
<1037830378.24840.40.camel@linda>
<3DDC47A9.4C0BC2DE@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, justin@postgresql.org, olly@lfix.co.uk, xzilla@users.sourceforge.net, pgsql-hackers@postgresql.org
Subject: Re: [GENERAL] Bug with sequence
In-Reply-To: <7017.1037851915@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox