From: | Michael Paquier <michael(at)paquier(dot)xyz> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Arseny Sher <a(dot)sher(at)postgrespro(dot)ru>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Indexes on partitioned tables and foreign partitions |
Date: | 2018-05-10 01:37:11 |
Message-ID: | 20180510013711.GD26879@paquier.xyz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2018-05-10 01:50:03 | Re: Postgres, fsync, and OSs (specifically linux) |
Previous Message | Amit Langote | 2018-05-10 01:15:05 | Re: Indexes on partitioned tables and foreign partitions |