Inherited constraints and search paths (was Re: Preserving data after updates)

Berend Tober <btober(at)seaworthysys(dot)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