Thread: Inherited constraints and search paths (was Re: [GENERAL] Preserving data after updates)

Berend Tober <btober@seaworthysys.com> writes:
> Now what, oh most wise one?

OK, now I finally get the point: you are creating child tables in
different schemas than their parents live in.  This creates a problem
because reverse-listing of the constraints varies depending on what
the search path is.

An example in CVS tip is:

create function foo(text) returns bool as 'select true' language sql;
create table t1(f1 text constraint c1 check (foo(f1)));
create schema s1;
create table s1.t2() inherits(public.t1);

pg_dump yields this:

SET search_path = public, pg_catalog;

CREATE TABLE t1 (
    f1 text,
    CONSTRAINT c1 CHECK (foo(f1))
);

...

SET search_path = s1, pg_catalog;

CREATE TABLE t2 (CONSTRAINT c1 CHECK (public.foo(f1))
)
INHERITS (public.t1);

It's the same constraint, but the different reverse-listing fools
pg_dump into assuming that it's different.

At the moment I'm not seeing any really nice way to fix this.

A short-term workaround is to hack pg_dump so that it doesn't compare
the constraint expressions at all, but just assumes that a child table's
constraint is the same as the parent's if the constraint name matches.
You can of course break this by manually dropping the child constraint
and creating a different one of the same name --- but does anyone do
that in practice?  (Note: the code in pg_dump seems to think that there
is something special about constraint names beginning with '$', but
in quick tests I don't see the system generating constraint names of
that kind as far back as 7.0, which is the oldest server version pg_dump
now claims to support.  So I think that is long-dead code, and that a
comparison of constraint names is probably sufficient in practice.)

It can be argued that we should actually prohibit dropping inherited
constraints, which'd eliminate that problem.  I seem to recall that this
has come up before and we explicitly decided against making such a
restriction ... but given that a dump/restore will cause the inherited
constraint to come back anyway, it can hardly be claimed that we really
support dropping them.

Comments anyone?

            regards, tom lane

On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote:
> Berend Tober <btober@seaworthysys.com> writes:
> > Now what, oh most wise one?
>
> OK, now I finally get the point: you are creating child tables in
> different schemas than their parents live in.

...

> Comments anyone?

Best thing to do is to prevent people from creating child tables in
different schemas. Or at least advise against it.

Doing anything to restrict dropping of inherited constraints seems like
wasted effort and potentially annoying anyhow.

My partitioning efforts will eventually distinguish between inherited
and non-inherited constraints, since the former are fairly useless for
partition elimination. So I can't see a reason to care whether they are
there or not, if the user knows better.

Best Regards, Simon Riggs

Berend Tober <btober@seaworthysys.com> writes:
>> On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote:
>>> OK, now I finally get the point: you are creating child tables in
>>> different schemas than their parents live in.
>>
> The case in question was not one of the child table being in a different
> partition (do you mean schema?), although that arrangement was
> considered and rejected for other reasons during data base design.

I should clarify: the version of the pg_dump bug that still exists in
HEAD is triggered by putting the child table in a different schema than
the parent.  7.3 has different behavior --- offhand I think that in 7.3
the problem can occur if the child table is created while search_path is
set differently than it was when the parent was created.  (Of course,
across multiple pg_dump and reload cycles this may boil down to the same
thing.  But there are more ways to burn yourself given the 7.3
implementation.)

            regards, tom lane

Simon Riggs wrote:

>On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote:
>
>
>>Berend Tober <btober@seaworthysys.com> writes:
>>
>>
>>>Now what, oh most wise one?
>>>
>>>
>>OK, now I finally get the point: you are creating child tables in
>>different schemas than their parents live in.
>>
>>
>
>...
>
>
>>Comments anyone?
>>
>>
>
>Best thing to do is to prevent people from creating child tables in
>different schemas. Or at least advise against it.
>
>Doing anything to restrict dropping of inherited constraints seems like
>wasted effort and potentially annoying anyhow.
>
>My partitioning efforts will eventually distinguish between inherited
>and non-inherited constraints, since the former are fairly useless for
>partition elimination. So I can't see a reason to care whether they are
>there or not, if the user knows better.
>
>
The case in question was not one of the child table being in a different
partition (do you mean schema?), although that arrangement was
considered and rejected for other reasons during data base design. In
this implementation, a function called for a table constraint was in a
different schema. The function so called was defined in the public
scheme because it is a generic function that can be used by different
applications, and some tables are relevant only to specific applications
and so have there own, application-specific schema -- but they still can
make use of shared definitions, i.e., this particular function, which
are defined in the public schema.

Simon Riggs <simon@2ndquadrant.com> 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

On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> 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

Simon Riggs <simon@2ndquadrant.com> 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

Added to TODO:

    * Prevent child tables from altering constraints like CHECK that were
      inherited from the parent table


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

Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> 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 majordomo@postgresql.org 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