Re: Partitioned tables and covering indexes

On Tue, Apr 10, 2018 at 12:47 PM, Amit Langote <
Langote_Amit_f8@lab.ntt.co.jp> wrote:

> On 2018/04/10 16:07, Jaime Casanova wrote:
> > Hi,
> >
> > Trying covering indexes on partitioned tables i get this error
> > """
> > postgres=# create index on t1_part (i) include (t);
> > ERROR:  cache lookup failed for opclass 0
> > """
> >
> > To reproduce:
> >
> > create table t1_part (i int, t text) partition by hash (i);
> > create table t1_part_0 partition of t1_part for values with (modulus
> > 2, remainder 0);
> > create table t1_part_1 partition of t1_part for values with (modulus
> > 2, remainder 1);
> > insert into t1_part values (1, repeat('abcdefquerty', 20));
> >
> > create index on t1_part (i) include (t);
>
> It seems that the bug is caused due to the original IndexStmt that
> DefineIndex receives being overwritten when processing the INCLUDE
> columns.  Also, the original copy of it should be used when recursing for
> defining the index in partitions.
>
> Does the attached fix look correct?  Haven't checked the fix with ATTACH
> PARTITION though.
>

Attached patch seems to fix the problem.  However, I would rather get
rid of modifying stmt->indexParams.  That seems to be more logical
for me.  Also, it would be good to check some covering indexes on
partitioned tables.  See the attached patch.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company