Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX
Yugo Nagata 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
Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX
On Thu, 12 Jul 2018 15:58:08 +0900 Yugo Nagata 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
Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX
Hi! > 10 июля 2018 г., в 17:54, Tom Lane написал(а): > > Aditya Toshniwal 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.
Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX
On Tue, Jul 10, 2018 at 6:37 PM, Aditya Toshniwal 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
Aditya Toshniwal 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
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.toshni...@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"
[PG-11] Potential bug related to INCLUDE clause of CREATE INDEX
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"
