Skip site navigation (1) Skip section navigation (2)

BUG #1474: pg_dump outputs tables in wrong order

From: "Gerard Krupa" <gerard(dot)krupa(at)marconi(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1474: pg_dump outputs tables in wrong order
Date: 2005-02-10 11:48:46
Message-ID: 20050210114846.E600CF0B13@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1474
Logged by:          Gerard Krupa
Email address:      gerard(dot)krupa(at)marconi(dot)com
PostgreSQL version: 8.0.1
Operating system:   HP-UX B.11.11 U 9000/800 650319392 unlimited-user
license PA-RISC 2.0 (64-bit)
Description:        pg_dump outputs tables in wrong order
Details: 

pg_dump outputs table data COPYs and INSERTs in alphabetical order ignoring
any foreign key constraints that exist between those tables.  In a full dump
this is not a problem as the constraints are removed and then re-added after
the data but using a data-only dump the data will be restored in the wrong
order causing FK constraint violations.

In 7.4.6 the output was always sorted in such a way that FK violations would
not occur.

To reproduce:

1. Add the schema and data below to a clean database
2. Dump up the data using pg_dump --data-only
3. Delete all tuples from the two relations
4. Use the dump generated in step 2 to restore the data

CREATE TABLE b_first (
    id integer NOT NULL PRIMARY KEY
);

CREATE TABLE a_second (
    id integer NOT NULL PRIMARY KEY,
    ref integer NOT NULL
);

ALTER TABLE a_second
    ADD CONSTRAINT the_link FOREIGN KEY (ref)
    REFERENCES b_first(id);

COPY b_first (id) FROM stdin;
1
2
3
\.

COPY a_second (id, ref) FROM stdin;
1   1
2   2
3   3
\.

Compiled using native HP C compiler in both 64- and 32- bit modes - the
problem always occurs.

pgsql-bugs by date

Next:From: Dennis HübnerDate: 2005-02-10 12:47:46
Subject: No connection to Server
Previous:From: Gerard KrupaDate: 2005-02-10 10:03:58
Subject: Re: pg_dump table ordering bug [8.0.1]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group