Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was
Added to TODO: * Prevent child tables from altering constraints like CHECK that were inherited from the parent table --- Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [HACKERS] Inherited constraints and search paths
On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? Thats a good case. I retract my comment on potentially annoying. If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. In that case I would also retract my comment on wasted effort. :-) If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. So the name of inherited constraints differs depending upon whether CREATE or ALTER puts them there. FWIW, fixing either of those won't get in my way on partitioning... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. Inherited and non-inherited constraints you mean? Probably. I hadn't thought through the details, but certainly there would need to be some better way of telling whether a constraint was inherited. If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. Hmm, that's weird: if you give an explicit name (add constraint foo) then it's used, but if you let the system generate the name it's not the same. I agree that was probably unintentional. Does anyone want to argue for keeping it this way? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table parent add check (f1 100); ALTER TABLE regression=# \d parent Table public.parent Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) parent_f1_check1 CHECK (f1 100) regression=# \d child Table public.child Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) child_f1_check CHECK (f1 100) Inherits: parent (This is, btw, another case that would break the current code for identifying inherited constraints in pg_dump. Given a positive marker for an inherited constraint, however, we wouldn't care. So I don't think we need to consider pg_dump in debating which behavior we like.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
