Re: Table constraint ordering disrupted by pg_dump

Patricia Holben <pholben@greatbridge.com> writes:
> improvement but as usual there is a related error.  In the constraints=20
> test, there is a table created "INSERT_CHILD" which has a constraint and=
> then inherits from "INSERT_TBL" the constraint "insert_con" and the=20
> unnamed check.  If you look at the expected output from this test, when =
> the inherited unnamed check fails, the error is $1.  After doing the=20
> dump/reload, this error is called $3.

Hm.  There are a couple of things going on here.  The one that may be
worth fixing is that pg_dump isn't reliably recognizing nameless
inherited constraints as duplicates.  It looks for matches on both
rcname and rcsrc, but the rcname may get reassigned (particularly if
there is multiple inheritance).  This will lead to multiple instances
of the same constraint, which is inefficient, and becomes more and more
so with repeated dump/reload cycles.

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.

The other thing is that the backend won't necessarily assign a nameless
constraint the same $-index in different tables, so even if pg_dump is
changed there's not a guarantee that you won't get different error
messages in the above example.  I don't consider that a bug, however.
If you're depending on the name of a constraint then you should name it.

            regards, tom lane

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.