Re: Table constraint ordering disrupted by pg_dump

Patricia Holben <pholben@greatbridge.com> writes:
> I have loaded this new version and re-tested but don't see it fixed.

It looks fixed to me.  Original SQL in constraints.sql:

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));

As dumped by current-CVS pg_dump:

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

> I do have a separate routine which I am running - it isn't complete which
> is why I haven't shared it yet - which proves that when the dump is
> reloaded the constraints are not applied in the original order.

I notice that the backend tends to apply the constraints in the reverse
order of declaration, but this should be consistent between the original
database and the dumped/reloaded one.

            regards, tom lane