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
createtable command contained two constraints, the error messages would reflect that the constraints were being checked
ina consistent order and error messages would show either the constraint name or $1 or $2 if no name was used for the
constraintwhich generated the first error. After dumping and restoring the database, the order was reversed. As a
resultthe 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
