Skip site navigation (1) Skip section navigation (2)

Patch for bug #2073 (Can't drop sequence when created via SERIAL column)

From: Dhanaraj M - Sun Microsystems <dhanaraj(dot)m(at)mail-apac(dot)sun(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Patch for bug #2073 (Can't drop sequence when created via SERIAL column)
Date: 2006-04-10 09:19:06
Message-ID: 443A230A.8010603@mail-apac.sun.com (view raw or flat)
Thread:
Lists: pgsql-patches
Hi all

I fixed the above bug. I attach the patch here. Please review and 
acknowledge me.

Bug details
========
BUG #2073: Can't drop sequence when created via SERIAL column

    * From: "Aaron Dummer" <aaron ( at ) dummer ( dot ) info>
    * To: pgsql-bugs ( at ) postgresql ( dot ) org
    * Subject: BUG #2073: Can't drop sequence when created via SERIAL column
    * Date: Mon, 28 Nov 2005 21:10:33 +0000 (GMT)

The following bug has been logged online:

Bug reference:      2073
Logged by:          Aaron Dummer
Email address:      aaron ( at ) dummer ( dot ) info
PostgreSQL version: 8.0.3
Operating system:   Debian Linux
Description:        Can't drop sequence when created via SERIAL column
Details:

If I create a table named foo with a column named bar, column type SERIAL,
it auto-generates a sequence named foo_bar_seq.  Now if I manually create a
new sequence called custom_seq, and change the default value of foo.bar to
reference the new sequence, I still can't delete the old sequence
(foo_bar_seq).

In other words, from a user's point of view, the foo table is no longer
dependent on the foo_bar_seq, yet the system still sees it as dependent.

I brought this topic up on the #postgresql IRC channel and the behavior was
confirmed by AndrewSN, scampbell_, and mastermind.
--------------------------------------------
# 1 - create the table with a SERIAL column
beta=# CREATE TABLE test_table (id serial, value text);
NOTICE:  CREATE TABLE will create implicit sequence "test_table_id_seq" for
serial column "test_table.id"
CREATE TABLE

# 2 - create the new sequence you want to use
beta=# CREATE SEQUENCE new_sequence_seq;
CREATE SEQUENCE

#3 - alter the table so it uses the new sequence you made
beta=# ALTER TABLE test_table ALTER COLUMN id SET DEFAULT
nextval('new_sequence_seq');
ALTER TABLE

#4 - try to delete the test_table_id_seq, since it's not used anymore
beta=# DROP SEQUENCE test_table_id_seq;
ERROR:  cannot drop sequence test_table_id_seq because table test_table 
column
id requires it
HINT:  You may drop table test_table column id instead.


You see, PostgreSQL used some underlying mechanism for noting that 
test_table
depends on test_table_id_seq.  It shouldn't do that.


Attachment: DefaultSeqFix.txt
Description: text/plain (5.2 KB)

Responses

pgsql-patches by date

Next:From: Qingqing ZhouDate: 2006-04-10 09:22:13
Subject: Re: Support Parallel Query Execution in Executor
Previous:From: Martijn van OosterhoutDate: 2006-04-10 09:16:56
Subject: Re: Support Parallel Query Execution in Executor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group