From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: noah@leadboat.com
Cc: shigeru.hanada@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
Date: Thu, 11 Sep 2014 20:30:29 +0900
Message-ID: <541187D5.4050301@lab.ntt.co.jp> (raw)
In-Reply-To: <54117D6E.3000008@vmware.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, hlinnakangas@vmware.com, horiguchi.kyotaro@lab.ntt.co.jp, noah@leadboat.com, shigeru.hanada@gmail.com
Subject: Re: inherit support for foreign tables
In-Reply-To: <541187D5.4050301@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox