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

[Thorsten Glaser]

Hi *,

while I’d still appreciate help on the bugreport (context is this…
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=859033 … one), I’ve
found this… http://dba.stackexchange.com/a/75635/65843 … which says
① that using a CHECK constraint to check data from another table
  is wrong (but not why), and
② that there’s no reason to not have a CHECK constraint in NOT VALID
  mode, as that’s how it operates anyway (when existent right from the
  time the table is created), and
③ that NOT VALID constraints are ordered below the data by pg_dump.

So, now I have a workaround (although I still consider it a bug that
pg_dump creates SQL that cannot ever be restored without manual editing
and user intervention) requiring a minimal but application-wise (hope‐
fully) compatible schema change:

--- bugreport.cgi	2017-03-31 16:19:38.565969747 +0200
+++ testcase.sql	2017-03-31 16:20:10.146336502 +0200
@@ -22,11 +22,12 @@
 	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)
 );
 
+ALTER TABLE derived_things ADD	CONSTRAINT derived_things_check_child CHECK (check_derived_is_child(child)) NOT VALID;
+ALTER TABLE derived_things ADD	CONSTRAINT derived_things_check_parent CHECK (check_derived_is_parent(parent)) NOT VALID;
+
 -- these will succeed
 INSERT INTO things VALUES (1, 'foo', TRUE);
 INSERT INTO things VALUES (2, 'bar', TRUE);

I’ll see whether this can mitigate the most pressing issues with this.


From a comment on http://stackoverflow.com/q/16323236/2171120,
I also have a more generic suggestion to use an FK instead of a
CHECK constraint, although I’m not sure that this wouldn’t require
changes to the application code, and I *am* sure that VIEWs have
penalties to the query optimiser (probably not a big issue here,
though).

I was thinking about…

CREATE VIEW vw_things_parents AS SELECT * FROM things WHERE standalone=FALSE;
CREATE VIEW vw_things_children AS SELECT * FROM things WHERE standalone=TRUE;

DROP TABLE derived_things;
CREATE TABLE derived_things (
	parent BIGINT NOT NULL REFERENCES vw_things_parents(pk),
	child BIGINT NOT NULL REFERENCES vw_things_children(pk),
	arbitrary_data TEXT NOT NULL,
	PRIMARY KEY (parent, child)
);

This, however, gives me:
ERROR:  referenced relation "vw_things_parents" is not a table


So, I might be doing it wrong (or not?), but how do I solve
this the best way?

Thanks in advance,
//mirabilos
-- 
tarent solutions GmbH
Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/
Tel: +49 228 54881-393 • Fax: +49 228 54881-235
HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941
Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg