Thread: pg_dump table ordering bug [8.0.1]

Hi!

Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
got dumped in the wrong order (before their dependecies) and had to get
their contents added manually after the restore. I've atleast isolated
the part where things go wrong.

Two files are attached, related as follows (all binaries from 8.0.1):
 > psql -f db-9-spec.sql
 > pg_dump -n debug database > db-9-dump.sql
[drop schema debug cascade]
 > psql -f db-9-dump.sql

psql:db-9-dump.sql:302: ERROR:  new row for relation "form_a_int"
violates check constraint "form_a_int_qid_check"
CONTEXT:  COPY form_a_int, line 1: "1    2    1109    5"
psql:db-9-dump.sql:311: ERROR:  new row for relation "form_a_text"
violates check constraint "form_a_text_qid_check"
CONTEXT:  COPY form_a_text, line 1: "1    1    1109    foo"

The tables have both check and reference constraints. The errors are
from check constraints but the reference constraints would have kicked
in next as the referenced data is below this table in the dump file...

ida=# \d debug.form_a_int
    Table "debug.form_a_int"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 finstance | integer | not null
 qid       | integer | not null
 uid       | integer | not null
 a_int     | integer |
Indexes:
    "form_a_int_pkey" primary key, btree (finstance, qid, uid)
Check constraints:
    "form_a_int_check" CHECK (debug.match_q_instance(finstance, qid))
    "form_a_int_qid_check" CHECK (debug.get_atype(qid) = 'INT'::text)
Foreign-key constraints:
    "form_a_int_qid_fkey" FOREIGN KEY (qid) REFERENCES debug.form_q(qid)
ON UPDATE CASCADE ON DELETE CASCADE
    "form_a_int_finstance_fkey" FOREIGN KEY (finstance) REFERENCES
debug.form_instance(finstance) ON UPDATE CASCADE ON DELETE CASCADE

And the dump data order is:
-- Data for Name: form_a_int;
-- Data for Name: form_instance;
-- Data for Name: form_q;

  Regards,
     Andreas (Not a subscriber of this list)

begin;
--drop schema debug cascade;
create schema debug;


create sequence debug.form_id;

create sequence debug.form_instance_id;

create sequence debug.form_q_id;



create table debug.form_template (
   fid integer primary key default nextval('debug.form_id'),
   name text
);


create table debug.form_instance (
   finstance integer
      primary key
      default nextval('debug.form_instance_id'),
   fid integer not null
     references debug.form_template (fid)
     on delete cascade
     on update cascade,
   type text check (type = 'COURSE')
);

create table debug.course_form (
   finstance integer not null
     primary key
     references debug.form_instance (finstance)
     on delete cascade
     on update cascade,
   cid integer not null
--     references debug.courses (cid)
--     on delete cascade
--     on update cascade
);



create table debug.form_qtypes (
  qtype integer primary key,
  atype text not null
     check (atype = 'INT' or atype = 'TEXT')
);


create table debug.form_q (
  qid integer not null primary key
      default nextval('debug.form_q_id'),
  fid integer not null
      references debug.form_template (fid)
      on delete cascade
      on update cascade,
  qno integer not null
      check (qno >= 0),
  qtype integer not null
      references debug.form_qtypes (qtype)
      on update cascade,
  qtext text
);


create table debug.form_q_alt (
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  altno integer not null
      check (altno >= 0),
  altvalue text not null,
  alttext text,
  primary key (qid, altno)
);


create or replace
   function debug.add_course_form(integer, integer)
   returns integer as '
DECLARE
   my_fid ALIAS FOR $1;
   my_cid ALIAS FOR $2;
   finst integer;
BEGIN
   select nextval(''debug.form_instance_id'') into finst;
   insert into debug.form_instance (finstance, fid, type)
    values(finst, my_fid, ''COURSE'');
   insert into debug.course_form (finstance, cid)
    values (finst, my_cid);
   return finst;
END
'
language 'plpgsql';



create or replace
   function debug.get_atype(integer)
   returns text stable as '
DECLARE
   my_qid ALIAS FOR $1;
   my_qtype integer;
BEGIN
   select qtype
       from debug.form_q
       where qid=my_qid
       into my_qtype;
   if my_qtype is null then
      return '''';
   end if;
   return
      (select atype
       from debug.form_qtypes
       where qtype=my_qtype);
END
'
language 'plpgsql';


create or replace
   function debug.match_q_instance(integer, integer)
   returns boolean stable as '
DECLARE
   my_finstance ALIAS FOR $1;
   my_qid ALIAS FOR $2;
   q_fid integer;
   finst_fid integer;
BEGIN
   select fid
       from debug.form_instance
       where finstance=my_finstance
       into finst_fid;
   select fid
       from debug.form_q
       where qid=my_qid
       into q_fid;

   return finst_fid = q_fid;
END
'
language 'plpgsql';



create table debug.form_a_int (
  finstance integer not null
      references debug.form_instance (finstance)
      on delete cascade
      on update cascade,
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  uid integer not null,
--      references debug.users (uid)
--      on update cascade,
  a_int integer,
  check (debug.get_atype(qid) = 'INT'),
  check (debug.match_q_instance(finstance, qid)),
  primary key (finstance, qid, uid)
);



create table debug.form_a_text (
  finstance integer not null
      references debug.form_instance (finstance)
      on delete cascade
      on update cascade,
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  uid integer not null,
--      references debug.users (uid)
--      on update cascade,
  a_text text,
  check (debug.get_atype(qid) = 'TEXT'),
  check (debug.match_q_instance(finstance, qid)),
  primary key (finstance, qid, uid)
);

create or replace view debug.form_answers as
 select fid, finstance, qno, qid, qtype, atype, uid, a_text, a_int
   from debug.form_a_int  as ai
   natural full outer join debug.form_a_text as at
   natural join debug.form_q
   natural join debug.form_qtypes
   order by fid, finstance, qno, uid;


-- Text string
insert into debug.form_qtypes (qtype, atype) values (1, 'TEXT');
-- A numeric answer
insert into debug.form_qtypes (qtype, atype) values (2, 'INT');
-- Question with alternatives, numeric answer
insert into debug.form_qtypes (qtype, atype) values (3, 'INT');


insert into debug.form_template (name) values ('Test template');

insert into debug.form_q (fid,qno,qtype,qtext)
  values (1,1,1, 'Fie');
insert into debug.form_q (fid,qno,qtype,qtext)
  values (1,2,2, 'Fum');

insert into debug.form_instance (fid, type) values (1,'COURSE');
insert into debug.course_form (finstance,cid) values (1,2);

insert into debug.form_a_text (finstance,qid,uid,a_text)
  values(1,1,1109,'foo');
insert into debug.form_a_int (finstance,qid,uid,a_int)
  values(1,2,1109,5);



commit;--
-- PostgreSQL database dump
--

SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: debug; Type: SCHEMA; Schema: -; Owner: pgadmin
--

CREATE SCHEMA debug;


ALTER SCHEMA debug OWNER TO pgadmin;

SET search_path = debug, pg_catalog;

--
-- Name: add_course_form(integer, integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION add_course_form(integer, integer) RETURNS integer
    AS $_$
DECLARE
   my_fid ALIAS FOR $1;
   my_cid ALIAS FOR $2;
   finst integer;
BEGIN
   select nextval('debug.form_instance_id') into finst;
   insert into debug.form_instance (finstance, fid, type)
    values(finst, my_fid, 'COURSE');
   insert into debug.course_form (finstance, cid)
    values (finst, my_cid);
   return finst;
END
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION debug.add_course_form(integer, integer) OWNER TO pgadmin;

--
-- Name: get_atype(integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION get_atype(integer) RETURNS text
    AS $_$
DECLARE
   my_qid ALIAS FOR $1;
   my_qtype integer;
BEGIN
   select qtype
       from debug.form_q
       where qid=my_qid
       into my_qtype;
   if my_qtype is null then
      return '';
   end if;
   return
      (select atype
       from debug.form_qtypes
       where qtype=my_qtype);
END
$_$
    LANGUAGE plpgsql STABLE;


ALTER FUNCTION debug.get_atype(integer) OWNER TO pgadmin;

--
-- Name: match_q_instance(integer, integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION match_q_instance(integer, integer) RETURNS boolean
    AS $_$
DECLARE
   my_finstance ALIAS FOR $1;
   my_qid ALIAS FOR $2;
   q_fid integer;
   finst_fid integer;
BEGIN
   select fid
       from debug.form_instance
       where finstance=my_finstance
       into finst_fid;
   select fid
       from debug.form_q
       where qid=my_qid
       into q_fid;

   return finst_fid = q_fid;
END
$_$
    LANGUAGE plpgsql STABLE;


ALTER FUNCTION debug.match_q_instance(integer, integer) OWNER TO pgadmin;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: course_form; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE course_form (
    finstance integer NOT NULL,
    cid integer NOT NULL
);


ALTER TABLE debug.course_form OWNER TO pgadmin;

--
-- Name: form_a_int; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_a_int (
    finstance integer NOT NULL,
    qid integer NOT NULL,
    uid integer NOT NULL,
    a_int integer,
    CONSTRAINT form_a_int_check CHECK (match_q_instance(finstance, qid)),
    CONSTRAINT form_a_int_qid_check CHECK ((get_atype(qid) = 'INT'::text))
);


ALTER TABLE debug.form_a_int OWNER TO pgadmin;

--
-- Name: form_a_text; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_a_text (
    finstance integer NOT NULL,
    qid integer NOT NULL,
    uid integer NOT NULL,
    a_text text,
    CONSTRAINT form_a_text_check CHECK (match_q_instance(finstance, qid)),
    CONSTRAINT form_a_text_qid_check CHECK ((get_atype(qid) = 'TEXT'::text))
);


ALTER TABLE debug.form_a_text OWNER TO pgadmin;

--
-- Name: form_q; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_q (
    qid integer DEFAULT nextval('debug.form_q_id'::text) NOT NULL,
    fid integer NOT NULL,
    qno integer NOT NULL,
    qtype integer NOT NULL,
    qtext text,
    CONSTRAINT form_q_qno_check CHECK ((qno >= 0))
);


ALTER TABLE debug.form_q OWNER TO pgadmin;

--
-- Name: form_qtypes; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_qtypes (
    qtype integer NOT NULL,
    atype text NOT NULL,
    CONSTRAINT form_qtypes_atype_check CHECK (((atype = 'INT'::text) OR (atype = 'TEXT'::text)))
);


ALTER TABLE debug.form_qtypes OWNER TO pgadmin;

--
-- Name: form_answers; Type: VIEW; Schema: debug; Owner: pgadmin
--

CREATE VIEW form_answers AS
    SELECT form_q.fid, finstance, form_q.qno, qid, form_q.qtype, form_qtypes.atype, uid, "at".a_text, ai.a_int FROM
(((form_a_intai NATURAL FULL JOIN form_a_text "at") NATURAL JOIN form_q) NATURAL JOIN form_qtypes) ORDER BY form_q.fid,
finstance,form_q.qno, uid; 


ALTER TABLE debug.form_answers OWNER TO pgadmin;

--
-- Name: form_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

CREATE SEQUENCE form_id
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE debug.form_id OWNER TO pgadmin;

--
-- Name: form_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_id', 1, true);


--
-- Name: form_instance; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_instance (
    finstance integer DEFAULT nextval('debug.form_instance_id'::text) NOT NULL,
    fid integer NOT NULL,
    "type" text,
    CONSTRAINT form_instance_type_check CHECK (("type" = 'COURSE'::text))
);


ALTER TABLE debug.form_instance OWNER TO pgadmin;

--
-- Name: form_instance_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

CREATE SEQUENCE form_instance_id
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE debug.form_instance_id OWNER TO pgadmin;

--
-- Name: form_instance_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_instance_id', 1, true);


--
-- Name: form_q_alt; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_q_alt (
    qid integer NOT NULL,
    altno integer NOT NULL,
    altvalue text NOT NULL,
    alttext text,
    CONSTRAINT form_q_alt_altno_check CHECK ((altno >= 0))
);


ALTER TABLE debug.form_q_alt OWNER TO pgadmin;

--
-- Name: form_q_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

CREATE SEQUENCE form_q_id
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE debug.form_q_id OWNER TO pgadmin;

--
-- Name: form_q_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_q_id', 2, true);


--
-- Name: form_template; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace:
--

CREATE TABLE form_template (
    fid integer DEFAULT nextval('debug.form_id'::text) NOT NULL,
    name text
);


ALTER TABLE debug.form_template OWNER TO pgadmin;

--
-- Data for Name: course_form; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY course_form (finstance, cid) FROM stdin;
1    2
\.


--
-- Data for Name: form_a_int; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_a_int (finstance, qid, uid, a_int) FROM stdin;
1    2    1109    5
\.


--
-- Data for Name: form_a_text; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_a_text (finstance, qid, uid, a_text) FROM stdin;
1    1    1109    foo
\.


--
-- Data for Name: form_instance; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_instance (finstance, fid, "type") FROM stdin;
1    1    COURSE
\.


--
-- Data for Name: form_q; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_q (qid, fid, qno, qtype, qtext) FROM stdin;
1    1    1    1    Fie
2    1    2    2    Fum
\.


--
-- Data for Name: form_q_alt; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_q_alt (qid, altno, altvalue, alttext) FROM stdin;
\.


--
-- Data for Name: form_qtypes; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_qtypes (qtype, atype) FROM stdin;
1    TEXT
2    INT
3    INT
\.


--
-- Data for Name: form_template; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_template (fid, name) FROM stdin;
1    Test template
\.


--
-- Name: course_form_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY course_form
    ADD CONSTRAINT course_form_pkey PRIMARY KEY (finstance);


ALTER INDEX debug.course_form_pkey OWNER TO pgadmin;

--
-- Name: form_a_int_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_pkey PRIMARY KEY (finstance, qid, uid);


ALTER INDEX debug.form_a_int_pkey OWNER TO pgadmin;

--
-- Name: form_a_text_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_pkey PRIMARY KEY (finstance, qid, uid);


ALTER INDEX debug.form_a_text_pkey OWNER TO pgadmin;

--
-- Name: form_instance_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_instance
    ADD CONSTRAINT form_instance_pkey PRIMARY KEY (finstance);


ALTER INDEX debug.form_instance_pkey OWNER TO pgadmin;

--
-- Name: form_q_alt_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_q_alt
    ADD CONSTRAINT form_q_alt_pkey PRIMARY KEY (qid, altno);


ALTER INDEX debug.form_q_alt_pkey OWNER TO pgadmin;

--
-- Name: form_q_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_pkey PRIMARY KEY (qid);


ALTER INDEX debug.form_q_pkey OWNER TO pgadmin;

--
-- Name: form_qtypes_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_qtypes
    ADD CONSTRAINT form_qtypes_pkey PRIMARY KEY (qtype);


ALTER INDEX debug.form_qtypes_pkey OWNER TO pgadmin;

--
-- Name: form_template_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace:
--

ALTER TABLE ONLY form_template
    ADD CONSTRAINT form_template_pkey PRIMARY KEY (fid);


ALTER INDEX debug.form_template_pkey OWNER TO pgadmin;

--
-- Name: course_form_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY course_form
    ADD CONSTRAINT course_form_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE
CASCADEON DELETE CASCADE; 


--
-- Name: form_a_int_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE
CASCADEON DELETE CASCADE; 


--
-- Name: form_a_int_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_a_text_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE
CASCADEON DELETE CASCADE; 


--
-- Name: form_a_text_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_instance_fid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_instance
    ADD CONSTRAINT form_instance_fid_fkey FOREIGN KEY (fid) REFERENCES form_template(fid) ON UPDATE CASCADE ON DELETE
CASCADE;


--
-- Name: form_q_alt_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q_alt
    ADD CONSTRAINT form_q_alt_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_q_fid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_fid_fkey FOREIGN KEY (fid) REFERENCES form_template(fid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_q_qtype_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_qtype_fkey FOREIGN KEY (qtype) REFERENCES form_qtypes(qtype) ON UPDATE CASCADE;

Andreas Lange wrote :

> Hi!
>
> Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables got
dumped in the wrong order
> (before their dependecies) and had to get their contents added manually
after the restore. I've atleast
> isolated the part where things go wrong.

I've experienced a similar problem with a data-only dump using 8.0.1's
pg_dump.  It seems that the COPYs (or INSERTs) are generated in
alphabetical order of table name, ignoring foreign key contraints and
requiring the output to be post-processed.  In a full dump of the database
the constraints are added after the data is inserted so there are no
problems.  See the two attached dumps of the same database as an example.

I am running under HP-UX 11.11 on PA-RISC 2.0, (both 32- and 64-bit builds
tried) built using the native ANSI C compiler.  All tests passed when
running 'make check'.

Gerard Krupa MBCS
Marconi Corporation

(See attached file: data-only.sql)(See attached file: full.sql)

"Gerard Krupa" <Gerard.Krupa@marconi.com> writes:
> I've experienced a similar problem with a data-only dump using 8.0.1's
> pg_dump.  It seems that the COPYs (or INSERTs) are generated in
> alphabetical order of table name, ignoring foreign key contraints and
> requiring the output to be post-processed.  In a full dump of the database
> the constraints are added after the data is inserted so there are no
> problems.  See the two attached dumps of the same database as an example.

This is not a bug.  In a data-only dump pg_dump cannot hope to know what
FK constraints may be applied to the data at load time --- there's no
particularly good reason to assume they are the same as what was in the
source database.  So it's your responsibility to order the loading steps
properly.  See pg_restore's -l/-L switches.

            regards, tom lane

Andreas Lange <anlan@ida.liu.se> writes:
> Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
> got dumped in the wrong order (before their dependecies) and had to get
> their contents added manually after the restore. I've atleast isolated
> the part where things go wrong.

I don't think this is a pg_dump bug: the problem is you are abusing
check constraints to emulate foreign key constraints.  pg_dump has no
way to know what those functions are doing and therefore no way to
realize that the check constraints impose a data load ordering
dependency.  Furthermore, the check constraints are fundamentally wrong
anyway because they don't create a two-way relationship --- that is,
altering the referenced tables won't raise an error if the check is now
violated for something in the referencing table.

It would be best to find a way to express these relationships with
ordinary foreign keys.  Maybe you could add a column to form_a_int
that is a foreign key reference to both of form_instance.fid and
form_q.fid, for example?

            regards, tom lane

On Thu, 2005-03-24 at 17:27 -0500, Tom Lane wrote:
> Andreas Lange <anlan@ida.liu.se> writes:
> > Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
> > got dumped in the wrong order (before their dependecies) and had to get
> > their contents added manually after the restore. I've atleast isolated
> > the part where things go wrong.
>
> I don't think this is a pg_dump bug: the problem is you are abusing
> check constraints to emulate foreign key constraints.  pg_dump has no
> way to know what those functions are doing and therefore no way to
> realize that the check constraints impose a data load ordering
> dependency.  Furthermore, the check constraints are fundamentally wrong
> anyway because they don't create a two-way relationship --- that is,
> altering the referenced tables won't raise an error if the check is now
> violated for something in the referencing table.

(Sorry for the long delay here!)

Could this be resolved simply by having pg_dump write out all constraint
statements after all insert and trigger statements?

Then no data-order-dependent constraints will be triggered when the dump
is loaded, and even constraints that aren't met when when the dump is
taken won't be triggered when the data is re-loaded.

(I would say that would be a feature not a bug, since as I understand it
the point of pg_dump is to replicate a db setup, with it also being a
separate sanity checker merely a possible benefit.  And in any event, if
a few "special" rows don't meet constraints, having had to have been
entered before the constraints were put into place, those rows could
still be restored without problems.  Whether that's indicative of poor
schema design is a separate issue.)

Are there any downsides to changing the order of pg_dump output with
respect to constraints?  (Versus requiring users to alter their schema
design.)

--
Mark Shewmaker
mark@primefactor.com