Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:

[Tom Lane]

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 (was Re:

[Tom Lane]

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