Re: Table constraint ordering disrupted by pg_dump

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 17:51 3/04/01 -0400, Tom Lane wrote:
>> Rather than using "c.rcname = pg_relcheck.rcname" as part of the match
>> condition, consider
>> (c.rcname = pg_relcheck.rcname OR
>> (c.rcname[0] = '$' AND pg_relcheck.rcname[0] = '$'))
>> so that any two nameless constraints will be considered duplicate if
>> their rcsrcs match.

> Looks reasonable, but users can define constraints with names starting with
> '$', so there is a small chance we will discard a redundant constraint,
> which is probably OK.

Actually, I was seriously considering the fascist alternative: if the
rcsrc fields match then discard the child's constraint, never mind what
its name is. Functionally the result is the same...

> BTW, it looks like 'ALTER TABLE ADD CONSTRAINT' on a parent does not affect
> the already created children. Is this intended behaviour? It seems like a
> but and it certainly will cause an issue for pg_dump.

This seems a bug, but I do not think we should address it for 7.1.
TODO list material.

>> PS: Philip, it seems to me that lines 2071-2121 in pg_dump.c are largely
>> a waste of time, since the subsequent query to fetch the constraints
>> will do all the same work over again. Just have to relax the check at
>> line 2157 to allow ntups2 <= ncheck, and update ncheck after that.

> I was planning to clean up that area when 7.1 was out the door since I
> assumed that there was an obscure reason for it's existence.

> Looking at the code more closely, I think it is part of the sanity check
> pg_dump tries to do. The first piece of SQL finds inherited constraints,
> and the second one finds non-inherited constraints, but the way the SQL is
> structured, I can't really see how they would fail to come to the correct
> total, so it's value as a sanity check seems minimal. Do you agree, or
> should I leave it in?

My thought is that it's useless as a sanity check (since there's no
orthogonal-condition test there), and it does pose a maintenance problem.
For example, to adjust the name-matching condition being discussed
above, you'd have to remember to change both queries. Change only one
and you're in trouble. Given that, the downside is bigger than the
upside.

regards, tom lane