Indexes with INCLUDE columns and their support in B-tree

This patch introduces INCLUDE clause to index definition.  This clause
specifies a list of columns which will be included as a non-key part in
the index.  The INCLUDE columns exist solely to allow more queries to
benefit from index-only scans.  Also, such columns don't need to have
appropriate operator classes.  Expressions are not supported as INCLUDE
columns since they cannot be used in index-only scans.

Index access methods supporting INCLUDE are indicated by amcaninclude flag
in IndexAmRoutine.  For now, only B-tree indexes support INCLUDE clause.

In B-tree indexes INCLUDE columns are truncated from pivot index tuples
(tuples located in non-leaf pages and high keys).  Therefore, B-tree indexes
now might have variable number of attributes.  This patch also provides
generic facility to support that: pivot tuples contain number of their
attributes in t_tid.ip_posid.  Free 13th bit of t_info is used for indicating
that.  This facility will simplify further support of index suffix truncation.
The changes of above are backward-compatible, pg_upgrade doesn't need special
handling of B-tree indexes for that.

Bump catalog version

Author: Anastasia Lubennikova with contribition by Alexander Korotkov and me
Reviewed by: Peter Geoghegan, Tomas Vondra, Antonin Houska, Jeff Janes,
			 David Rowley, Alexander Korotkov
Discussion: https://www.postgresql.org/message-id/flat/56168952.4010101@postgrespro.ru

11 files changed, 613 insertions, 8 deletions

diff --git a/src/test/isolation/specs/insert-conflict-do-nothing-2.spec b/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
index f1e5bde357c..8a8ec944473 100644
--- a/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
+++ b/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
@@ -3,7 +3,7 @@
 
 setup
 {
-  CREATE TABLE ints (key int primary key, val text);
+  CREATE TABLE ints (key int, val text, PRIMARY KEY (key) INCLUDE (val));
 }
 
 teardown
diff --git a/src/test/isolation/specs/insert-conflict-do-update-2.spec b/src/test/isolation/specs/insert-conflict-do-update-2.spec
index cd7e3f42feb..f5b4f601b58 100644
--- a/src/test/isolation/specs/insert-conflict-do-update-2.spec
+++ b/src/test/isolation/specs/insert-conflict-do-update-2.spec
@@ -7,7 +7,7 @@
 setup
 {
   CREATE TABLE upsert (key text not null, payload text);
-  CREATE UNIQUE INDEX ON upsert(lower(key));
+  CREATE UNIQUE INDEX ON upsert(lower(key)) INCLUDE (payload);
 }
 
 teardown
diff --git a/src/test/isolation/specs/lock-committed-keyupdate.spec b/src/test/isolation/specs/lock-committed-keyupdate.spec
index 1630282d0f4..3fb424af0ed 100644
--- a/src/test/isolation/specs/lock-committed-keyupdate.spec
+++ b/src/test/isolation/specs/lock-committed-keyupdate.spec
@@ -8,7 +8,7 @@
 setup
 {
     DROP TABLE IF EXISTS lcku_table;
-    CREATE TABLE lcku_table (id INTEGER PRIMARY KEY, value TEXT);
+    CREATE TABLE lcku_table (id INTEGER, value TEXT, PRIMARY KEY (id) INCLUDE (value));
     INSERT INTO lcku_table VALUES (1, 'one');
     INSERT INTO lcku_table VALUES (3, 'two');
 }
diff --git a/src/test/isolation/specs/lock-update-traversal.spec b/src/test/isolation/specs/lock-update-traversal.spec
index 7042b9399cf..2ffe87d152b 100644
--- a/src/test/isolation/specs/lock-update-traversal.spec
+++ b/src/test/isolation/specs/lock-update-traversal.spec
@@ -7,8 +7,9 @@
 setup
 {
   CREATE TABLE foo (
-	key		int PRIMARY KEY,
-	value	int
+	key		int,
+	value	int,
+	PRIMARY KEY (key) INCLUDE (value)
   );
 
   INSERT INTO foo VALUES (1, 1);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 09757c5a749..fe5b698669c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2433,6 +2433,25 @@ DETAIL:  Key ((f1 || f2))=(ABCDEF) already exists.
 -- but this shouldn't:
 INSERT INTO func_index_heap VALUES('QWERTY');
 --
+-- Test unique index with included columns
+--
+CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text);
+CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3);
+INSERT INTO covering_index_heap VALUES(1,1,'AAA');
+INSERT INTO covering_index_heap VALUES(1,2,'AAA');
+-- this should fail because of unique index on f1,f2:
+INSERT INTO covering_index_heap VALUES(1,2,'BBB');
+ERROR:  duplicate key value violates unique constraint "covering_index_index"
+DETAIL:  Key (f1, f2)=(1, 2) already exists.
+-- and this shouldn't:
+INSERT INTO covering_index_heap VALUES(1,4,'AAA');
+-- Try to build index on table that already contains data
+CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3);
+-- Try to use existing covering index as primary key
+ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX
+covering_pkey;
+DROP TABLE covering_index_heap;
+--
 -- Also try building functional, expressional, and partial indexes on
 -- tables that already contain data.
 --
diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out
new file mode 100644
index 00000000000..1d253ee77d6
--- /dev/null
+++ b/src/test/regress/expected/index_including.out
@@ -0,0 +1,346 @@
+/*
+ * 1.test CREATE INDEX
+ */
+-- Regular index with included columns
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_idx ON tbl using btree (c1, c2) INCLUDE (c3,c4);
+-- must fail because of intersection of key and included columns
+CREATE INDEX tbl_idx ON tbl using btree (c1, c2) INCLUDE (c1,c3);
+ERROR:  included columns must not intersect with key columns
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+                             pg_get_indexdef                              
+--------------------------------------------------------------------------
+ CREATE INDEX tbl_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+DROP TABLE tbl;
+-- Unique index and unique constraint
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ALTER TABLE tbl add UNIQUE USING INDEX tbl_idx_unique;
+ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+                                       pg_get_indexdef                                       
+---------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+ CREATE UNIQUE INDEX tbl_idx_unique ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(2 rows)
+
+DROP TABLE tbl;
+-- Unique index and unique constraint. Both must fail.
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ERROR:  could not create unique index "tbl_idx_unique"
+DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
+ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
+ERROR:  could not create unique index "tbl_c1_c2_c3_c4_key"
+DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
+DROP TABLE tbl;
+-- PK constraint
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+                                 pg_get_indexdef                                  
+----------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_pkey ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+DROP TABLE tbl;
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ALTER TABLE tbl add PRIMARY KEY USING INDEX tbl_idx_unique;
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+                                    pg_get_indexdef                                     
+----------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_idx_unique ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+DROP TABLE tbl;
+-- PK constraint. Must fail.
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
+ERROR:  could not create unique index "tbl_pkey"
+DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
+DROP TABLE tbl;
+/*
+ * 2. Test CREATE TABLE with constraint
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				CONSTRAINT covering UNIQUE(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+ indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey  | indclass  
+------------+----------+-------------+-------------+--------------+---------+-----------
+ covering   |        4 |           2 | t           | f            | 1 2 3 4 | 1978 1978
+(1 row)
+
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+       pg_get_constraintdef       | conname  | conkey | conincluding 
+----------------------------------+----------+--------+--------------
+ UNIQUE (c1, c2) INCLUDE (c3, c4) | covering | {1,2}  | {3,4}
+(1 row)
+
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  duplicate key value violates unique constraint "covering"
+DETAIL:  Key (c1, c2)=(1, 2) already exists.
+DROP TABLE tbl;
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				CONSTRAINT covering PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+ indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey  | indclass  
+------------+----------+-------------+-------------+--------------+---------+-----------
+ covering   |        4 |           2 | t           | t            | 1 2 3 4 | 1978 1978
+(1 row)
+
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+         pg_get_constraintdef          | conname  | conkey | conincluding 
+---------------------------------------+----------+--------+--------------
+ PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | covering | {1,2}  | {3,4}
+(1 row)
+
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  duplicate key value violates unique constraint "covering"
+DETAIL:  Key (c1, c2)=(1, 2) already exists.
+INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  null value in column "c2" violates not-null constraint
+DETAIL:  Failing row contains (1, null, 3, (4,4),(4,4)).
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				UNIQUE(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+     indexrelid      | indnatts | indnkeyatts | indisunique | indisprimary | indkey  | indclass  
+---------------------+----------+-------------+-------------+--------------+---------+-----------
+ tbl_c1_c2_c3_c4_key |        4 |           2 | t           | f            | 1 2 3 4 | 1978 1978
+(1 row)
+
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+       pg_get_constraintdef       |       conname       | conkey | conincluding 
+----------------------------------+---------------------+--------+--------------
+ UNIQUE (c1, c2) INCLUDE (c3, c4) | tbl_c1_c2_c3_c4_key | {1,2}  | {3,4}
+(1 row)
+
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  duplicate key value violates unique constraint "tbl_c1_c2_c3_c4_key"
+DETAIL:  Key (c1, c2)=(1, 2) already exists.
+DROP TABLE tbl;
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+ indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey  | indclass  
+------------+----------+-------------+-------------+--------------+---------+-----------
+ tbl_pkey   |        4 |           2 | t           | t            | 1 2 3 4 | 1978 1978
+(1 row)
+
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+         pg_get_constraintdef          | conname  | conkey | conincluding 
+---------------------------------------+----------+--------+--------------
+ PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | tbl_pkey | {1,2}  | {3,4}
+(1 row)
+
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  duplicate key value violates unique constraint "tbl_pkey"
+DETAIL:  Key (c1, c2)=(1, 2) already exists.
+INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  null value in column "c2" violates not-null constraint
+DETAIL:  Failing row contains (1, null, 3, (4,4),(4,4)).
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+    indexrelid     | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 
+-------------------+----------+-------------+-------------+--------------+--------+----------
+ tbl_c1_c3_c4_excl |        3 |           1 | f           | f            | 1 3 4  | 1978
+(1 row)
+
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+               pg_get_constraintdef               |      conname      | conkey | conincluding 
+--------------------------------------------------+-------------------+--------+--------------
+ EXCLUDE USING btree (c1 WITH =) INCLUDE (c3, c4) | tbl_c1_c3_c4_excl | {1}    | {3,4}
+(1 row)
+
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ERROR:  conflicting key value violates exclusion constraint "tbl_c1_c3_c4_excl"
+DETAIL:  Key (c1)=(1) conflicts with existing key (c1)=(1).
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+/*
+ * 3.0 Test ALTER TABLE DROP COLUMN.
+ * Any column deletion leads to index deletion.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int);
+CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+                                indexdef                                
+------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2, c3, c4)
+(1 row)
+
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+DROP TABLE tbl;
+/*
+ * 3.1 Test ALTER TABLE DROP COLUMN.
+ * Included column deletion leads to the index deletion,
+ * AS well AS key columns deletion. It's explained in documentation.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box);
+CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+                                    indexdef                                     
+---------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+DROP TABLE tbl;
+/*
+ * 3.2 Test ALTER TABLE DROP COLUMN.
+ * Included column deletion leads to the index deletion.
+ * AS well AS key columns deletion. It's explained in documentation.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+                                          indexdef                                           
+---------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+ALTER TABLE tbl DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+DROP TABLE tbl;
+/*
+ * 4. CREATE INDEX CONCURRENTLY
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x;
+CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+                                          indexdef                                           
+---------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+ CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(2 rows)
+
+DROP TABLE tbl;
+/*
+ * 5. REINDEX
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+                                          indexdef                                           
+---------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
+(1 row)
+
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+REINDEX INDEX tbl_c1_c2_c3_c4_key;
+ERROR:  relation "tbl_c1_c2_c3_c4_key" does not exist
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+ALTER TABLE tbl DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+DROP TABLE tbl;
+/*
+ * 7. Check various AMs. All but btree must fail.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
+CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
+ERROR:  access method "brin" does not support included columns
+CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
+ERROR:  access method "gist" does not support included columns
+CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
+ERROR:  access method "spgist" does not support included columns
+CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
+ERROR:  access method "gin" does not support included columns
+CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
+ERROR:  access method "hash" does not support included columns
+CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
+NOTICE:  substituting access method "gist" for obsolete method "rtree"
+ERROR:  access method "gist" does not support included columns
+CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);
+DROP TABLE tbl;
+/*
+ * 8. Update, delete values in indexed table.
+ */
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4);
+UPDATE tbl SET c1 = 100 WHERE c1 = 2;
+UPDATE tbl SET c1 = 1 WHERE c1 = 3;
+-- should fail
+UPDATE tbl SET c2 = 2 WHERE c1 = 1;
+ERROR:  duplicate key value violates unique constraint "tbl_idx_unique"
+DETAIL:  Key (c1, c2)=(1, 2) already exists.
+UPDATE tbl SET c3 = 1;
+DELETE FROM tbl WHERE c1 = 5 OR c3 = 12;
+DROP TABLE tbl;
+/*
+ * 9. Alter column type.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl ALTER c1 TYPE bigint;
+ALTER TABLE tbl ALTER c3 TYPE bigint;
+\d tbl
+                Table "public.tbl"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ c1     | bigint  |           |          | 
+ c2     | integer |           |          | 
+ c3     | bigint  |           |          | 
+ c4     | box     |           |          | 
+Indexes:
+    "tbl_c1_c2_c3_c4_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDE (c3, c4)
+
+DROP TABLE tbl;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 00c324dd444..0d3a27ed410 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -55,7 +55,7 @@ test: copy copyselect copydml
 # ----------
 test: create_misc create_operator create_procedure
 # These depend on the above two
-test: create_index create_view
+test: create_index create_view index_including
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 39c3fa9c850..20027c131c2 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -65,6 +65,7 @@ test: create_misc
 test: create_operator
 test: create_procedure
 test: create_index
+test: index_including
 test: create_view
 test: create_aggregate
 test: create_function_3
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index c9671a4e13e..f7731265a08 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -742,6 +742,26 @@ INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 INSERT INTO func_index_heap VALUES('QWERTY');
 
 --
+-- Test unique index with included columns
+--
+CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text);
+CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3);
+
+INSERT INTO covering_index_heap VALUES(1,1,'AAA');
+INSERT INTO covering_index_heap VALUES(1,2,'AAA');
+-- this should fail because of unique index on f1,f2:
+INSERT INTO covering_index_heap VALUES(1,2,'BBB');
+-- and this shouldn't:
+INSERT INTO covering_index_heap VALUES(1,4,'AAA');
+-- Try to build index on table that already contains data
+CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3);
+-- Try to use existing covering index as primary key
+ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX
+covering_pkey;
+DROP TABLE covering_index_heap;
+
+
+--
 -- Also try building functional, expressional, and partial indexes on
 -- tables that already contain data.
 --
diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql
new file mode 100644
index 00000000000..caedc9866d3
--- /dev/null
+++ b/src/test/regress/sql/index_including.sql
@@ -0,0 +1,203 @@
+/*
+ * 1.test CREATE INDEX
+ */
+
+-- Regular index with included columns
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_idx ON tbl using btree (c1, c2) INCLUDE (c3,c4);
+-- must fail because of intersection of key and included columns
+CREATE INDEX tbl_idx ON tbl using btree (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'::regclass ORDER BY c.relname;
+DROP TABLE tbl;
+
+-- Unique index and unique constraint
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ALTER TABLE tbl add UNIQUE USING INDEX tbl_idx_unique;
+ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+DROP TABLE tbl;
+
+-- Unique index and unique constraint. Both must fail.
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
+DROP TABLE tbl;
+
+-- PK constraint
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+DROP TABLE tbl;
+
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
+ALTER TABLE tbl add PRIMARY KEY USING INDEX tbl_idx_unique;
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
+DROP TABLE tbl;
+
+-- PK constraint. Must fail.
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
+DROP TABLE tbl;
+
+
+/*
+ * 2. Test CREATE TABLE with constraint
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				CONSTRAINT covering UNIQUE(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				CONSTRAINT covering PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				UNIQUE(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
+				EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4));
+SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
+SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
+-- ensure that constraint works
+INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
+DROP TABLE tbl;
+
+/*
+ * 3.0 Test ALTER TABLE DROP COLUMN.
+ * Any column deletion leads to index deletion.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int);
+CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+DROP TABLE tbl;
+
+/*
+ * 3.1 Test ALTER TABLE DROP COLUMN.
+ * Included column deletion leads to the index deletion,
+ * AS well AS key columns deletion. It's explained in documentation.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box);
+CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+DROP TABLE tbl;
+
+/*
+ * 3.2 Test ALTER TABLE DROP COLUMN.
+ * Included column deletion leads to the index deletion.
+ * AS well AS key columns deletion. It's explained in documentation.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+DROP TABLE tbl;
+
+
+/*
+ * 4. CREATE INDEX CONCURRENTLY
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x;
+CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+DROP TABLE tbl;
+
+
+/*
+ * 5. REINDEX
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c3;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+REINDEX INDEX tbl_c1_c2_c3_c4_key;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+ALTER TABLE tbl DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
+DROP TABLE tbl;
+
+/*
+ * 7. Check various AMs. All but btree must fail.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
+CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
+CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
+CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);
+DROP TABLE tbl;
+
+/*
+ * 8. Update, delete values in indexed table.
+ */
+CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4);
+UPDATE tbl SET c1 = 100 WHERE c1 = 2;
+UPDATE tbl SET c1 = 1 WHERE c1 = 3;
+-- should fail
+UPDATE tbl SET c2 = 2 WHERE c1 = 1;
+UPDATE tbl SET c3 = 1;
+DELETE FROM tbl WHERE c1 = 5 OR c3 = 12;
+DROP TABLE tbl;
+
+/*
+ * 9. Alter column type.
+ */
+CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
+INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
+ALTER TABLE tbl ALTER c1 TYPE bigint;
+ALTER TABLE tbl ALTER c3 TYPE bigint;
+\d tbl
+DROP TABLE tbl;
+
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index e0104cd8d05..4050e82bc9f 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -3,7 +3,7 @@ use strict;
 use warnings;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 16;
+use Test::More tests => 17;
 
 # Initialize publisher node
 my $node_publisher = get_new_node('publisher');
@@ -31,6 +31,8 @@ $node_publisher->safe_psql('postgres',
 	"CREATE TABLE tab_mixed (a int primary key, b text)");
 $node_publisher->safe_psql('postgres',
 	"INSERT INTO tab_mixed (a, b) VALUES (1, 'foo')");
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab_include (a int, b text, CONSTRAINT covering PRIMARY KEY(a) INCLUDE(b))");
 
 # Setup structure on subscriber
 $node_subscriber->safe_psql('postgres', "CREATE TABLE tab_notrep (a int)");
@@ -44,13 +46,17 @@ $node_subscriber->safe_psql('postgres',
 $node_subscriber->safe_psql('postgres',
 	"CREATE TABLE tab_mixed (c text, b text, a int primary key)");
 
+# replication of the table with included index
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE tab_include (a int, b text, CONSTRAINT covering PRIMARY KEY(a) INCLUDE(b))");
+
 # Setup logical replication
 my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
 $node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub");
 $node_publisher->safe_psql('postgres',
 	"CREATE PUBLICATION tap_pub_ins_only WITH (publish = insert)");
 $node_publisher->safe_psql('postgres',
-"ALTER PUBLICATION tap_pub ADD TABLE tab_rep, tab_full, tab_full2, tab_mixed"
+"ALTER PUBLICATION tap_pub ADD TABLE tab_rep, tab_full, tab_full2, tab_mixed, tab_include"
 );
 $node_publisher->safe_psql('postgres',
 	"ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_ins");
@@ -89,6 +95,11 @@ $node_publisher->safe_psql('postgres', "UPDATE tab_rep SET a = -a");
 $node_publisher->safe_psql('postgres',
 	"INSERT INTO tab_mixed VALUES (2, 'bar')");
 
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO tab_include SELECT generate_series(1,50)");
+$node_publisher->safe_psql('postgres', "DELETE FROM tab_include WHERE a > 20");
+$node_publisher->safe_psql('postgres', "UPDATE tab_include SET a = -a");
+
 $node_publisher->wait_for_catchup($appname);
 
 $result = $node_subscriber->safe_psql('postgres',
@@ -104,6 +115,10 @@ $result =
 is( $result, qq(|foo|1
 |bar|2), 'check replicated changes with different column order');
 
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT count(*), min(a), max(a) FROM tab_include");
+is($result, qq(20|-20|-1), 'check replicated changes with primary key index with included columns');
+
 # insert some duplicate rows
 $node_publisher->safe_psql('postgres',
 	"INSERT INTO tab_full SELECT generate_series(1,10)");