Re: Debian Bug#859033: pg_dump: creates dumps that cannotbe 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.klaver@aklaver.com