Horton

Thread

Commits

  1. Drop the rule against included index columns duplicating key columns.

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

    Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> — 2018-07-10T13:07:54Z

    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"

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

    Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> — 2018-07-10T13:11:18Z

    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"

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-07-10T13:54:55Z

    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

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

    Dilip Kumar <dilipbalaut@gmail.com> — 2018-07-10T15:44:06Z

    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

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

    x4mmm@yandex-team.ru — 2018-07-10T16:37:49Z

    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" test altogether,
> 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 steps that seem useful: e.g. "ERROR:  including column does not support ASC/DESC options" and "ERROR:  including column 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.

Best regards, Andrey Borodin.

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

    Yugo Nagata <nagata@sraoss.co.jp> — 2018-07-12T06:58:08Z

    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" test altogether,
> > 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 steps that seem useful: e.g. "ERROR:  including column does not support ASC/DESC options" and "ERROR:  including column 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>

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

    Yugo Nagata <nagata@sraoss.co.jp> — 2018-07-12T07:20:13Z

    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>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-07-18T18:44:43Z

    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