aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2018-07-18 14:43:03 -0400
committerTom Lane <tgl@sss.pgh.pa.us>2018-07-18 14:43:03 -0400
commit701fd0bbc98fe8211d36e96f90753985104cd295 (patch)
tree7ebc4d374bff5c83699bcc2b1f62ac12dd8ae3d6 /src
parent3cb646264e8ced9f25557ce271284da512d92043 (diff)
downloadpostgresql-701fd0bbc98fe8211d36e96f90753985104cd295.tar.gz
postgresql-701fd0bbc98fe8211d36e96f90753985104cd295.zip
Drop the rule against included index columns duplicating key columns.
The initial version of the included-index-column feature stated that included columns couldn't be the same as any key column of the index. While it'd be pretty silly to do that, since the included column would be entirely redundant, we've never prohibited redundant index columns before so it's not very consistent to do so here. Moreover, the prohibition was itself badly implemented, so that it failed to reject columns that were effectively identical but not spelled quite alike, as reported by Aditya Toshniwal. (Moreover, it's not hard to imagine that for some non-btree index types, such cases would be non-silly anyhow: the index might use a lossy representation for key columns but be able to support retrieval of the original form of included columns.) Hence, let's just drop the prohibition. In passing, do some copy-editing on the documentation for the included-column feature. Yugo Nagata; documentation and test corrections by me Discussion: https://postgr.es/m/CAM9w-_mhBCys4fQNfaiQKTRrVWtoFrZ-wXmDuE9Nj5y-Y7aDKQ@mail.gmail.com
Diffstat (limited to 'src')
-rw-r--r--src/backend/commands/indexcmds.c7
-rw-r--r--src/test/regress/expected/index_including.out14
-rw-r--r--src/test/regress/sql/index_including.sql6
3 files changed, 11 insertions, 16 deletions
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 00538321957..b9dad9672ef 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
*/
@@ -596,7 +591,7 @@ DefineIndex(Oid relationId,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("access method \"%s\" does not support unique indexes",
accessMethodName)));
- if (list_length(stmt->indexIncludingParams) > 0 && !amRoutine->amcaninclude)
+ if (stmt->indexIncludingParams != NIL && !amRoutine->amcaninclude)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("access method \"%s\" does not support included columns",
diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out
index ee976994a44..48bec3bf77a 100644
--- a/src/test/regress/expected/index_including.out
+++ b/src/test/regress/expected/index_including.out
@@ -7,17 +7,17 @@
-- Regular index with included columns
CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
-CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c3,c4);
--- must fail because of intersection of key and included columns
-CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c1,c3);
-ERROR: included columns must not intersect with key columns
+CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);
+-- duplicate column is pretty pointless, but we allow it anyway
+CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;
- pg_get_indexdef
---------------------------------------------------------------------------------------------------
+ pg_get_indexdef
+---------------------------------------------------------------------------------------------------------------
+ CREATE INDEX tbl_include_reg_c1_c2_c11_c3_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c1, c3)
CREATE INDEX tbl_include_reg_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c3, c4)
-(1 row)
+(2 rows)
-- Unique index and unique constraint
CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box);
diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql
index b59adeb845e..2d833fc9f74 100644
--- a/src/test/regress/sql/index_including.sql
+++ b/src/test/regress/sql/index_including.sql
@@ -8,9 +8,9 @@
-- Regular index with included columns
CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
-CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c3,c4);
--- must fail because of intersection of key and included columns
-CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c1,c3);
+CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);
+-- duplicate column is pretty pointless, but we allow it anyway
+CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;