Re: 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