Re: Indexes on partitioned tables and foreign partitions

On 2018/05/09 23:57, Robert Haas wrote:
> For right now, I think the options are (1) throw an ERROR if we
> encounter a foreign table or (2) silently skip the foreign table. I
> think (2) is defensible for non-UNIQUE indexes, because the index is
> just a performance optimization.

Along with others, my +1 to option 1.

> However, for UNIQUE indexes, at
> least, it seems like we'd better do (1), 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.

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

-- whereas, local partition does
insert into p values (-1001);
ERROR: new row for relation "p1" violates check constraint "p_a_check"
DETAIL: Failing row contains (-1001).

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)

Thanks,
Amit