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

Wed, 10 Jul 2002 18:33:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Should this require CASCADE?
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.


Wed, 10 Jul 2002 15:45:24 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Should this require CASCADE?
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?

			regards, tom lane

Wed, 10 Jul 2002 18:59:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should this require CASCADE? 
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?).



Wed, 10 Jul 2002 16:27:39 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Should this require CASCADE? 
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).






10 Jul 2002 19:27:47 -0400
Rod Taylor <rbt@zort.ca>
Re: 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 #

Wed, 10 Jul 2002 19:34:53 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Should this require CASCADE?
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

Wed, 10 Jul 2002 22:20:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should this require CASCADE? 
> 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


Thu, 11 Jul 2002 10:24:45 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Should this require CASCADE? 
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 all light.  --XTC


Thu, 11 Jul 2002 11:55:35 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Should this require CASCADE?
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, Pennsylvania 19026

Wed, 10 Jul 2002 23:05:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Should this require CASCADE?
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

Wed, 10 Jul 2002 23:19:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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, Pennsylvania 19026

Wed, 10 Jul 2002 23:24:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Should this require CASCADE?
> > 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


Thu, 11 Jul 2002 11:30:41 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Should this require CASCADE? 
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

Wed, 10 Jul 2002 23:31:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should this require CASCADE? 
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, Pennsylvania 19026

Wed, 10 Jul 2002 23:33:27 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Should this require CASCADE?