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).)

	(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, Pennsylvania 19026

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

Thu, 11 Jul 2002 00:59:48 -0400
"Groff, Dana" <Dana.Groff@filetek.com>
Re: Should this require CASCADE?
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, Pennsylvania 19026

Thu, 11 Jul 2002 12:27:30 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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

Thu, 11 Jul 2002 12:36:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should this require CASCADE? 
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, Pennsylvania 19026

Thu, 11 Jul 2002 12:46:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Should this require CASCADE?
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


Fri, 12 Jul 2002 09:29:09 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Should this require CASCADE? 
"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

Thu, 11 Jul 2002 23:04:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should this require CASCADE? 
> "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


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


Fri, 12 Jul 2002 11:12:19 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Should this require CASCADE? 