Thread: Should this require CASCADE?

Consider
CREATE TABLE foo (f1 int primary key);
CREATE TABLE bar (f1 int references foo);
DROP TABLE foo RESTRICT;

Should this succeed?  Or should it be necessary to say DROP CASCADE to
get rid of the foreign-key reference to foo?

Our historical behavior is to allow the drop, while issuing a notice
about implicit deletion of triggers.  But I think SQL92 intends that
CASCADE should be required.

(If you deduce from this question that a lot of Rod Taylor's pg_depend
patch is working here, you are right...)
        regards, tom lane

On Wed, 10 Jul 2002, Tom Lane wrote:

> Consider
>
>     CREATE TABLE foo (f1 int primary key);
>
>     CREATE TABLE bar (f1 int references foo);
>
>     DROP TABLE foo RESTRICT;
>
> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> get rid of the foreign-key reference to foo?
>
> Our historical behavior is to allow the drop, while issuing a notice
> about implicit deletion of triggers.  But I think SQL92 intends that
> CASCADE should be required.

I think the above should fail.  If someone was adding restrict since it
was optional, I'd guess they were doing so in advance for the days when
we'd actually restrict the drop.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 10 Jul 2002, Tom Lane wrote:
>> DROP TABLE foo RESTRICT;
>> 
>> Should this succeed?  Or should it be necessary to say DROP CASCADE to
>> get rid of the foreign-key reference to foo?

> I think the above should fail.  If someone was adding restrict since it
> was optional, I'd guess they were doing so in advance for the days when
> we'd actually restrict the drop.

Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
until now.  What I'm intending though is that DROP with no option will
default to DROP RESTRICT, which means that a lot of cases that used to
be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
my example just to emphasize that the intended behavior is RESTRICT.

So if you prefer, imagine same example but you merely sayDROP TABLE foo;
Does your answer change?
        regards, tom lane

On Wed, 10 Jul 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> DROP TABLE foo RESTRICT;
> >>
> >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> >> get rid of the foreign-key reference to foo?
>
> > I think the above should fail.  If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
>
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now.  What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.
>
> So if you prefer, imagine same example but you merely say
>     DROP TABLE foo;
> Does your answer change?

That's tougher.  If I had a choice without worrying about the complexities
involved, I'd say that DROP TABLE foo; should restrict unless the only
references were from foreign keys and that those should cascade which is
the similar behavior to past versions without the really unsafe
referencing things that don't exist, and restrict and cascade should work
as specified.  However, that adds effectively a third drop behavior and
one that isn't in the spec and would have to be documented, however I
think (unless I misread the spec) it wouldn't directly conflict with the
spec since drop behavior isn't optional.

Given that that's a can of worms we probably don't want to open, I
think restrict is probably safer behavior even though it breaks
compatibility with old versions even more than the above, but I think
silently cascading will be more difficult for users (hey, where did
my definition of <X> go?).

On Wed, 2002-07-10 at 18:33, Tom Lane wrote:
> Consider
> 
>     CREATE TABLE foo (f1 int primary key);
> 
>     CREATE TABLE bar (f1 int references foo);
> 
>     DROP TABLE foo RESTRICT;

> Our historical behavior is to allow the drop, while issuing a notice
> about implicit deletion of triggers.  But I think SQL92 intends that
> CASCADE should be required.

I think you know my answer (Fail).

- As stated, spec intends it to be required
- Number of automated scripts doing drop table is small
- Users will quickly learn the ropes.  They would be surprised if it
cascaded by default.

The question I suppose is:

DROP TABLE foo;

Does it default to restrict or cascade?  Currently it is restrict.  I
don't believe the spec allows those statements to be without the
qualifier.


Or, how about ALTER TABLE bar DROP CONSTRAINT <fkey_cons> RESTRICT;

I forget what happens here -- does bar depend on foo via the fkey?


ALTER TABLE foo DROP CONSTRAINT <primary key> RESTRICT; should
definitely fail (bar depends on fkey which depends on foo.pkey).

Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> DROP TABLE foo RESTRICT;
> >>
> >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> >> get rid of the foreign-key reference to foo?
> 
> > I think the above should fail.  If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
> 
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now.  What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.

I think the idea was to have it default to CASCADE for this release, not
to break existing code right away. Then 7.3 is transition time and
RESTRICT will be the default from the next release on.

If so, this has to go into the release notes.


Jan

> 
> So if you prefer, imagine same example but you merely say
>         DROP TABLE foo;
> Does your answer change?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

IMHO, I believe that the standard should be adhered to if at all possible.
Since Cascade was added, Restrict must be the default is my reading of the
standard.

So that everyone can talk from the same sheet, the 1999 SQL Standard for DROP
TABLE follows:


11.20 <drop table statement>
Function
Destroy a table.
Format
<drop table statement> ::=
DROP TABLE <table name> <drop behavior>
Syntax Rules
1) Let T be the table identified by the <table name> and let TN be that <table
name>.
2) The schema identified by the explicit or implicit schema name of the <table
name> shall include the descriptor of T.
3) T shall be a base table.
4) T shall not be a declared local temporary table.
5) If RESTRICT is specified, then T shall not have any subtables.
6) If RESTRICT is specified, then T shall not be referenced in any of the
following:
a) The <query expression> of any view descriptor.
b) The <search condition> of any table check constraint descriptor of any table
other than T or the <search condition> of a constraint descriptor of an
assertion descriptor.
c) The table descriptor of the referenced table of any referential constraint
descriptor of any table other than T.
d) The scope of the declared type of a column of a table other than T and of the
declared type of an SQL parameter of any SQL-invoked routine.
e) The <SQL routine body> of any SQL-invoked routine descriptor.
f) The scope of the declared type of an SQL parameter of any SQL-invoked
routine.
g) The trigger action of any trigger descriptor.
NOTE 197 - If CASCADE is specified, then such referenced objects will be dropped
by the execution of the <revoke statement> specified in the General Rules of
this Subclause.
7) Let A be the <authorization identifier> that owns the schema identified by
the <schema name> of the table identified by TN.
8) Let the containing schema be the schema identified by the <schema name>
explicitly or implicitly contained in <table name>.
Access Rules
1) The enabled authorization identifiers shall include A.
General Rules
1) Let ST be the <table name> of any subtable of T. The following <drop table
statement> is effectively executed without further Access Rule checking:
DROP TABLE ST CASCADE
2) If T is a referenceable table, then:
a) Let ST be structured type associated with T.
b) Let RST be the reference type whose referenced type is ST.
c) Let DT be any table whose table descriptor includes a column descriptor that
generally includes a field descriptor, an attribute descriptor, or an array
descriptor that includes a reference type descriptor RST whose scope includes
TN.
NOTE 198 - A descriptor that ''generally includes'' another descriptor is
defined in Subclause 6.2.4, "Descriptors", in ISO/IEC 9075-1.
d) Let DTN be the name of the table DT.
e) Case:
i) If DT is a base table, then the following <drop table statement> is
effectively executed without further Access Rule checking:
DROP TABLE DTN CASCADE
ii) Otherwise, the following <drop view statement> is effectively executed
without further Access Rule checking:
DROP VIEW DTN CASCADE
3) For every supertable of T, every superrow and every subrow of every row of T
is effectively deleted at the end of the SQL-statement, prior to the checking of
any integrity constraints.
NOTE 199 - This deletion creates neither a new trigger execution context nor the
definition of a new state change in the current trigger execution context.
4) The following <revoke statement> is effectively executed with a current
authorization identifier of ''_SYSTEM'' and without further Access Rule
checking:
REVOKE ALL PRIVILEGES ON TN FROM A CASCADE
5) Let R be any SQL-invoked routine whose routine descriptor contains the <table
name> of T in the <SQL routine body>. Let SN be the <specific name> of R. The
following <drop routine statement> is effectively executed without further
Access Rule checking:
DROP SPECIFIC ROUTINE SN CASCADE
6) For each direct supertable DST of T, the table name of T is removed from the
list of table names of direct subtables of DST that is included in the table
descriptor of DST.
7) The descriptor of T is destroyed.
Conformance Rules
1) Without Feature F032, ''CASCADE drop behavior'', a <drop behavior> of CASCADE
shall not be specified in <drop table statement>.



-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Wednesday, July 10, 2002 7:35 PM
To: Tom Lane
Cc: Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> DROP TABLE foo RESTRICT;
> >>
> >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> >> get rid of the foreign-key reference to foo?
> 
> > I think the above should fail.  If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
> 
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now.  What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.

I think the idea was to have it default to CASCADE for this release, not
to break existing code right away. Then 7.3 is transition time and
RESTRICT will be the default from the next release on.

If so, this has to go into the release notes.


Jan

> 
> So if you prefer, imagine same example but you merely say
>         DROP TABLE foo;
> Does your answer change?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Actually, the answer is even clearer, the standard calls for the specification
of "CASCADE" or "RESTRICT" and doesn't support _not_ having that specified.
(the <drop behavior> is NOT [drop behavior] aka optional)

NOTE: <drop behavior> ::= CASCADE | RESTRICT 
as defined in 11.2


Dana
PS to be complete: all these references are to: ISO/IEC 9075-2:1999 "Foundation"

-----Original Message-----
From: Groff, Dana [mailto:Dana.Groff@filetek.com]
Sent: Wednesday, July 10, 2002 8:30 PM
To: 'pgsql-hackers@postgreSQL.org'
Subject: Re: [HACKERS] Should this require CASCADE?


IMHO, I believe that the standard should be adhered to if at all possible.
Since Cascade was added, Restrict must be the default is my reading of the
standard.

So that everyone can talk from the same sheet, the 1999 SQL Standard for DROP
TABLE follows:


11.20 <drop table statement>
Function
Destroy a table.
Format
<drop table statement> ::=
DROP TABLE <table name> <drop behavior>
Syntax Rules
1) Let T be the table identified by the <table name> and let TN be that <table
name>.
2) The schema identified by the explicit or implicit schema name of the <table
name> shall include the descriptor of T.
3) T shall be a base table.
4) T shall not be a declared local temporary table.
5) If RESTRICT is specified, then T shall not have any subtables.
6) If RESTRICT is specified, then T shall not be referenced in any of the
following:
a) The <query expression> of any view descriptor.
b) The <search condition> of any table check constraint descriptor of any table
other than T or the <search condition> of a constraint descriptor of an
assertion descriptor.
c) The table descriptor of the referenced table of any referential constraint
descriptor of any table other than T.
d) The scope of the declared type of a column of a table other than T and of the
declared type of an SQL parameter of any SQL-invoked routine.
e) The <SQL routine body> of any SQL-invoked routine descriptor.
f) The scope of the declared type of an SQL parameter of any SQL-invoked
routine.
g) The trigger action of any trigger descriptor.
NOTE 197 - If CASCADE is specified, then such referenced objects will be dropped
by the execution of the <revoke statement> specified in the General Rules of
this Subclause.
7) Let A be the <authorization identifier> that owns the schema identified by
the <schema name> of the table identified by TN.
8) Let the containing schema be the schema identified by the <schema name>
explicitly or implicitly contained in <table name>.
Access Rules
1) The enabled authorization identifiers shall include A.
General Rules
1) Let ST be the <table name> of any subtable of T. The following <drop table
statement> is effectively executed without further Access Rule checking:
DROP TABLE ST CASCADE
2) If T is a referenceable table, then:
a) Let ST be structured type associated with T.
b) Let RST be the reference type whose referenced type is ST.
c) Let DT be any table whose table descriptor includes a column descriptor that
generally includes a field descriptor, an attribute descriptor, or an array
descriptor that includes a reference type descriptor RST whose scope includes
TN.
NOTE 198 - A descriptor that ''generally includes'' another descriptor is
defined in Subclause 6.2.4, "Descriptors", in ISO/IEC 9075-1.
d) Let DTN be the name of the table DT.
e) Case:
i) If DT is a base table, then the following <drop table statement> is
effectively executed without further Access Rule checking:
DROP TABLE DTN CASCADE
ii) Otherwise, the following <drop view statement> is effectively executed
without further Access Rule checking:
DROP VIEW DTN CASCADE
3) For every supertable of T, every superrow and every subrow of every row of T
is effectively deleted at the end of the SQL-statement, prior to the checking of
any integrity constraints.
NOTE 199 - This deletion creates neither a new trigger execution context nor the
definition of a new state change in the current trigger execution context.
4) The following <revoke statement> is effectively executed with a current
authorization identifier of ''_SYSTEM'' and without further Access Rule
checking:
REVOKE ALL PRIVILEGES ON TN FROM A CASCADE
5) Let R be any SQL-invoked routine whose routine descriptor contains the <table
name> of T in the <SQL routine body>. Let SN be the <specific name> of R. The
following <drop routine statement> is effectively executed without further
Access Rule checking:
DROP SPECIFIC ROUTINE SN CASCADE
6) For each direct supertable DST of T, the table name of T is removed from the
list of table names of direct subtables of DST that is included in the table
descriptor of DST.
7) The descriptor of T is destroyed.
Conformance Rules
1) Without Feature F032, ''CASCADE drop behavior'', a <drop behavior> of CASCADE
shall not be specified in <drop table statement>.



-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Wednesday, July 10, 2002 7:35 PM
To: Tom Lane
Cc: Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> DROP TABLE foo RESTRICT;
> >>
> >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> >> get rid of the foreign-key reference to foo?
> 
> > I think the above should fail.  If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
> 
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now.  What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.

I think the idea was to have it default to CASCADE for this release, not
to break existing code right away. Then 7.3 is transition time and
RESTRICT will be the default from the next release on.

If so, this has to go into the release notes.


Jan

> 
> So if you prefer, imagine same example but you merely say
>         DROP TABLE foo;
> Does your answer change?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Jan Wieck <JanWieck@Yahoo.com> writes:
> I think the idea was to have it default to CASCADE for this release, not
> to break existing code right away.

I never thought that.  If we default to CASCADE then a DROP is likely to
delete stuff that it would not have deleted in prior releases.  That
seems *far* more dangerous than "breaking existing code".  I doubt
there's much existing code that does automatic DROPs anyway, at least
of things that might have dependencies.
        regards, tom lane

> Jan Wieck <JanWieck@Yahoo.com> writes:
> > I think the idea was to have it default to CASCADE for this release, not
> > to break existing code right away.
>
> I never thought that.  If we default to CASCADE then a DROP is likely to
> delete stuff that it would not have deleted in prior releases.  That
> seems *far* more dangerous than "breaking existing code".  I doubt
> there's much existing code that does automatic DROPs anyway, at least
> of things that might have dependencies.

Wow - I think defaulting to CASCADE is nuts!  Surely RESTRICT should be the
safest default?

Chris

On Wed, 10 Jul 2002, Tom Lane wrote:

>     CREATE TABLE foo (f1 int primary key);
>     CREATE TABLE bar (f1 int references foo);
>     DROP TABLE foo RESTRICT;
>
> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> get rid of the foreign-key reference to foo?
>
> Our historical behavior is to allow the drop, while issuing a notice
> about implicit deletion of triggers.  But I think SQL92 intends that
> CASCADE should be required.

I don't think it should succeed, no; to me the historical behaviour
seems wrong. To get a bit Dateish (or is that Datish?) for a moment,
when you created table bar, you added this rule to the set of rules
for your database:
   For every f1 in bar, there exists the same f1 in foo.

If you allow table foo to be dropped, you make that statement false. But
I think removing that rule should be an explicit, not implicit action.

And as far as the compatability thing goes, well, probably pretty
much everyone agrees that it's better to break things such that
you are less likely to lose data....

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC
 

Jan Wieck wrote:
> Tom Lane wrote:
> > 
> > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > On Wed, 10 Jul 2002, Tom Lane wrote:
> > >> DROP TABLE foo RESTRICT;
> > >>
> > >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> > >> get rid of the foreign-key reference to foo?
> > 
> > > I think the above should fail.  If someone was adding restrict since it
> > > was optional, I'd guess they were doing so in advance for the days when
> > > we'd actually restrict the drop.
> > 
> > Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> > until now.  What I'm intending though is that DROP with no option will
> > default to DROP RESTRICT, which means that a lot of cases that used to
> > be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> > my example just to emphasize that the intended behavior is RESTRICT.
> 
> I think the idea was to have it default to CASCADE for this release, not
> to break existing code right away. Then 7.3 is transition time and
> RESTRICT will be the default from the next release on.

I am not in favor of changing thing 1/2 way for one release, then doing
the final job in the next release.  If we need to change it, we document
it and move on.  Two smalll changes are worse than one big change.

As far as this question, seems with no RESTRICT/CASCADE, it fails, with
RESTRICT it drops the trigger, and with CASCADE it drops the referencing
table.  Is that accurate?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

"Groff, Dana" <Dana.Groff@filetek.com> writes:
> Actually, the answer is even clearer, the standard calls for the specification
> of "CASCADE" or "RESTRICT" and doesn't support _not_ having that specified.
> (the <drop behavior> is NOT [drop behavior] aka optional)

Right, the spec does not allow it to be defaulted.  We will, however,
since the alternative is breaking every PG application that uses DROP.

Defaulting to RESTRICT behavior seems a reasonably safe way of
preserving as much backwards compatibility as we can.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> As far as this question, seems with no RESTRICT/CASCADE, it fails, with
> RESTRICT it drops the trigger, and with CASCADE it drops the referencing
> table.  Is that accurate?

Not at all.  CASCADE would drop the foreign key constraint (including
the triggers that implement it), but not the other table.  In my mind
the issue is whether RESTRICT mode should do the same, or report an
error.

I'm not eager to accept the idea that DROP-without-either-option should
behave in some intermediate fashion.  I want it to be the same as
RESTRICT.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > As far as this question, seems with no RESTRICT/CASCADE, it fails, with
> > RESTRICT it drops the trigger, and with CASCADE it drops the referencing
> > table.  Is that accurate?
> 
> Not at all.  CASCADE would drop the foreign key constraint (including
> the triggers that implement it), but not the other table.  In my mind
> the issue is whether RESTRICT mode should do the same, or report an
> error.
> 
> I'm not eager to accept the idea that DROP-without-either-option should
> behave in some intermediate fashion.  I want it to be the same as
> RESTRICT.

Sounds good to me, and I don't think we need to require RESTRICT just
because the standard says so.  Does the standard require RESTRICT for
every DROP or just drops that have foreign keys?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> > As far as this question, seems with no RESTRICT/CASCADE, it fails, with
> > RESTRICT it drops the trigger, and with CASCADE it drops the referencing
> > table.  Is that accurate?
>
> Not at all.  CASCADE would drop the foreign key constraint (including
> the triggers that implement it), but not the other table.  In my mind
> the issue is whether RESTRICT mode should do the same, or report an
> error.
>
> I'm not eager to accept the idea that DROP-without-either-option should
> behave in some intermediate fashion.  I want it to be the same as
> RESTRICT.

I think that an unqualified drop should restrict and fail to drop if there's
a foreign key.  Any app that lets people do a drop is probably already
checking for error conditions.  Hence, it's just another error condition.

Chris

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Sounds good to me, and I don't think we need to require RESTRICT just
> because the standard says so.  Does the standard require RESTRICT for
> every DROP or just drops that have foreign keys?

Syntactically, it requires RESTRICT or CASCADE on *every* form of DROP.
I don't think we're willing to do that...
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Sounds good to me, and I don't think we need to require RESTRICT just
> > because the standard says so.  Does the standard require RESTRICT for
> > every DROP or just drops that have foreign keys?
> 
> Syntactically, it requires RESTRICT or CASCADE on *every* form of DROP.
> I don't think we're willing to do that...

Yuck, or RESTRICT|CASCADE yuck.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

I think that we are getting into two or three issues here.  If I may:
(1) Is DROP TABLE <foo> acceptable by the standard?(2) Should we break "old" functionality?(3) assuming we support the
oldsyntax:     should DROP TABLE <foo> be functionally the same as     DROP TABLE <foo> RESTRICT(4) does that mean that
Thata DROP TABLE <foo> RESTRICT fails on
 
foreign key reference to foo.

Answers from my experience and from my reading of the standard.  (See earlier
note, I encourage you to determine if I am mistaken, the stand is often hard to
read.)
(1) It is ONLY acceptable (see conformance note) if you do not support
CASCADE.  If you support CASCADE, you must indicate CASCADE or RESTRICT.  This
isn't an "optional parameter".  So, no -- the suggestion that "DROP TABLE <foo>"
is now valid syntax given the CASCADE functionality breaks the standard.
Vendors <sarcasm> occasionally </sarcasm> decide to break the standard.
(currently the standards node seems to be down -- I was going to verify that
nothing in 2004 has yet to change this syntax.  That verification will have to
come tomorrow (assuming it comes back up).)
(2) I am new here.  This is really an answer that should be driven by
the user community.  My experience doing database engineering allows me to argue
both viewpoints.  I would claim, as I have seen others hint at, that "drop
table" operations are not heavily embedded into application code and this is one
case where backward compatibility may not be as important as clarity and
standard conformance.  Tom seems to have made a clear statement that there is A
DESIRE to not break old implementations directly with a syntax restriction.  I
don't know if you have a mechanism to provide a warning that this is a
deprecated feature -- if so, that may be "a good idea"(tm).  The reasoning
behind the standard thrust here is that a maintainer should explicitly know what
the drop command will accomplish.
(3) I believe Tom is right in that you don't want to do something "half
way".  It should behave like RESTRICT.  There are commercial examples for this
behavior (Oracle Rdb (aka Digital Rdb) is the one that immediately comes to
mind; Oracle 9i's CASCADE CONSTRAINTS|<nothing> is another variant).
(4) yes seems to be the general answer from all corners.  (I agree)

Dana
(BTW: while I mentioned this to some of the core folks, I actually do
participate on the SQL standard for the US.  NO, that doesn't make me the last
word when it comes to the standard (that's Jim Melton's job :-), but I do have
access to all the documents.  I have been developing commercial DB engines for
some so, I have some experience in the field but I am only just starting to hack
PostgreSQL.)

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wednesday, July 10, 2002 11:25 PM
To: Tom Lane
Cc: Jan Wieck; Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > As far as this question, seems with no RESTRICT/CASCADE, it fails, with
> > RESTRICT it drops the trigger, and with CASCADE it drops the referencing
> > table.  Is that accurate?
> 
> Not at all.  CASCADE would drop the foreign key constraint (including
> the triggers that implement it), but not the other table.  In my mind
> the issue is whether RESTRICT mode should do the same, or report an
> error.
> 
> I'm not eager to accept the idea that DROP-without-either-option should
> behave in some intermediate fashion.  I want it to be the same as
> RESTRICT.

Sounds good to me, and I don't think we need to require RESTRICT just
because the standard says so.  Does the standard require RESTRICT for
every DROP or just drops that have foreign keys?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

IMHO, I believe that the standard should be adhered to if at all possible.
Since Cascade was added, Restrict must be the default is my reading of the
standard.

So that everyone can talk from the same sheet, the 1999 SQL Standard for DROP
TABLE follows:


11.20 <drop table statement>
Function
Destroy a table.
Format
<drop table statement> ::=
DROP TABLE <table name> <drop behavior>
Syntax Rules
1) Let T be the table identified by the <table name> and let TN be that <table
name>.
2) The schema identified by the explicit or implicit schema name of the <table
name> shall include the descriptor of T.
3) T shall be a base table.
4) T shall not be a declared local temporary table.
5) If RESTRICT is specified, then T shall not have any subtables.
6) If RESTRICT is specified, then T shall not be referenced in any of the
following:
a) The <query expression> of any view descriptor.
b) The <search condition> of any table check constraint descriptor of any table
other than T or the <search condition> of a constraint descriptor of an
assertion descriptor.
c) The table descriptor of the referenced table of any referential constraint
descriptor of any table other than T.
d) The scope of the declared type of a column of a table other than T and of the
declared type of an SQL parameter of any SQL-invoked routine.
e) The <SQL routine body> of any SQL-invoked routine descriptor.
f) The scope of the declared type of an SQL parameter of any SQL-invoked
routine.
g) The trigger action of any trigger descriptor.
NOTE 197 - If CASCADE is specified, then such referenced objects will be dropped
by the execution of the <revoke statement> specified in the General Rules of
this Subclause.
7) Let A be the <authorization identifier> that owns the schema identified by
the <schema name> of the table identified by TN.
8) Let the containing schema be the schema identified by the <schema name>
explicitly or implicitly contained in <table name>.
Access Rules
1) The enabled authorization identifiers shall include A.
General Rules
1) Let ST be the <table name> of any subtable of T. The following <drop table
statement> is effectively executed without further Access Rule checking:
DROP TABLE ST CASCADE
2) If T is a referenceable table, then:
a) Let ST be structured type associated with T.
b) Let RST be the reference type whose referenced type is ST.
c) Let DT be any table whose table descriptor includes a column descriptor that
generally includes a field descriptor, an attribute descriptor, or an array
descriptor that includes a reference type descriptor RST whose scope includes
TN.
NOTE 198 - A descriptor that ''generally includes'' another descriptor is
defined in Subclause 6.2.4, "Descriptors", in ISO/IEC 9075-1.
d) Let DTN be the name of the table DT.
e) Case:
i) If DT is a base table, then the following <drop table statement> is
effectively executed without further Access Rule checking:
DROP TABLE DTN CASCADE
ii) Otherwise, the following <drop view statement> is effectively executed
without further Access Rule checking:
DROP VIEW DTN CASCADE
3) For every supertable of T, every superrow and every subrow of every row of T
is effectively deleted at the end of the SQL-statement, prior to the checking of
any integrity constraints.
NOTE 199 - This deletion creates neither a new trigger execution context nor the
definition of a new state change in the current trigger execution context.
4) The following <revoke statement> is effectively executed with a current
authorization identifier of ''_SYSTEM'' and without further Access Rule
checking:
REVOKE ALL PRIVILEGES ON TN FROM A CASCADE
5) Let R be any SQL-invoked routine whose routine descriptor contains the <table
name> of T in the <SQL routine body>. Let SN be the <specific name> of R. The
following <drop routine statement> is effectively executed without further
Access Rule checking:
DROP SPECIFIC ROUTINE SN CASCADE
6) For each direct supertable DST of T, the table name of T is removed from the
list of table names of direct subtables of DST that is included in the table
descriptor of DST.
7) The descriptor of T is destroyed.
Conformance Rules
1) Without Feature F032, ''CASCADE drop behavior'', a <drop behavior> of CASCADE
shall not be specified in <drop table statement>.



-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Wednesday, July 10, 2002 7:35 PM
To: Tom Lane
Cc: Stephan Szabo; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Should this require CASCADE?


Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 10 Jul 2002, Tom Lane wrote:
> >> DROP TABLE foo RESTRICT;
> >>
> >> Should this succeed?  Or should it be necessary to say DROP CASCADE to
> >> get rid of the foreign-key reference to foo?
> 
> > I think the above should fail.  If someone was adding restrict since it
> > was optional, I'd guess they were doing so in advance for the days when
> > we'd actually restrict the drop.
> 
> Sorry if I wasn't clear: we never had the RESTRICT/CASCADE syntax at all
> until now.  What I'm intending though is that DROP with no option will
> default to DROP RESTRICT, which means that a lot of cases that used to
> be "gotchas" will now fail until you say CASCADE.  I wrote RESTRICT in
> my example just to emphasize that the intended behavior is RESTRICT.

I think the idea was to have it default to CASCADE for this release, not
to break existing code right away. Then 7.3 is transition time and
RESTRICT will be the default from the next release on.

If so, this has to go into the release notes.


Jan

> 
> So if you prefer, imagine same example but you merely say
>         DROP TABLE foo;
> Does your answer change?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Groff, Dana wrote:
> I think that we are getting into two or three issues here.  If I may:
> 
>     (1) Is DROP TABLE <foo> acceptable by the standard?
>     (2) Should we break "old" functionality?
>     (3) assuming we support the old syntax: 
>         should DROP TABLE <foo> be functionally the same as 
>         DROP TABLE <foo> RESTRICT
>     (4) does that mean that That a DROP TABLE <foo> RESTRICT fails on
> foreign key reference to foo.
> 
> Answers from my experience and from my reading of the standard.  (See earlier
> note, I encourage you to determine if I am mistaken, the stand is often hard to
> read.)
> 
>     (1) It is ONLY acceptable (see conformance note) if you do not support
> CASCADE.  If you support CASCADE, you must indicate CASCADE or RESTRICT.  This
> isn't an "optional parameter".  So, no -- the suggestion that "DROP TABLE <foo>"
> is now valid syntax given the CASCADE functionality breaks the standard.
> Vendors <sarcasm> occasionally </sarcasm> decide to break the standard.
> (currently the standards node seems to be down -- I was going to verify that
> nothing in 2004 has yet to change this syntax.  That verification will have to
> come tomorrow (assuming it comes back up).)

Hard to argue why we should invalidate all preexisting SQL books by
rejecting DROP TABLE tab.  If I create a table, and then want to drop
it, why should I have to put another noise word in there to make the
server happy.  Now, if someone wanted to say CASCADE|RESTRICT was
required for DROP _only_ if there is some foreign key references to the
table, I would be OK with that, but that's not what the standard says.

Hard to imagine what the standards people were thinking on this one.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Now, if someone wanted to say CASCADE|RESTRICT was
> required for DROP _only_ if there is some foreign key references to the
> table, I would be OK with that, but that's not what the standard says.

But in fact that is not different from what I propose to do.  Consider
what such a rule really means:* if no dependencies exist for the object, go ahead and delete.* if dependencies exist,
complain.
How is that different from "the default behavior is RESTRICT"?
        regards, tom lane

I think that is the proper behavior Tom.

Also I agree with Bruce that this might be an oversight in the standard.  That
is why standards evolve.  As I write this I am also sending a note to H2 asking
about this very issue.  The latest working draft still has this construct.

Dana

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, July 11, 2002 12:36 PM
> To: Bruce Momjian
> Cc: Groff, Dana; Jan Wieck; Stephan Szabo; 
> pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Should this require CASCADE? 
> 
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, if someone wanted to say CASCADE|RESTRICT was
> > required for DROP _only_ if there is some foreign key 
> references to the
> > table, I would be OK with that, but that's not what the 
> standard says.
> 
> But in fact that is not different from what I propose to do.  Consider
> what such a rule really means:
>     * if no dependencies exist for the object, go ahead and delete.
>     * if dependencies exist, complain.
> How is that different from "the default behavior is RESTRICT"?
> 
>             regards, tom lane
> 

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, if someone wanted to say CASCADE|RESTRICT was
> > required for DROP _only_ if there is some foreign key references to the
> > table, I would be OK with that, but that's not what the standard says.
> 
> But in fact that is not different from what I propose to do.  Consider
> what such a rule really means:
>     * if no dependencies exist for the object, go ahead and delete.
>     * if dependencies exist, complain.
> How is that different from "the default behavior is RESTRICT"?

No, I support your ideas.  We are allowing RESTRICT to be the default.

What I was saying is that the standard _requiring_ RESTRICT or CASCADE
was really strange, and I could understand such a requirement only if
foreign keys existed on the table.  Requiring it when no foreign keys
exist is really weird.  I agree we should default to RESTRICT in all
cases.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

With all this dependency stuff, what happens with the ALTER TABLE / DROP NOT
NULL syntax we came up with?

Maybe we should allow RESTRICT/CASCADE on that syntax and if restrict is
specified, you can't drop it if a primary key depends on it and if cascade
is specified it will drop the primary key...

Just for consistency...

Also, when talking about whether or not the index supporting a constraint
should be sort of 'hidden' from the user, should not we change pg_dump to
dump unique indices using the ALTER TABLE syntax, rather than the CREATE
UNIQUE INDEX syntax?  Otherwise this information will be lost.

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> With all this dependency stuff, what happens with the ALTER TABLE / DROP NOT
> NULL syntax we came up with?

Nothing, AFAICS.  NOT NULL doesn't have any dependency implications.

> Also, when talking about whether or not the index supporting a constraint
> should be sort of 'hidden' from the user, should not we change pg_dump to
> dump unique indices using the ALTER TABLE syntax, rather than the CREATE
> UNIQUE INDEX syntax?  Otherwise this information will be lost.

I thought we did that already.  We do need to tweak pg_dump's handling
of foreign keys though --- dumping some trigger definitions is no longer
the right thing.

It would be interesting to see if we can reasonably reverse-engineer
a foreign-key-constraint structure given the CREATE TRIGGER commands
that are actually going to be present in existing pg_dump scripts.
        regards, tom lane

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > With all this dependency stuff, what happens with the ALTER
> TABLE / DROP NOT
> > NULL syntax we came up with?
>
> Nothing, AFAICS.  NOT NULL doesn't have any dependency implications.

What about the primary keys that I mentioned?  In the current
implementation, it's restrict-only.

Chris

> > Also, when talking about whether or not the index supporting a
> constraint
> > should be sort of 'hidden' from the user, should not we change
> pg_dump to
> > dump unique indices using the ALTER TABLE syntax, rather than the CREATE
> > UNIQUE INDEX syntax?  Otherwise this information will be lost.
>
> I thought we did that already.

Nope: (CVS-HEAD)

test=# create table test (a int4 unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'test_a_key' for
table 'test'
CREATE TABLE
test=# \q
chriskl@alpha:~$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - chriskl

SET search_path = public, pg_catalog;

--
-- TOC Entry ID 2 (OID 16575)
--
-- Name: test Type: TABLE Schema: public Owner: chriskl
--

CREATE TABLE "test" (       "a" integer
);

--
-- Data for TOC Entry ID 4 (OID 16575)
--
-- Name: test Type: TABLE DATA Schema: public Owner: chriskl
--


COPY "test" FROM stdin;
\.
--
-- TOC Entry ID 3 (OID 16577)
--
-- Name: test_a_key Type: INDEX Schema: public Owner: chriskl
--

CREATE UNIQUE INDEX test_a_key ON test USING btree (a);

I think that if an index is unique and uses btree, it should be dumped as an
alter table statement?

Chris

The answer from H2 (Jim Melton).

When this feature was being voted on, some vendors had "cascade" as a default,
others had "restrict".  So, the compromise was not to define a default.
<grumble grumble>

As such providing a "default" is a vendor extension and compliance simply
requires we also support the standard syntax.

Dana

> -----Original Message-----
> From: Groff, Dana [mailto:Dana.Groff@filetek.com]
> Sent: Thursday, July 11, 2002 12:43 PM
> To: 'Tom Lane'; Bruce Momjian
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Should this require CASCADE? 
> 
> 
> I think that is the proper behavior Tom.
> 
> Also I agree with Bruce that this might be an oversight in 
> the standard.  That
> is why standards evolve.  As I write this I am also sending a 
> note to H2 asking
> about this very issue.  The latest working draft still has 
> this construct.
> 
> Dana
> 
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Thursday, July 11, 2002 12:36 PM
> > To: Bruce Momjian
> > Cc: Groff, Dana; Jan Wieck; Stephan Szabo; 
> > pgsql-hackers@postgresql.org
> > Subject: Re: [HACKERS] Should this require CASCADE? 
> > 
> > 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Now, if someone wanted to say CASCADE|RESTRICT was
> > > required for DROP _only_ if there is some foreign key 
> > references to the
> > > table, I would be OK with that, but that's not what the 
> > standard says.
> > 
> > But in fact that is not different from what I propose to 
> do.  Consider
> > what such a rule really means:
> >     * if no dependencies exist for the object, go ahead and delete.
> >     * if dependencies exist, complain.
> > How is that different from "the default behavior is RESTRICT"?
> > 
> >             regards, tom lane
> > 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>