Great (and admittedly long overdue) patch by Tom Lane:
Make pg_dump --data-only try to order the table dumps so that foreign keys' referenced tables are dumped before the referencing tables. This avoids failures when the data is loaded with the FK constraints already active. If no such ordering is possible because of circular or self-referential constraints, print a NOTICE to warn the user about it.
What it exactly means?
Let's start with basic assumption – this patch touches only –data-only dumping. So, if you never use it, it does absolutely nothing for you. Sorry.
But, if you use –data-only, and you have pre-patch version of PostgreSQL, here is what happens:
First, let's create some tables:
# create table b (id serial primary key); NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for serial column "b.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b" CREATE TABLE # create table a (id serial primary key, b_id int4 references b (id)); NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE
And now, let's insert some data:
# insert into b (id) values (DEFAULT), (DEFAULT), (DEFAULT); INSERT 0 3 # insert into a (b_id) values (1), (2), (3); INSERT 0 3
Pretty simple, here are the data:
# select * from b; id ---- 1 2 3 (3 rows) # select * from a; id | b_id ----+------ 1 | 1 2 | 2 3 | 3 (3 rows)
What will happen if I'll dump it now? With –data-only of course:
=> pg_dump --data-only ... COPY a (id, b_id) FROM stdin; 1 1 2 2 3 3 \. ... COPY b (id) FROM stdin; 1 2 3 \. ...
Whoa, that's bad – it will try to load data to a (with fkey to b) – before b is populated.
Well, this data still can be loaded – by disabling (ALTER TABLE … DISABLE TRIGGER) foreign key triggers, but it is complicated, and definitely “not cool" approach.
But now, with this new patch, pg_dump looks differently:
... COPY b (id) FROM stdin; 1 2 3 \. ... COPY a (id, b_id) FROM stdin; 1 1 2 2 3 3 \. ...
Order is now correct. Of course it will not work in cases of circular dependency, like:
# create table a (id serial primary key); # create table b (id serial primary key, a_id int4 references a(id)); # alter table a add column b_id int4 references b (id); # insert into a (id) values (DEFAULT); # insert into b (id, a_id) values (1, 1); # insert into a (b_id) values (1);
It will show nice warning:
pg_dump: NOTICE: there are circular foreign-key constraints among these table(s): pg_dump: a pg_dump: b pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
Great addition. And kudos to Tom.