Thread: DROP TABLE... CASCADE weirdness

Hackers,

Suppose I do

CREATE TABLE a (a int);
CREATE TABLE b () INHERITS (a);

And then I want to drop both tables:

regression=# DROP TABLE a, b;
NOTICE:  table b depends on table a
ERROR:  Cannot drop table a because other objects depend on it       Use DROP ...  CASCADE to drop the dependent
objectstoo
 


Oh, so I use CASCADE:

regression=# DROP TABLE a, b CASCADE;
NOTICE:  Drop cascades to table b
ERROR:  table "b" does not exist

I understand what's going on and how to get the desired behavior, but
it's weird and I think it should be fixed if possible.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Endurecerse, pero jamas perder la ternura" (E. Guevara)

Alvaro Herrera <alvherre@atentus.com> writes:
> I understand what's going on and how to get the desired behavior, but
> it's weird and I think it should be fixed if possible.

Define why you consider this broken and what you would consider fixed.
        regards, tom lane

Tom Lane dijo: 

> Alvaro Herrera <alvherre@atentus.com> writes:
> > I understand what's going on and how to get the desired behavior, but
> > it's weird and I think it should be fixed if possible.
> 
> Define why you consider this broken

On the first case, if I'm specifying to drop both tables, I don't want
to be bothered telling me that the second depends on the first: I have
already specified that I want it dropped.

On the second case (CASCADE), I'm trying to drop the second table, so I
do not want to be bothered telling me that it doesn't exist, because
that is exactly what I want.

> and what you would consider fixed.

In both cases (CASCADE and RESTRICT), both tables should be dropped
(after all, that's what I'm trying to do).

It's only an annoyance, and I suppose it's very difficult to "fix".
My solution would be first to fetch the whole list of OIDs to be dropped
and only then do the deletion.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

Alvaro Herrera <alvherre@atentus.com> writes:
> Tom Lane dijo: 
>> Alvaro Herrera <alvherre@atentus.com> writes:
> I understand what's going on and how to get the desired behavior, but
> it's weird and I think it should be fixed if possible.
>> 
>> Define why you consider this broken

> On the first case, if I'm specifying to drop both tables, I don't want
> to be bothered telling me that the second depends on the first: I have
> already specified that I want it dropped.

I believe that "DROP TABLE a, b CASCADE" is (and should be) equivalent
toDROP TABLE a CASCADE;DROP TABLE b CASCADE;

It would be really hard to make the case that the latter pair of
commands should work in the scenario you give.  Perhaps you should
try to make the case that this equivalence is wrong ... but I don't
much care for that idea either.  If it is wrong, exactly how will
you define the command to work instead?

> My solution would be first to fetch the whole list of OIDs to be dropped
> and only then do the deletion.

I don't think that will get you anywhere in terms of avoiding failures;
you'd still find yourself trying to drop already-dropped tables, only by
OID instead of name.
        regards, tom lane

On Sat, 2002-09-14 at 05:17, Tom Lane wrote:
> Alvaro Herrera <alvherre@atentus.com> writes:
> > Tom Lane dijo: 
> >> Alvaro Herrera <alvherre@atentus.com> writes:
> > I understand what's going on and how to get the desired behavior, but
> > it's weird and I think it should be fixed if possible.
> >> 
> >> Define why you consider this broken
> 
> > On the first case, if I'm specifying to drop both tables, I don't want
> > to be bothered telling me that the second depends on the first: I have
> > already specified that I want it dropped.
> 
> I believe that "DROP TABLE a, b CASCADE" is (and should be) equivalent
> to
>     DROP TABLE a CASCADE;
>     DROP TABLE b CASCADE;
> 
> It would be really hard to make the case that the latter pair of
> commands should work in the scenario you give.  Perhaps you should
> try to make the case that this equivalence is wrong ... but I don't
> much care for that idea either.  If it is wrong, exactly how will
> you define the command to work instead?
> 
> > My solution would be first to fetch the whole list of OIDs to be dropped
> > and only then do the deletion.
> 
> I don't think that will get you anywhere in terms of avoiding failures;
> you'd still find yourself trying to drop already-dropped tables, only by
> OID instead of name.

This seems to be a problem that is of similar nature to our UNIQUE
constraints not working in all cases (depending on the _physical_ order
of tuples, which should never affect any user-visible behaviour).

The two DROP TABLE cases are not equivalent in the sense that the first
is _one_ command and the other is _two_ separate commands.

OTOH, I don't think that fixing DROP TABLE is as urgent as the UNIQUE
because* the UNIQUE bug can come to haunt as at random times* DROP TABLE is usually done by more qualified people
(DBAsand programmers)* our whole inheritance stuff is still somewhat a moving target.
 

-------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> This seems to be a problem that is of similar nature to our UNIQUE
> constraints not working in all cases (depending on the _physical_ order
> of tuples, which should never affect any user-visible behaviour).

No, I don't see any similarity at all.  The behavior Alvaro is unhappy
with is perfectly deterministic and repeatable.  He's basically saying
that DROP should be forgiving of redundant DROP operations, so long as
they are packaged into a single command.  I don't really agree with
that, but it doesn't seem related to the UNIQUE issues.

> The two DROP TABLE cases are not equivalent in the sense that the first
> is _one_ command and the other is _two_ separate commands.

As long as they are wrapped into a single transaction block, there is no
difference.
        regards, tom lane

On Sat, 2002-09-14 at 19:43, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > This seems to be a problem that is of similar nature to our UNIQUE
> > constraints not working in all cases (depending on the _physical_ order
> > of tuples, which should never affect any user-visible behaviour).
> 
> No, I don't see any similarity at all.  The behavior Alvaro is unhappy
> with is perfectly deterministic and repeatable.  He's basically saying
> that DROP should be forgiving of redundant DROP operations, so long as
> they are packaged into a single command.

> I don't really agree with that, but it doesn't seem related to the UNIQUE issues.

The similarity is in COMMAND vs. TRANCACTION level checks.

Both other databases I tested (Oracle and DB2) disallow commands that
violate unique even inside a transaction, but they both allow commands
that must have some point of internal violation _during_ any serial
execution of the command:

ie. for table 

t(i int not null unique)

having values 1 and 2

the command 

update  t set i=2 where i=1;

is not allowed on either of then, even inside transaction, but both 

update  t set i=i+1;

and

update  t set i=i-1;

are allowed.

> > The two DROP TABLE cases are not equivalent in the sense that the first
> > is _one_ command and the other is _two_ separate commands.
> 
> As long as they are wrapped into a single transaction block, there is no
> difference.

Once we will be able to continue after errors it may become a
significant difference -

DROP TABLE a,b CASCADE; 
COMMIT;

will leave the tables untouched whereas 

DROP TABLE b CASCADE;
DROP TABLE a CASCADE;
COMMIT;

Will delete both tables but issue an error;

-----
Hannu