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