Message-ID: <52DE047A.8020200@ak.jp.nec.com>
Date: Tue, 21 Jan 2014 14:24:10 +0900
From: KaiGai Kohei <kaigai@ak.jp.nec.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.1
MIME-Version: 1.0
To: Shigeru Hanada <shigeru.hanada@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: inherit support for foreign tables
References: 
 <CAEZqfEfVJNptoTSEY3QrQ90K9WD2nxfM+6fsk3YTc1ZXZ1Y5-g@mail.gmail.com>
	<12432.1384406906@sss.pgh.pa.us>
	<CA+Tgmob3ZNDi+P+v3ypHvFN1tD-Bpt8M+0dHFTnCqHxmcNw8aQ@mail.gmail.com>
	<10163.1384786524@sss.pgh.pa.us>
	<CAEZqfEc6MkEx+L=bQHVPEjr_+LgWSzy8uA11ci=X0A8VEPRmiw@mail.gmail.com>
	<52DDD071.9090405@ak.jp.nec.com>
 <CAEZqfEe_enXpSFpS8S_q6a8gNHK8AKtD5vy=fYxTddRtR8uOmw@mail.gmail.com>
In-Reply-To: 
 <CAEZqfEe_enXpSFpS8S_q6a8gNHK8AKtD5vy=fYxTddRtR8uOmw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

(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>


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers