Table constraint ordering disrupted by pg_dump

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Table constraint ordering disrupted by pg_dump
Date: 2001-04-02 20:20:38
Message-ID: 200104022020.f32KKcX93338@hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tricia Holben (pholben(at)greatbridge(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2001-04-02 20:40:24 Re: Table constraint ordering disrupted by pg_dump
Previous Message Stephen Ramsay 2001-04-02 20:13:43 psql segmentation fault