Обсуждение: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX

Поиск
Список
Период
Сортировка

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

От
Aditya Toshniwal
Дата:
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 TABLE some_table
(
    id serial primary key,
    first_name character varying(45),
    last_name character varying
)

CREATE INDEX ind1
    ON public.some_table USING btree
    (id)
    INCLUDE(id)
    TABLESPACE pg_default;

This query fails with error
ERROR: included columns must not intersect with key columns

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.

--
Thanks and Regards,
Aditya Toshniwal
Software Engineer | EnterpriseDB Software Solutions | Pune
"Don't Complain about Heat, Plant a tree"

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

От
Aditya Toshniwal
Дата:
Hi Team,

Please ignore the name after "Hi" in the previous mail. :/
The potential bug is a mentioned in the mail.



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 TABLE some_table
(
    id serial primary key,
    first_name character varying(45),
    last_name character varying
)

CREATE INDEX ind1
    ON public.some_table USING btree
    (id)
    INCLUDE(id)
    TABLESPACE pg_default;

This query fails with error
ERROR: included columns must not intersect with key columns

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.

--
Thanks and Regards,
Aditya Toshniwal
Software Engineer | EnterpriseDB Software Solutions | Pune
"Don't Complain about Heat, Plant a tree"



--
Thanks and Regards,
Aditya Toshniwal
Software Engineer | EnterpriseDB Software Solutions | Pune
"Don't Complain about Heat, Plant a tree"

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

От
Tom Lane
Дата:
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


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

От
Dilip Kumar
Дата:
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


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

От
Andrey Borodin
Дата:
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.

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

От
Yugo Nagata
Дата:
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>

Вложения

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

От
Yugo Nagata
Дата:
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>


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

От
Tom Lane
Дата:
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