On 03/31/2017 07:34 AM, Thorsten Glaser wrote:
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
Because that is a documented limitation:
https://www.postgresql.org/docs/9.6/static/sql-createtable.html
"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row. The system column
tableoid may be referenced, but not any other system column."
Using a function as a 'cheat' gets you the below. You are asking
Postgres to dive into a function and figure out the embedded dependencies.
② 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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
