Thread: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX

Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> writes:
> I am working on a feature to support INCLUDE clause of index in PG-11. As
> per the documentation https://www.postgresql.org/docs/11/static/
> sql-createindex.html, columns listed in INCLUDE clause cannot also be
> present as index key columns. But I find different behaviour for below
> queries which are logically identical.

I wonder why there is any such restriction at all.  We have never
attempted to prevent the creation of "silly" indexes, eg

regression=# create table some_table (id int);
CREATE TABLE
regression=# create index on some_table (id,id);
CREATE INDEX
regression=# \d+ some_table
                                Table "public.some_table"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Indexes:
    "some_table_id_id1_idx" btree (id, id)

So my inclination is to rip out the "must not intersect" test altogether,
not try to make it a bit smarter.

            regards, tom lane

On Tue, Jul 10, 2018 at 6:37 PM, Aditya Toshniwal
<aditya.toshniwal@enterprisedb.com> wrote:
> Hi Dave,
>
> I am working on a feature to support INCLUDE clause of index in PG-11. As
> per the documentation
> https://www.postgresql.org/docs/11/static/sql-createindex.html, columns
> listed in INCLUDE clause cannot also be present as index key columns. But I
> find different behaviour for below queries which are logically identical.
>

>
> CREATE INDEX ind1
>     ON public.some_table USING btree
>     (id asc nulls last)
>     INCLUDE(id)
>     TABLESPACE pg_default;
>
> This query passes and index is created.
>
> Kindly let me know if I am missing anything.
>

Seems like a bug to me.  I think the problem is while checking whether
the INCLUDE column intersects with the index key or not it will
compare the "IndexElem" of INCLUDE with the "IndexElem" of the index
key.  So if any field of the "IndexElem" is not same then it will be
considered as non-intersecting and in this example, the ORDER is not
matching.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Hi!

> 10 июля 2018 г., в 17:54, Tom Lane <tgl@sss.pgh.pa.us> написал(а):
>
> Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> writes:
>> I am working on a feature to support INCLUDE clause of index in PG-11. As
>> per the documentation https://www.postgresql.org/docs/11/static/
>> sql-createindex.html, columns listed in INCLUDE clause cannot also be
>> present as index key columns. But I find different behaviour for below
>> queries which are logically identical.
>
> I wonder why there is any such restriction at all.  We have never
> attempted to prevent the creation of "silly" indexes [...] So my inclination is to rip out the "must not intersect"
testaltogether, 
> not try to make it a bit smarter

It seems to me valid way of reaching the completely consistent validation behavior. But there are some other validation
stepsthat seem useful: e.g. "ERROR:  including column does not support ASC/DESC options" and "ERROR:  including column
doesnot support NULLS FIRST/LAST options". 

IMHO it is not a bug. CREATE INDEX ON some_table(id+0) INCLUDE (id); or some similar tricks will work anyway.

Best regards, Andrey Borodin.

On Tue, 10 Jul 2018 20:37:49 +0400
Andrey Borodin <x4mmm@yandex-team.ru> wrote:

> Hi!
> 
> > 10 июля 2018 г., в 17:54, Tom Lane <tgl@sss.pgh.pa.us> написал(а):
> > 
> > Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> writes:
> >> I am working on a feature to support INCLUDE clause of index in PG-11. As
> >> per the documentation https://www.postgresql.org/docs/11/static/
> >> sql-createindex.html, columns listed in INCLUDE clause cannot also be
> >> present as index key columns. But I find different behaviour for below
> >> queries which are logically identical.
> > 
> > I wonder why there is any such restriction at all.  We have never
> > attempted to prevent the creation of "silly" indexes [...] So my inclination is to rip out the "must not intersect"
testaltogether,
 
> > not try to make it a bit smarter
> 
> It seems to me valid way of reaching the completely consistent validation behavior. But there are some other
validationsteps that seem useful: e.g. "ERROR:  including column does not support ASC/DESC options" and "ERROR:
includingcolumn does not support NULLS FIRST/LAST options".
 
> 
> IMHO it is not a bug. CREATE INDEX ON some_table(id+0) INCLUDE (id); or some similar tricks will work anyway.

Yes, more simplly, the following query also works;

 CREATE INDEX ON test((i)) INCLUDE (i);

However, a problem is that when we use pg_dump for the database, this generate the following query

 CREATE INDEX test_i_i1_idx ON public.test USING btree (i) INCLUDE (i);

Of cause, this causes the "must not intersect" error, and we cannot restore this dump.

To fix this, we agree with Tom about getting rid of "must not intersect" restriction.
A patch is attached for this

Regards,

-- 
Yugo Nagata <nagata@sraoss.co.jp>

On Thu, 12 Jul 2018 15:58:08 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:
 
> Yes, more simplly, the following query also works;
> 
>  CREATE INDEX ON test((i)) INCLUDE (i);
> 
> However, a problem is that when we use pg_dump for the database, this generate the following query
> 
>  CREATE INDEX test_i_i1_idx ON public.test USING btree (i) INCLUDE (i);
> 
> Of cause, this causes the "must not intersect" error, and we cannot restore this dump.
> 
> To fix this, we agree with Tom about getting rid of "must not intersect" restriction.
> A patch is attached for this

Should we add this to PG11 open items?




-- 
Yugo Nagata <nagata@sraoss.co.jp>

Yugo Nagata <nagata@sraoss.co.jp> writes:
> To fix this, we agree with Tom about getting rid of "must not intersect" restriction.
> A patch is attached for this

Pushed, after fixing documentation and regression tests to match.

            regards, tom lane