Table constraint ordering disrupted by pg_dump

PostgreSQL Bugs List <pgsql-bugs@postgresql.org>

From: pgsql-bugs@postgresql.org
To: pgsql-bugs@postgresql.org
Date: 2001-04-02T20:20:38Z
Lists: pgsql-bugs
Tricia Holben (pholben@greatbridge.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Table constraint ordering disrupted by pg_dump

Long Description
When running a backup/restore version of the regression tests on 7.0.3 and 7.1RCD1 the following was noted:  when the create table command contained two constraints, the error messages would reflect that the constraints were being checked in a consistent order and error messages would show either the constraint name or $1 or $2 if no name was used for the constraint which generated the first error.  After dumping and restoring the database, the order was reversed.  As a result the meaning of error messages changed.

Sample Code
Original create statement:
CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
	y TEXT DEFAULT '-NULL-',
	z INT DEFAULT -1 * currval('insert_seq'),
	CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
	CHECK (x + z = 0));

NOTE - if the first constraint fails the error message reads:
ERROR:  ExecAppend: rejected due to CHECK constraint insert_con

NOTE - if the first passes but the second fails the message reads:
ERROR:  ExecAppend: rejected due to CHECK constraint $2

The code in the pg_dump file is:
CREATE TABLE "insert_tbl" (
        "x" integer DEFAULT nextval('insert_seq'::text),
        "y" text DEFAULT '-NULL-',
        "z" integer DEFAULT (-1 * currval('insert_seq'::text)),
        CHECK (((x + z) = 0)),
        CONSTRAINT "insert_con" CHECK ((((x >= 3) AND (y <> 'check failed'::text)) AND (x < 8)))
);

The constraints are checked in a different order now and the one error message becomes:

ERROR:  ExecAppend: rejected due to CHECK constraint $1

No file was uploaded with this report