Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0EI89V50843 for ; Mon, 14 Jan 2002 13:08:09 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g0EI7v313637; Mon, 14 Jan 2002 13:07:57 -0500 (EST) From: Bruce Momjian Message-Id: <200201141807.g0EI7v313637@candle.pha.pa.us> Subject: Re: CREATE TABLE glitch -fix request for 7.2 In-Reply-To: To: Josh Berkus Date: Mon, 14 Jan 2002 13:07:57 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200201/144 X-Sequence-Number: 6232 Josh Berkus wrote: > Tom, Peter, Stephan, et al.: > > Here's a glitch that's bothered me for a while (ver 7.0 -> 7.2b4 ) in > PostgreSQL DDL statements. I was thinking that since 7.2 is still in beta, > that you could fix it this version. > > To produce the glitch: > 1. Create a SQL script file that drops a table, then creates that table with a > SERIAL primary key (I do this all the time to build my databases) > 2. Run the script once to create the table. You'll get a error (no table to > drop) but that doesn't matter. > 3. Run the script a second time, as if you were making changes to the data > structure and needed to rebuild. > 4. You will get an error telling you that "table1_id_seq" already exists, and > the CREATE TABLE statement will fail. > > This is very user-unfriendly behaviour, especially in a database that still > does not support about 50% of ALTER TABLE. I spend a fair amount of extra > time deleting SERIAL sequences when I am doing the database-building part of > the development process. > > Can we change this behavior, please? I'd suppose that it would require you to > create some sort of permanent link between SERIAL columns and the sequences > they spawn. We need pg_depend table to track dependency of sequence on specific tables, hopefully in >=7.3. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026