pg_dump table ordering bug [8.0.1] - Mailing list pgsql-bugs

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;