[Pkg-postgresql-public] Bug#859033: pg_dump: creates dumps that cannot be restored

[Thorsten Glaser]

Package: postgresql-client-common
Version: 179
Severity: normal

I’ve created a testcase (MWE) here.


Step 1: initialise a new database user and DB, for the test:

user$ sudo su - postgres
postgres$ createuser -D -P -R -S testuser
postgres$ createdb -E UTF-8 -O testuser -T template0 -l de_DE.UTF-8 testdb
^D


Step 2: import the attached SQL, note the last two statements
will fail but the others will succeed:

psql -U testuser -h 127.0.0.1 -f testcase.sql testdb


Step 3: create a database dump, removing a few lines that are
known to always produce errors on restoring:

pg_dump -c -Fp --no-owner --if-exists -U testuser -h 127.0.0.1 testdb | sed \
    -e '/^DROP EXTENSION IF EXISTS plpgsql/d' \
    -e '/^DROP SCHEMA IF EXISTS public/d' \
    -e '/^CREATE SCHEMA public/d' \
    -e '/^COMMENT ON SCHEMA public/d' \
    -e '/^COMMENT ON EXTENSION plpgsql/d' \
    >dump.sql


Step 4: try to restore it, watch it explode:

$ psql -U testuser -h 127.0.0.1 -f dump.sql --single-transaction --set=ON_ERROR_STOP=1 testdb
Password for user testuser:
SET
SET
SET
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP SEQUENCE
DROP TABLE
DROP TABLE
DROP FUNCTION
DROP FUNCTION
CREATE EXTENSION
SET
CREATE FUNCTION
CREATE FUNCTION
SET
SET
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER TABLE
psql:dump.sql:146: ERROR:  new row for relation "derived_things" violates check constraint "derived_things_check_child"
DETAIL:  Failing row contains (3, 1, foobar on foo).
CONTEXT:  COPY derived_things, line 1: "3       1       foobar on foo"


‣‣‣ What happened?

The database dump contains the tables derived_things and things in
this order (ASCIIbetically), however, the table derived_things depends
on the table things.

‣‣‣ What have I expected?

That pg_dump recognises the dependency (there i̲s̲ a FOREIGN KEY reference
in there) and reorders the tables dumped.

‣‣‣ How can I prove this works?

$ ed dump.sql
/^COPY derived_things/ka
/^\\\./kb
/^COPY things/                  
/^\\\./+1kc
'a,'bm'c
wq

This reorders the dump to look as follows:


[…]
-- Data for Name: things; Type: TABLE DATA; Schema: public; Owner: -
--

COPY things (pk, some_data, standalone) FROM stdin;
1       foo     t
2       bar     t
3       foobar  f
4       foofoobar       f
5       baz     t
\.

COPY derived_things (parent, child, arbitrary_data) FROM stdin;
3       1       foobar on foo
3       2       foobar on bar
4       1       foofoobar on foo
\.

--
-- Name: things_pk_seq; Type: SEQUENCE SET; Schema: public; Owner: -
[…]


Restoring this works just fine:

$ psql -U testuser -h 127.0.0.1 -f dump.sql --single-transaction --set=ON_ERROR_STOP=1 testdb
Password for user testuser:
SET
SET
SET
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP SEQUENCE
DROP TABLE
DROP TABLE
DROP FUNCTION
DROP FUNCTION
CREATE EXTENSION
SET
CREATE FUNCTION
CREATE FUNCTION
SET
SET
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER TABLE
COPY 5
COPY 3
 setval
--------
      1
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE


-- System Information:
Debian Release: 9.0
  APT prefers unreleased
  APT policy: (500, 'unreleased'), (500, 'buildd-unstable'), (500, 'unstable')
Architecture: x32 (x86_64)
Foreign Architectures: i386, amd64

Kernel: Linux 4.9.0-2-amd64 (SMP w/4 CPU cores)
Locale: LANG=C, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/lksh
Init: sysvinit (via /sbin/init)

Versions of packages postgresql-client-common depends on:
ii  netbase  5.4

Versions of packages postgresql-client-common recommends:
ii  libreadline7  7.0-2
ii  lsb-release   9.20161125

postgresql-client-common suggests no packages.

-- no debconf information
-------------- next part --------------
CREATE TABLE things (
	pk BIGSERIAL PRIMARY KEY,
	some_data TEXT NOT NULL,
	standalone BOOLEAN NOT NULL
);

CREATE FUNCTION check_derived_is_child(BIGINT) RETURNS BOOLEAN AS $$
	SELECT EXISTS (
		SELECT 1 FROM things
		    WHERE pk=$1 AND standalone=TRUE
	);
$$ LANGUAGE sql;

CREATE FUNCTION check_derived_is_parent(BIGINT) RETURNS BOOLEAN AS $$
	SELECT EXISTS (
		SELECT 1 FROM things
		    WHERE pk=$1 AND standalone=FALSE
	);
$$ LANGUAGE sql;

CREATE TABLE derived_things (
	parent BIGINT NOT NULL REFERENCES things(pk),
	child BIGINT NOT NULL REFERENCES things(pk),
	arbitrary_data TEXT NOT NULL,
	CONSTRAINT derived_things_check_child CHECK (check_derived_is_child(child)),
	CONSTRAINT derived_things_check_parent CHECK (check_derived_is_parent(parent)),
	PRIMARY KEY (parent, child)
);

-- these will succeed
INSERT INTO things VALUES (1, 'foo', TRUE);
INSERT INTO things VALUES (2, 'bar', TRUE);
INSERT INTO things VALUES (3, 'foobar', FALSE);
INSERT INTO things VALUES (4, 'foofoobar', FALSE);
INSERT INTO things VALUES (5, 'baz', TRUE);
INSERT INTO derived_things VALUES (3, 1, 'foobar on foo');
INSERT INTO derived_things VALUES (3, 2, 'foobar on bar');
INSERT INTO derived_things VALUES (4, 1, 'foofoobar on foo');

-- these will not succeed due to the check constraints
INSERT INTO derived_things VALUES (4, 3, 'foofoobar on foobar');
INSERT INTO derived_things VALUES (5, 1, 'baz on foo');

-- show
SELECT * FROM derived_things;
SELECT * FROM things;