From 88452d5ba6b3e8ad49133ac1a660ce0725710f8c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 25 Jan 2011 15:42:03 -0500 Subject: 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 --- doc/src/sgml/ref/alter_table.sgml | 79 ++++++++++++++++++++++++++++++++++++++- 1 file changed, 78 insertions(+), 1 deletion(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 17a1d34d08a..bba690d5230 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -43,6 +43,7 @@ ALTER TABLE name ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint + ADD table_constraint_using_index DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] @@ -62,6 +63,12 @@ ALTER TABLE name NO INHERIT parent_table OWNER TO new_owner SET TABLESPACE new_tablespace + +and table_constraint_using_index is: + + [ CONSTRAINT constraint_name ] + { UNIQUE | PRIMARY KEY } USING INDEX index_name + [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -229,6 +236,57 @@ ALTER TABLE name + + ADD table_constraint_using_index + + + This form adds a new PRIMARY KEY or UNIQUE + constraint to a table based on an existing unique index. All the + columns of the index will be included in the constraint. + + + + The index cannot have expression columns nor be a partial index. + Also, it must be a b-tree index with default sort ordering. These + restrictions ensure that the index is equivalent to one that would be + built by a regular ADD PRIMARY KEY or ADD UNIQUE + command. + + + + If PRIMARY KEY is specified, and the index's columns are not + already marked NOT NULL, then this command will attempt to + do ALTER COLUMN SET NOT NULL against each such column. + That requires a full table scan to verify the column(s) contain no + nulls. In all other cases, this is a fast operation. + + + + If a constraint name is provided then the index will be renamed to match + the constraint name. Otherwise the constraint will be named the same as + the index. + + + + After this command is executed, the index is owned by the + constraint, in the same way as if the index had been built by + a regular ADD PRIMARY KEY or ADD UNIQUE + command. In particular, dropping the constraint will make the index + disappear too. + + + + + Adding a constraint using an existing index can be helpful in + situations where a new constraint needs to be added without blocking + table updates for a long time. To do that, create the index using + CREATE INDEX CONCURRENTLY, and then install it as an + official constraint using this syntax. See the example below. + + + + + DROP CONSTRAINT [ IF EXISTS ] @@ -920,13 +978,24 @@ ALTER TABLE myschema.distributors SET SCHEMA yourschema; + + To recreate a primary key constraint, without blocking updates while the + index is rebuilt: + +CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx on distributors (dist_id); +ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, + ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; + + + Compatibility - The forms ADD, DROP, SET DEFAULT, + The forms ADD (without USING INDEX), + DROP, SET DEFAULT, and SET DATA TYPE (without USING) conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. @@ -940,4 +1009,12 @@ ALTER TABLE myschema.distributors SET SCHEMA yourschema; extension of SQL, which disallows zero-column tables. + + + See Also + + + + + -- cgit v1.2.3