Re: [HACKERS] Another oddity in handling of WCO constraints in postgres_fdw

On Wed, Mar 7, 2018 at 8:55 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Greetings Robert, Ashutosh, Arthur, Etsuro, all,
>
> * Arthur Zakirov (a(dot)zakirov(at)postgrespro(dot)ru) wrote:
>> On Tue, Mar 06, 2018 at 08:09:50PM +0900, Etsuro Fujita wrote:
>> > Agreed. I added a comment to that function. I think that that comment in
>> > combination with changes to the FDW docs in the patch would help FDW authors
>> > understand why that is needed. Please find attached an updated version of
>> > the patch.
>>
>> Thank you.
>>
>> All tests pass, the documentation builds. There was the suggestion [1]
>> of different approach. But the patch fix the issue in much more simple
>> way.
>>
>> Marked as "Ready for Commiter".
>>
>> 1 - https://www.postgresql.org/message-id/20171005.200631.134118679.horiguchi.kyotaro%40lab.ntt.co.jp
>
> Thanks, I've looked through this patch and thread again and continue to
> feel that this is both a good and sensible improvment and that the patch
> is in pretty good shape.
>
> The remaining question is if the subsequent discussion has swayed the
> opinion of Robert and Ashutosh. If we can get agreement that these
> semantics are acceptable and an improvement over the status quo then I'm
> happy to try and drive this patch to commit.
>
> Robert, Ashutosh?

If there is a local constraint on the foreign table, we don't check
it. So, a row that was inserted through this foreign table may not
show up when selected from the foreign table. Apply same logic to the
WCO on a view on the foreign table, it should be fine if a row
inserted through the view doesn't show up in the view. Somebody who
created the view, knew that it's a foreign table underneath.

Stephen said [1] that the view is local and irrespective of what's
underneath it, it should obey WCO. Which seems to be a fair point when
considered alone, but with the above context, it doesn't look any
fair.

Etsuro said [2] that WCO constraints can not be implemented on foreign
server and normal check constraints can be, and for that he provides
an example in [3]. But I think that example is going the wrong
direction. For local constraints to be enforced, we use remote
constraints. For local WCO we need to use remote WCO. That means we
create many foreign tables pointing to same local table on the foreign
server through many views, but it's not impossible.

[1] https://www.postgresql.org/message-id/20180117130021.GC2416%40tamriel.snowman.net
[2] https://www.postgresql.org/message-id/5A058F21.2040201%40lab.ntt.co.jp
[3] https://www.postgresql.org/message-id/a3955a1d-ad07-5b0a-7618-b6ef5ff0e1c5%40lab.ntt.co.jp

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company