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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
