В ожидании 8.4 - упорядоченная загрузка данных в дамп

Перевод Waiting for 8.4 - ordered data loading in pg_dump с select * from depesz;

Отличный (и, надо сказать, давно ожидаемый) патч от Tom Lane:

Теперь pg_dump --data-only пытается упорядочить дампы таблиц таким образом,
что таблицы, на которые ссылаются вторичные ключи, выгружаются раньше таблиц, 
содержащих эти ключи. Это помогает обходить сбои, возникающие при загрузке 
данных,которые ссылаются на ещё не загруженные данные. Когда такое упорядочивание 
не возможно, в случае циклических зависимостей или ссылок на самих себя, выводит 
NOTICE для предупреждения об этом пользователя.

Что это означает в действительности?

Начнём с простого допущения - этот патч затрагивает выгрузку только данных (--data-only). Так что, если вы это не используете, то вам оно ничем не поможет. Простите.

Но, если используете, и у вас "пропатченая" версия postgres, вот что произойдёт:

Во первых, создадим кое-какие таблицы:

# 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

И добавим данных:

# insert into b (id) values (DEFAULT), (DEFAULT), (DEFAULT);
INSERT 0 3
# insert into a (b_id) values (1), (2), (3);
INSERT 0 3

Всё просто, вот данные:

# select * from b;
id
----
1
2
3
(3 rows)
 
# select * from a;
id | b_id
----+------
1 | 1
2 | 2
3 | 3
(3 rows)

Вот что произойдёт если я сделаю дамп без патча:

=> 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
\.
...

Печально - данные будут загружены в таблицу a (с FK на b) перед тем, как загрузится b.

Конечно же данные могут быть загружены иначе, через выключение триггеров вторичных ключей (ALTER TABLE … DISABLE TRIGGER), но это сложновато, и определённо не "круто".

С новым патчем дамп выглядит иначе:

...
COPY b (id) FROM stdin;
1
2
3
\.
...
COPY a (id, b_id) FROM stdin;
1 1
2 2
3 3
\.
...

Порядок теперь правильный. Конечно это не сработает в случае циклической зависимости:

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

Будет выведено предупреждение:

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.

Хорошее дополнение. И респект Tom'у.