Message-ID: <541187D5.4050301@lab.ntt.co.jp>
Date: Thu, 11 Sep 2014 20:30:29 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.0;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Heikki Linnakangas <hlinnakangas@vmware.com>,
 Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
CC: noah@leadboat.com, shigeru.hanada@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
References: <53F48245.6020204@lab.ntt.co.jp>
	<20140822025137.GA563733@tornado.leadboat.com>
	<53FEEF94.6040101@lab.ntt.co.jp>
 <20140902.142218.253402812.horiguchi.kyotaro@lab.ntt.co.jp>
 <5410A76B.20102@vmware.com> <541169C3.3030900@lab.ntt.co.jp>
 <54117D6E.3000008@vmware.com>
In-Reply-To: <54117D6E.3000008@vmware.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

(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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers