Re: [PATCHES] Inherited Constraints - Mailing list pgsql-hackers

Added to TODO:
     o Prevent parent tables from altering or dropping constraints       like CHECK that are inherited by child tables
       Dropping constraints should only be possible with CASCADE.

and we already have this in TODO:
       o %Prevent child tables from altering or dropping constraints         like CHECK that were inherited from the
parenttable
 

so I think we now have all the failure cases documented.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 
> Where are we on this patch?  My testing shows it is still shows we have
> a problem:
>     
>     test=> CREATE TABLE x(y INT CHECK(y > 0));
>     CREATE TABLE
>     test=> CREATE TABLE z(a INT) inherits (x);
>     CREATE TABLE
>     test=> ALTER TABLE z DROP CONSTRAINT "x_y_check";
>     ALTER TABLE
>     test=> ALTER TABLE x DROP CONSTRAINT "x_y_check";
>     ALTER TABLE
> 
> Deleting the parent constraint first does not require CASCADE, as it
> should, I think:
>     
>     test=> CREATE TABLE x(y INT CHECK(y > 0));
>     CREATE TABLE
>     test=> CREATE TABLE z(a INT) inherits (x);
>     CREATE TABLE
>     test=> ALTER TABLE x DROP CONSTRAINT "x_y_check";
>     ALTER TABLE
>     test=> ALTER TABLE z DROP CONSTRAINT "x_y_check";
>     ERROR:  CONSTRAINT "x_y_check" does NOT exist
> 
> ---------------------------------------------------------------------------
> 
> Simon Riggs wrote:
> > On Thu, 2005-12-08 at 11:10 +0000, Simon Riggs wrote:
> > > On Wed, 2005-12-07 at 21:24 +0000, Simon Riggs wrote:
> > > > Following patch implements record of whether a constraint is inherited
> > > > or not, and prevents dropping of inherited constraints.
> > > 
> > > Patch posted to -patches list.
> > > 
> > > > What it doesn't do:
> > > > It doesn't yet prevent dropping the parent constraint, which is wrong,
> > > > clearly, but what to do about it?
> > > > 1. make dropping a constraint drop all constraints dependent upon it
> > > > (without any explicit cascade)
> > > > 2. add a new clause to ALTER TABLE .... DROP CONSTRAINT .... CASCADE 
> > > > 
> > > > I prefer (1), since it is SQL Standard compliant, easier to remember and
> > > > automatic de-inheritance is the natural opposite of the automatic
> > > > inheritance process.
> > > 
> > > Comments, please -hackers?
> > 
> > Late night hacking again....
> > 
> > ALTER TABLE .... DROP CONSTRAINT .... CASCADE
> > 
> > does of course already exist, so the following should cause dependency
> > violation ERRORs:
> > - omitting the CASCADE when attempting to delete parent constraint
> > - attempting to drop the child constraint
> > 
> > Best Regards, Simon Riggs
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> > 
> >                http://archives.postgresql.org
> > 
> 
> -- 
>   Bruce Momjian   http://candle.pha.pa.us
>   SRA OSS, Inc.   http://www.sraoss.com
> 
>   + If your life is a hard drive, Christ can be your backup. +
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +