From: Michael Paquier <michael@paquier.xyz>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Arseny Sher <a.sher@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Indexes on partitioned tables and foreign partitions
Date: Thu, 10 May 2018 10:37:11 +0900
Message-ID: <20180510013711.GD26879@paquier.xyz> (raw)
In-Reply-To: <be1e6f22-26ef-cbef-827c-1c8dcb3c0484@lab.ntt.co.jp>
References: <87sh71cakz.fsf@ars-thinkpad>
<CANP8+j+L5W22kuiMMwtjhanqMMos_eRfuE=K6V4Krw_hgpgEcA@mail.gmail.com>
<CA+TgmoYYEdFeAh8FOwCfB+kgji18WmOoVAEwvYBJtMSQFQ+_Ng@mail.gmail.com>
<be1e6f22-26ef-cbef-827c-1c8dcb3c0484@lab.ntt.co.jp>
On Thu, May 10, 2018 at 10:15:05AM +0900, Amit Langote wrote:
> While I agree with this, let me point out that we do allow inherited check
> constraints on foreign tables that are not actually enforced locally.
>
> create table p (a int) partition by range (a);
> create table p1 partition of p for values from (minvalue) to (1);
> create table p2base (a int);
> create foreign table p2 partition of p for values from (1) to (maxvalue)
> server loopback options (table_name 'p2base');
>
> alter table p add check (a between -1000 and 1000);
>
> -- routed to foreign partition, which doesn't enforce check constraints
> insert into p values (1001);
> INSERT 0 1
That's not actually a surprise, right? Since foreign tables can be part
of inheritance trees in 9.5, CHECK constraints on foreign tables are not
enforced locally, but used as planner hints to guess how a query would
work remotely. So getting partition children to work the same way is
consistent.
> We have to do the following to prevent that.
>
> alter table p2base add check (a between -1000 and 1000);
> insert into p values (1001);
> ERROR: new row for relation "p2base" violates check constraint
> "p2base_a_check"
> DETAIL: Failing row contains (1001).
> CONTEXT: remote SQL command: INSERT INTO public.p2base(a) VALUES ($1)
This bit looks natural to me as well.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
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: michael@paquier.xyz, Langote_Amit_f8@lab.ntt.co.jp, robertmhaas@gmail.com, simon@2ndquadrant.com, a.sher@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Indexes on partitioned tables and foreign partitions
In-Reply-To: <20180510013711.GD26879@paquier.xyz>
* 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