Re: inherit support for foreign tables

(2014/01/14 18:24), Shigeru Hanada wrote:
> 2013/11/18 Tom Lane <tgl@sss.pgh.pa.us>:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> I think it's been previously proposed that we have some version of a
>>> CHECK constraint that effectively acts as an assertion for query
>>> optimization purposes, but isn't actually enforced by the system.  I
>>> can see that being useful in a variety of situations, including this
>>> one.
>>
>> Yeah, I think it would be much smarter to provide a different syntax
>> to explicitly represent the notion that we're only assuming the condition
>> is true, and not trying to enforce it.
>
> I'd like to revisit this feature.
>
> Explicit notation to represent not-enforcing (assertive?) is an
> interesting idea.  I'm not sure which word is appropriate, but for
> example, let's use the word ASSERTIVE as a new constraint attribute.
>
> CREATE TABLE parent (
>      id int NOT NULL,
>      group int NOT NULL,
>      name text
> );
>
> CREATE FOREIGN TABLE child_grp1 (
>    /* no additional column */
> ) INHERITS (parent) SERVER server1;
> ALTER  TABLE child_grp1 ADD CONSTRAINT chk_group1 CHECK (group = 1) ASSERTIVE;
>
> If ASSERTIVE is specified, it's not guaranteed that the constraint is
> enforced completely, so it should be treated as a hint for planner.
> As Robert mentioned, enforcing as much as we can during INSERT/UPDATE
> is one option about this issue.
>
> 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.

> Besides CHECK constraints, currently NOT NULL constraints are
> virtually ASSERTIVE (not enforcing).  Should it also be noted
> explicitly?
>
Backward compatibility....

NOT NULL [ASSERTIVE] might be an option.

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