Re: DROP TABLE... CASCADE weirdness

On Sat, 2002-09-14 at 19:43, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)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