Обсуждение: 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 >