(2014/01/21 11:44), Shigeru Hanada wrote:
Thanks for the comments.
2014/1/21 KaiGai Kohei <kai...@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 <kai...@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
