Обсуждение: Re: Debian Bug#859033: pg_dump: creates dumps that cannot berestored
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
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@aklaver.com
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
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.klaver@aklaver.com
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.