Re: [GENERAL] Debian Bug#859033: pg_dump: creates dumps that cannot be restored
On Fri, Mar 31, 2017 at 8:21 AM, Thorsten Glaserwrote: > 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? The usual solution is to add a validation trigger on the derived_things table. It is functionally the same as your CHECK constraint. You could couple it with a FK "child REFERENCES things (thing_id)". If going down the trigger route you could consider adding an insert trigger on things which will also insert a record into a "child_things" table containing on the PK value in a one-to-one relationship. An FK on that table could be define ON UPDATE/ON DELETE CASCADE. The derived_things table could then point to child_things table for it FK. In effect you've just created two (materialized) views of the existing things view just like you tried above but this one works because its indexes and physical and so can be the target of a FK. Because of those properties is just requires a bit of trigger-driven maintenance. > Caveat: I cannot split the “things” table into two. > The original table stays so this shouldn't apply - you do need to be able to create new tables and triggers. David J.
Re: [GENERAL] Debian Bug#859033: pg_dump: creates dumps that cannot be restored
On 03/31/2017 08:21 AM, Thorsten Glaser wrote: 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. Implement it as an ON INSERT/UPDATE trigger on derived_things? Caveat: I cannot split the “things” table into two. bye, //mirabilos -- Adrian Klaver adrian.kla...@aklaver.com -- 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