Re: Indexes on partitioned tables and foreign partitions

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