"Oliver Elphick" <olly@lfix.co.uk> writes:
> (Mind you, I think I have not yet got a reliable way of finding the
> ultimate ancestor of an inherited constraint. Is it actually possible to
> do this with queries or do we have to add a boolean flag to pg_relcheck
> to be set where the constraint is/is not inherited?)
In fact, I was about to point out that the query you were describing
couldn't possibly give you a reliable answer, quite independent of
whether the backend is implementing it properly or not.
Consider
CREATE TABLE parent1 (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE child1 (CONSTRAINT c2 CHECK (i1 > 4)) INHERITS (parent1);
CREATE TABLE child2 (CONSTRAINT c2 CHECK (i1 > 4)) INHERITS (parent1);
This will give us a pg_relcheck like
rcrelid rcname rcbin rcsrc
parent1 c1 gobbledegook i1 > 0child1 c1 gobbledegook i1 > 0child2 c1 gobbledegook
i1 > 0child1 c2 gobbledegook i1 > 4child2 c2 gobbledegook i1 > 4
(where I've written table names in place of numeric OIDs for rcrelid).
Now child2 did not inherit c2 from child1, but child1 has a lower OID
than child2, so your test would mistakenly omit c2 from child2's
definition.
It seems to me that the correct way to do this is to compare each of a
table's constraints against its immediate parent's constraints, and omit
from the child any constraints that have the same rcname AND the same
rcsrc as a constraint of the parent. (You need not look at anything
other than the immediate parent, because constraints inherited from
more distant ancestors will also be listed for the parent.)
There is a case that pg_relcheck does not allow you to distinguish,
and that is whether or not the child definition was actually written
with a redundant constraint:
CREATE TABLE parentx (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE childx (CONSTRAINT c1 CHECK (i1 > 0)) INHERITS (parentx);
Unless we modify pg_relcheck, pg_dump will have to dump this as simply
CREATE TABLE parentx (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE childx () INHERITS (parentx);
since it cannot tell that childx's constraint wasn't simply inherited.
However, it's not clear to me that suppression of redundant constraints
is a bad thing ;-)
regards, tom lane