On Tue, 10 Jul 2018 20:37:49 +0400
Andrey Borodin <x4...@yandex-team.ru> wrote:
> Hi!
>
> > 10 июля 2018 г., в 17:54, Tom Lane <t...@sss.pgh.pa.us> написал(а):
> >
> > Aditya Toshniwal <aditya.toshni...@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 <nag...@sraoss.co.jp>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 0053832..c4f0bb5 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -369,11 +369,6 @@ DefineIndex(Oid relationId,
Snapshot snapshot;
int i;
- if (list_intersection(stmt->indexParams, stmt->indexIncludingParams) != NIL)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("included columns must not intersect with key columns")));
-
/*
* count key attributes in index
*/
