Re: Debian Bug#859033: pg_dump: creates dumps that cannotbe 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?

Caveat: I cannot split the “things” table into two.