Re: [GENERAL] pg_dump: creates dumps that cannot be restored
Hi again, one, possibly, last, thing. I wrote: > I still find the CHECK constraint > to be a more natural way to express what I want, though. Now let me extend on this a bit. The CHECK constraint says nicely and natively, what constraints (no pun intended) I want the data to fulfil. With both the CHECK constraint and the trigger, we need an equivalent constraint on the referenced foreign table, which we have, which we always had; in the schema example I gave, this is not allowing the field “standalone” to change. I can, however, VALIDATE a CHECK constraint after the dump has been restored; I cannot do that with a trigger (or I haven’t found out how to do it). This means that if a user manually edited the dump prior to restoring I have no way to make the restoring transaction fail if the data is bogus. Sure, SOL on the user, but I created the various CHECK constraints to cover against user errors in the first place. I would very much prefer for PostgreSQL to ① formally allow and support such CHECK constraints, ② add an ALTER TABLE … INVALIDATE CONSTRAINT command, to pair with the existing ALTER TABLE … VALIDATE CONSTRAINT command, and ③ hack pg_dump to invalidate constraints before and revalidate them after the fact. This would allow me to express what I want in a more natural and easier to validate (pun intended this time) way. It feels “right” to use a trigger on the referenced table preventing the field from changing, but it feels more right for the referencing table to simply use a CHECK constraint. As for validation, see above. For my current use case, the ship has sailed, but (especially given that such CHECK constrains are currently, while not officially supported, at least “tolerated” and (except in pg_dump) work) this is something to consider for PostgreSQL 10 in my opinion. Thank you for listening. bye, //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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump: creates dumps that cannot be restored
Hi *, I’ve tried both setting the constraints temporarily to invalid (works) and converting (painstakingly slow, as this is new for me) to triggers (also works). Both can be dumped and restored. I’ve also found out that I probably can ship the schema update that converts the CHECK constraint to a trigger to the customer Right Now™ so I’ll fix this actual schema bug. I still find the CHECK constraint to be a more natural way to express what I want, though. I’m attaching the trigger conversion to help anyone else who does this (and to invite feedback should there be anything I could improve). Thanks, //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 testcase.sql Description: application/sql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debian Bug#859033: pg_dump: creates dumps that cannot be restored
On Fri, 31 Mar 2017, Adrian Klaver wrote: > > ① 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." Ah, okay. So, … > > 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 … this would be the proper fix, but… > > 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 … this. Can you suggest a better way to do this? An application developer coworker said to just drop the constraint and do the check in the application, but I work under the assumption that the SQL part is less code, less buggy, less often touched, and only by people who have somewhat a measure of experience, so I declined. Caveat: I cannot split the “things” table into two. bye, //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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Debian Bug#859033: pg_dump: creates dumps that cannot be restored
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.sql2017-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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
