Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Berend Tober <btober@xxxxxxxxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux