Re: inherit support for foreign tables

(2014/01/14 18:24), Shigeru Hanada wrote:
> 2013/11/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Robert Haas <robertmhaas(at)gmail(dot)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(at)ak(dot)jp(dot)nec(dot)com>