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

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@sss.pgh.pa.us]
Sent: 27 September 2002 16:34
To: tim@ametco.co.uk; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #785: 7.3b2 : Possible Inconsistency with DROP
INDEX ... CASCADE and DROP CONSTRAINT


pgsql-bugs@postgresql.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

"Tim Knowles" <tim@ametco.co.uk> writes:
> ... 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

Well, no, because the second message is not accurate at all for this
situation.  The hint to drop the constraint *instead* is perfectly
correct; when you do that the index will go away too.

I was thinking more of rewording the first line, perhaps like this:

> ERROR:  Cannot drop index t1_pkey because it is part of constraint c1 on table t1

but wasn't completely satisfied with that either.

            regards, tom lane

Is their any way to know if an index is a unique constraint from within the
dependency checker?  In testing with pg_dump I've noted that it seems to
know (I assume from contype in pg_constraint) if the index is just a unique
index or a constraint.

How about this:

Cannot drop unique constraint (index t1_pkey) because part of constraint c1
from table t1 requires it

If this is possible it would at least get you in to a 'constraint mindset'
and hopefully make you realise thats its a constraint first and an index as
a bonus/side effect, so to speak.

Is this easily possible?

Best Regards,

Tim Knowles

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 30 September 2002 15:51
To: Tim Knowles
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #785: 7.3b2 : Possible Inconsistency with DROP
INDEX ... CASCADE and DROP CONSTRAINT


"Tim Knowles" <tim@ametco.co.uk> writes:
> ... 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

Well, no, because the second message is not accurate at all for this
situation.  The hint to drop the constraint *instead* is perfectly
correct; when you do that the index will go away too.

I was thinking more of rewording the first line, perhaps like this:

> ERROR:  Cannot drop index t1_pkey because it is part of constraint c1 on
table t1

but wasn't completely satisfied with that either.

            regards, tom lane