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

Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT

From: "Tim Knowles" <tim(at)ametco(dot)co(dot)uk>
To: <pgsql-bugs(at)postgresql(dot)org>, <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT
Date: 2002-09-30 13:27:12
Message-ID: NGBBIAKALHHLLCHKLBONGECLCBAA.tim@ametco.co.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
Thanks Tom, I understand now the thinking behind disallowing the drop index
... cascade.  If only I'd spent a little bit more time thinking it thru
properly!  I do though think the error message could do with a slight change
of wording from:

ERROR:  Cannot drop index t1_pkey because constraint c1 on table t1 requires
it
        You may drop constraint c1 on table t1 instead
to:

ERROR:  Cannot drop index t1_pkey because constraint c1 on table t1 requires
it
        To drop index t1_pkey you will need to drop the constraint c1 from
table t1 first

I'd imagine that putting a caveat for foreign key dependencies in the docs
for DROP INDEX would probably cause more confusion than it would help stop!

I have just started learning C so I'm not currently able to offer much,
other than the odd bug report.  I hope to change that soon!

As an aside their's an interesting article on WAL's at
http://www.dbazine.com/gulutzan2.html

Best Regards,

Tim Knowles

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 27 September 2002 16:34
To: tim(at)ametco(dot)co(dot)uk; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Bug #785: 7.3b2 : Possible Inconsistency with DROP
INDEX ... CASCADE and DROP CONSTRAINT


pgsql-bugs(at)postgresql(dot)org writes:
> CREATE TABLE t1 (
>   col_a int,
>   PRIMARY KEY (col_a)
> );

> --DROP INDEX t1_pkey CASCADE WILL NOT CASCADE THE DROP TO DEPENDENT
OBJECTS
> DROP INDEX t1_pkey CASCADE;

> --ALTER TABLE .. DROP .. CASCADE WILL CASCADE THE DROP TO THE FOREIGN KEY
CONSTRAINT
> ALTER TABLE t1 DROP CONSTRAINT t1_pkey CASCADE;

This is deliberate: you created the index indirectly via a constraint,
so you should drop the constraint rather than dropping the index itself.
Essentially, the index is only an implementation detail that you should
not be messing with directly.

Or at least that was the design idea.  If you think this is wrongheaded,
feel free to start a discussion about it on pghackers.

It might be that the behavior is okay but the error message should be
phrased differently in this case.  Any thoughts?

			regards, tom lane


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-09-30 14:50:55
Subject: Re: Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT
Previous:From: Tom LaneDate: 2002-09-29 15:09:42
Subject: Re: Bug #790: Optimizer does not want to use an index for large table

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