Re: inherit support for foreign tables

(2014/01/21 11:44), Shigeru Hanada wrote:
> Thanks for the comments.
>
> 2014/1/21 KaiGai Kohei <kaigai@ak.jp.nec.com>:
>>> In addition, an idea which I can't throw away is to assume that all
>>> constraints defined on foreign tables as ASSERTIVE.  Foreign tables
>>> potentially have dangers to have "wrong" data by updating source data
>>> not through foreign tables.  This is not specific to an FDW, so IMO
>>> constraints defined on foreign tables are basically ASSERTIVE.  Of
>>> course PG can try to maintain data correct, but always somebody might
>>> break it.
>>> qu
>>>
>> Does it make sense to apply "assertive" CHECK constraint on the qual
>> of ForeignScan to filter out tuples with violated values at the local
>> side, as if row-level security feature doing.
>> It enables to handle a situation that planner expects only "clean"
>> tuples are returned but FDW driver is unavailable to anomalies.
>>
>> Probably, this additional check can be turned on/off on the fly,
>> if FDW driver has a way to inform the core system its capability,
>> like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
>> local checks.
>
> Hmm, IIUC you mean that local users can't (or don't need to) know that
> data which violates the local constraints exist on remote side.
> Applying constraints to the data which is modified through FDW would
> be necessary as well.  In that design, FDW is a bidirectional filter
> which provides these features:
>
> 1) Don't push wrong data into remote data source, by applying local
> constraints to the result of the modifying query executed on local PG.
>   This is not perfect filter, because remote constraints don't mapped
> automatically or perfectly (imagine constraints which is available on
> remote but is not supported in PG).
> 2) Don't retrieve wrong data from remote to local PG, by applying
> local constraints
>
Yes. (1) can be done with ExecConstraints prior to FDW callback on
UPDATE or INSERT, even not a perfect solution because of side-channel
on the remote data source. For (2), my proposition tries to drop
retrieved violated tuples, however, the result is same.

> I have a concern about consistency.  It has not been supported, but
> let's think of Aggregate push-down invoked by a query below.
>
> SELECT count(*) FROM remote_table;
>
> If this query was fully pushed down, the result is the # of records
> exist on remote side, but the result would be # of valid records when
> we don't push down the aggregate.  This would confuse users.
>
Hmm. In this case, FDW driver needs to be responsible to push-down
the additional check quals into remote side, so it does not work
transparently towards the ForeignScan.
It might be a little bit complicated suggestion for the beginning
of the efforts.

>>> Besides CHECK constraints, currently NOT NULL constraints are
>>> virtually ASSERTIVE (not enforcing).  Should it also be noted
>>> explicitly?
>>>
>> Backward compatibility….
>
> Yep, backward compatibility (especially visible ones to users) should
> be minimal, ideally zero.
>
>> NOT NULL [ASSERTIVE] might be an option.
>
> Treating [ASSERTIVE | NOT ASSERTIVE] like DEFERRABLE, and allow
> ingASSERTIVE for only foreign tables?  It makes sense, though we need
> consider exclusiveness .  But It needs to default to ASSERTIVE on
> foreign tables, and NOT ASSERTIVE (means "forced") on others.  Isn't
> is too complicated?
>
I think it is not easy to implement assertive checks, except for
foreign tables, because all the write stuff to regular tables are
managed by PostgreSQL itself.
So, it is a good first step to add support "ASSERTIVE" CHECK on
foreign table only, and to enforce FDW drivers nothing special
from my personal sense.

How about committer's opinion?

Thanks,
-- 
OSS Promotion Center / The PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>