Re: Indexes on partitioned tables and foreign partitions

On 2018/05/10 10:37, Michael Paquier wrote:
> 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.

Yes, I know it is working as designed and documented. I was just trying
to comment on this bit of Robert's email:

"...because a major point of such an index is to enforce a constraint; we
can't allege that we have such a constraint if foreign tables are just
silently skipped."

So if someday we go ahead and allow indexes to be created on partitioned
tables even if there are foreign partitions, how would we choose to deal
with a unique constraint? Will it be same as CHECK constraints such that
we don't enforce it locally but only assume it to be enforced by the
remote data source using whatever method?

But it seems I've misinterpreted what he was saying. He doesn't seem to
be saying anything about how or whether we enforce the unique constraint
on foreign tables. Only that if someone creates a constraint index on the
partitioned table, all partitions *including* foreign partitions, must get
a copy.

So for now, we give users an error if they try to create an index on a
partitioned table with a mix of local and foreign partitions. Once we
figure out how to allow creating indexes (constraint-enforcing or not) on
foreign tables, we can then think of relaxing that restriction.

Thanks,
Amit