Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Parser bug?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
Cc: hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Parser bug?
Date: 1998-11-24 16:17:58
Message-ID: 26279.911924278@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"Oliver Elphick" <olly(at)lfix(dot)co(dot)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 > 0
	child1		c1	gobbledegook	i1 > 0
	child2		c1	gobbledegook	i1 > 0
	child1		c2	gobbledegook	i1 > 4
	child2		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

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-11-24 16:25:46
Subject: Re: [HACKERS] pg_dump - segfault with -z option
Previous:From: Bruce MomjianDate: 1998-11-24 16:08:42
Subject: Re: [HACKERS] pg_dump - segfault with -z option

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group