Re: inherit support for foreign tables

(2014/09/11 19:46), Heikki Linnakangas wrote:
> On 09/11/2014 12:22 PM, Etsuro Fujita wrote:
>> (2014/09/11 4:32), Heikki Linnakangas wrote:
>>> I had a cursory look at this patch and the discussions around this.
>>
>> Thank you!
>>
>>> ISTM there are actually two new features in this: 1) allow CHECK
>>> constraints on foreign tables, and 2) support inheritance for foreign
>>> tables. How about splitting it into two?
>>
>> That's right. There are the two in this patch.
>>
>> I'm not sure if I should split the patch into the two, because 1) won't
>> make sense without 2). As described in the following note added to the
>> docs on CREATE FOREIGN TABLE, CHECK constraints on foreign tables are
>> intended to support constraint exclusion for partitioned foreign tables:
>>
>> + Constraints on foreign tables are not enforced on insert or update.
>> + Those definitions simply declare the constraints hold for all rows
>> + in the foreign tables. It is the user's responsibility to ensure
>> + that those definitions match the remote side. Such constraints are
>> + used for some kind of query optimization such as constraint
>> exclusion
>> + for partitioned tables
>
> The planner can do constraint exclusion based on CHECK constraints even
> without inheritance. It's not enabled by default because it can increase
> planning time, but if you set constraint_exclusion=on, it will work.

Exactly!

> For example:
>
> postgres=# create table foo (i int4 CHECK (i > 0));
> CREATE TABLE
> postgres=# explain select * from foo WHERE i < 0;
> QUERY PLAN
> ------------------------------------------------------
> Seq Scan on foo (cost=0.00..40.00 rows=800 width=4)
> Filter: (i < 0)
> Planning time: 0.335 ms
> (3 rows)
>
> postgres=# show constraint_exclusion ;
> constraint_exclusion
> ----------------------
> partition
> (1 row)
>
> postgres=# set constraint_exclusion ='on';
> SET
> postgres=# explain select * from foo WHERE i < 0;
> QUERY PLAN
> ------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0)
> One-Time Filter: false
> Planning time: 0.254 ms
> (3 rows)
>
> postgres=#

Actually, this patch allows the exact same thing to apply to foreign
tables. My explanation was insufficient about that. Sorry for that.

So, should I split the patch into the two?

Thanks,

Best regards,
Etsuro Fujita