Lists: | pgsql-hackerspgsql-patches |
---|
From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-patches(at)postgresql(dot)org |
Subject: | Inherited Constraints |
Date: | 2005-12-07 21:24:05 |
Message-ID: | 1133990645.2906.969.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Following patch implements record of whether a constraint is inherited
or not, and prevents dropping of inherited constraints.
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.
Current patch passes make check on cvstip, applies cleanly.
Further patch will utilise this new knowledge to reduce the number of
tests made during constraint_exclusion.
Best Regards, Simon Riggs
Attachment | Content-Type | Size |
---|---|---|
coninh.patch | text/x-patch | 13.8 KB |
From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-08 11:10:28 |
Message-ID: | 1134040228.2906.1111.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
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?
Which implementation should I pick (or another)?
> Further patch will utilise this new knowledge to reduce the number of
> tests made during constraint_exclusion.
Best Regards, Simon Riggs
From: | Hannu Krosing <hannu(at)skype(dot)net> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-08 17:15:01 |
Message-ID: | 1134062102.3967.10.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Ühel kenal päeval, N, 2005-12-08 kell 11:10, kirjutas Simon Riggs:
> 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?
It would be logical to do the same as DROP TABLE does, i.e (2).
hannu=# create table parent(i int);
CREATE TABLE
hannu=# create table child() inherits(parent);
CREATE TABLE
hannu=# drop table parent;
NOTICE: table child depends on table parent
ERROR: cannot drop table parent because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
hannu=#
Maybe there should be another option in addition to CASCADE, say
DISINHERIT, which leaves all child constraints as heads of new
ingeritance hierarchies. DROP CASCADE + ADD BACK ALL CHILD CONSTRAINTS
may be prohibitively expensive for biggish tables.
Another nice (but no doubt more complex) thing would be ability to add
multiple constraints at once, needing only one seqscan to check for
compliance with added constraints and/or making constraint checks
smarter, so that for.ex. "ADD CONSTRAINT CHECK i > 0" could make use of
index on i instead of doing a seqscan. Or if there is a constraint
"CHECK i > 0" then adding another like "CHECK i > -1" would not need to
check actual data either.
> Which implementation should I pick (or another)?
>
> > Further patch will utilise this new knowledge to reduce the number of
> > tests made during constraint_exclusion.
So will hierarchical inheritance be the thing to do to take advantage of
i then ?
year
+- month1
|+-day1
|+-day2
.....
|\-day31
+- month2
etc.
btw, will your DROP patch support multiple inheritance ?
-----------
Hannu
From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | Hannu Krosing <hannu(at)skype(dot)net> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-08 17:49:48 |
Message-ID: | 1134064188.63851.123.camel@home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
> Another nice (but no doubt more complex) thing would be ability to add
> multiple constraints at once, needing only one seqscan to check for
> compliance with added constraints and/or making constraint checks
> smarter, so that for.ex. "ADD CONSTRAINT CHECK i > 0" could make use of
> index on i instead of doing a seqscan. Or if there is a constraint
> "CHECK i > 0" then adding another like "CHECK i > -1" would not need to
> check actual data either.
Check out the comma in alter table.
ALTER TABLE tab ADD COLUMN serial NOT NULL UNIQUE,
ADD CHECK (foo > 24),
ADD CHECK (baz < 18),
ADD COLUMN integer NOT NULL DEFAULT 32;
Table tab (and each of the tables that inherits from it) is scanned and
rewritten once.
I believe this was added for 8.0.
--
From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-08 22:47:08 |
Message-ID: | 1134082028.2906.1141.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
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
From: | Trent Shipley <tshipley(at)deru(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-09 01:29:01 |
Message-ID: | 200512081829.02377.tshipley@deru.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
On Thursday 2005-12-08 15:47, Simon Riggs wrote:
> 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
Why should dropping the child constraint fail?
Child tables are supposed to be able to over-ride parent constraints.
Dropping a parent's constraint sounds like just a way to over-ride a
constraint with no constraint at all. (Making the column unconstrained.)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | tshipley(at)deru(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2005-12-09 01:43:05 |
Message-ID: | 5648.1134092585@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Trent Shipley <tshipley(at)deru(dot)com> writes:
> Child tables are supposed to be able to over-ride parent constraints.
Says who?
If we allow that, then reading the parent table will produce rows that
violate the parent's constraint. This does not seem very wise.
regards, tom lane
From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: Inherited Constraints |
Date: | 2005-12-13 22:31:29 |
Message-ID: | 1134513089.27873.112.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
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.
>
> What it doesn't do:
Patch withdrawn while I work on the final item.
Best Regards, Simon Riggs
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-03 04:08:45 |
Message-ID: | 200603030408.k2348j712285@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
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. +
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-06 17:12:40 |
Message-ID: | 200603061712.k26HCeX26727@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
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 parent table
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. +
From: | Hannu Krosing <hannu(at)skype(dot)net> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-06 21:53:17 |
Message-ID: | 1141681997.3810.4.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Ühel kenal päeval, E, 2006-03-06 kell 12:12, kirjutas Bruce Momjian:
> 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 parent table
>
> so I think we now have all the failure cases documented.
If you want to be consistent, then ALTER TABLE ONLY ADD CONSTRAINT ..
should also be forbidden, so you can't create non-inherited constraints
---------------
Hannu
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Hannu Krosing <hannu(at)skype(dot)net> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-06 22:25:16 |
Message-ID: | 200603062225.k26MPGI21439@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Hannu Krosing wrote:
> ?hel kenal p?eval, E, 2006-03-06 kell 12:12, kirjutas Bruce Momjian:
> > 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 parent table
> >
> > so I think we now have all the failure cases documented.
>
> If you want to be consistent, then ALTER TABLE ONLY ADD CONSTRAINT ..
> should also be forbidden, so you can't create non-inherited constraints
I don't have a problem with creating ONLY constraints on parents and
children. We just don't want them to be removed/modified if they are
shared.
--
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. +
From: | Hannu Krosing <hannu(at)skype(dot)net> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-08 12:53:44 |
Message-ID: | 1141822424.4009.5.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
Ühel kenal päeval, E, 2006-03-06 kell 17:25, kirjutas Bruce Momjian:
> Hannu Krosing wrote:
> > ?hel kenal p?eval, E, 2006-03-06 kell 12:12, kirjutas Bruce Momjian:
> > > 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 parent table
> > >
> > > so I think we now have all the failure cases documented.
> >
> > If you want to be consistent, then ALTER TABLE ONLY ADD CONSTRAINT ..
> > should also be forbidden, so you can't create non-inherited constraints
>
> I don't have a problem with creating ONLY constraints on parents and
> children. We just don't want them to be removed/modified if they are
> shared.
Well, when you delete a constraint from child, the constraint becomes an
"ONLY" constraint on parent. If you allow ONLY constraints on parents,
then why disallow dropping them from childs ?
IIRC the original complaint about being able to drop constraints from
children was that inherited tables not being bound by constraints on
parents was unexpected/broken.
I.E when you have
CREATE TABLE T(i int check (i>0));
then you would be really surprised by getting -1 out from that table.
---------------
Hannu
From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Hannu Krosing <hannu(at)skype(dot)net> |
Cc: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCHES] Inherited Constraints |
Date: | 2006-03-08 15:35:01 |
Message-ID: | 20060308073214.W77062@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-patches |
On Wed, 8 Mar 2006, Hannu Krosing wrote:
> hel kenal peval, E, 2006-03-06 kell 17:25, kirjutas Bruce Momjian:
> > Hannu Krosing wrote:
> > > ?hel kenal p?eval, E, 2006-03-06 kell 12:12, kirjutas Bruce Momjian:
> > > > 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 parent table
> > > >
> > > > so I think we now have all the failure cases documented.
> > >
> > > If you want to be consistent, then ALTER TABLE ONLY ADD CONSTRAINT ..
> > > should also be forbidden, so you can't create non-inherited constraints
> >
> > I don't have a problem with creating ONLY constraints on parents and
> > children. We just don't want them to be removed/modified if they are
> > shared.
>
> Well, when you delete a constraint from child, the constraint becomes an
> "ONLY" constraint on parent.
Only if there's a single child, otherwise you have a partially-ONLY
constraint unless you made it ONLY constraints on the parent and all other
children (but then removing the parent constraint wouldn't remove it from
the other children presumably).
> If you allow ONLY constraints on parents, then why disallow dropping
> them from childs ?
I agree with this in any case. I think both are fairly broken.