Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX.

This feature allows a unique or pkey constraint to be created using an
already-existing unique index.  While the constraint isn't very
functionally different from the bare index, it's nice to be able to do that
for documentation purposes.  The main advantage over just issuing a plain
ALTER TABLE ADD UNIQUE/PRIMARY KEY is that the index can be created with
CREATE INDEX CONCURRENTLY, so that there is not a long interval where the
table is locked against updates.

On the way, refactor some of the code in DefineIndex() and index_create()
so that we don't have to pass through those functions in order to create
the index constraint's catalog entries.  Also, in parse_utilcmd.c, pass
around the ParseState pointer in struct CreateStmtContext to save on
notation, and add error location pointers to some error reports that didn't
have one before.

Gurjeet Singh, reviewed by Steve Singer and Tom Lane

2 files changed, 63 insertions, 0 deletions

diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d586f69ebbb..c78d9ee1e80 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1210,6 +1210,43 @@ Indexes:
 
 DROP TABLE concur_heap;
 --
+-- Test ADD CONSTRAINT USING INDEX
+--
+CREATE TABLE cwi_test( a int , b varchar(10), c char);
+-- add some data so that all tests have something to work with.
+INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);
+CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);
+ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;
+\d cwi_test
+          Table "public.cwi_test"
+ Column |         Type          | Modifiers 
+--------+-----------------------+-----------
+ a      | integer               | not null
+ b      | character varying(10) | not null
+ c      | character(1)          | 
+Indexes:
+    "cwi_uniq_idx" PRIMARY KEY, btree (a, b)
+
+CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);
+ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
+	ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY
+		USING INDEX cwi_uniq2_idx;
+NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "cwi_uniq2_idx" to "cwi_replaced_pkey"
+\d cwi_test
+          Table "public.cwi_test"
+ Column |         Type          | Modifiers 
+--------+-----------------------+-----------
+ a      | integer               | not null
+ b      | character varying(10) | not null
+ c      | character(1)          | 
+Indexes:
+    "cwi_replaced_pkey" PRIMARY KEY, btree (b, a)
+
+DROP INDEX cwi_replaced_pkey;	-- Should fail; a constraint depends on it
+ERROR:  cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it
+HINT:  You can drop constraint cwi_replaced_pkey on table cwi_test instead.
+DROP TABLE cwi_test;
+--
 -- Tests for IS NULL/IS NOT NULL with b-tree indexes
 --
 SELECT unique1, unique2 INTO onek_with_null FROM onek;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 97c1beb358a..31b49ca2273 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -410,6 +410,32 @@ COMMIT;
 DROP TABLE concur_heap;
 
 --
+-- Test ADD CONSTRAINT USING INDEX
+--
+
+CREATE TABLE cwi_test( a int , b varchar(10), c char);
+
+-- add some data so that all tests have something to work with.
+
+INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);
+
+CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);
+ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;
+
+\d cwi_test
+
+CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);
+ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
+	ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY
+		USING INDEX cwi_uniq2_idx;
+
+\d cwi_test
+
+DROP INDEX cwi_replaced_pkey;	-- Should fail; a constraint depends on it
+
+DROP TABLE cwi_test;
+
+--
 -- Tests for IS NULL/IS NOT NULL with b-tree indexes
 --