diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index e475403..0bfc2fd 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -288,7 +288,7 @@ Boot_DeclareIndexStmt: $10, NULL, NIL, NIL, false, false, false, false, false, - false, false, true, false, false); + false, false, true, false, false, false); do_end(); } ; @@ -306,7 +306,7 @@ Boot_DeclareUniqueIndexStmt: $11, NULL, NIL, NIL, true, false, false, false, false, - false, false, true, false, false); + false, false, true, false, false, false); do_end(); } ; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 2b92e46..f919040 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -512,6 +512,7 @@ UpdateIndexRelation(Oid indexoid, * allow_system_table_mods: allow table to be a system catalog * skip_build: true to skip the index_build() step for the moment; caller * must do it later (typically via reindex_index()) + * index_exists: the index already exists, we are here just for formalities. * concurrent: if true, do not lock the table against writers. The index * will be marked "invalid" and the caller must take additional steps * to fix it up. @@ -535,6 +536,7 @@ index_create(Oid heapRelationId, bool initdeferred, bool allow_system_table_mods, bool skip_build, + bool index_exists, bool concurrent) { Relation pg_class; @@ -615,7 +617,8 @@ index_create(Oid heapRelationId, if (shared_relation && tableSpaceId != GLOBALTABLESPACE_OID) elog(ERROR, "shared relations must be placed in pg_global tablespace"); - if (get_relname_relid(indexRelationName, namespaceId)) + /* We don't check existence if index already exists */ + if (!index_exists && get_relname_relid(indexRelationName, namespaceId)) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", @@ -653,6 +656,17 @@ index_create(Oid heapRelationId, } } + if (index_exists) + { + Assert( skip_build && !IsBootstrapProcessingMode() ); + + indexRelation = relation_open(indexRelationId, AccessExclusiveLock); + + /* done with pg_class */ + heap_close(pg_class, RowExclusiveLock); + } + else + { /* * create the index relation's relcache entry and physical disk file. (If * we fail further down, it's the smgr's responsibility to remove the disk @@ -725,6 +739,7 @@ index_create(Oid heapRelationId, classObjectId, coloptions, isprimary, !deferrable, !concurrent); + } /* * Register constraint and dependencies for the index. @@ -838,7 +853,7 @@ index_create(Oid heapRelationId, true); } } - else + else if(!index_exists) { bool have_simple_col = false; @@ -881,6 +896,8 @@ index_create(Oid heapRelationId, Assert(!initdeferred); } + if (!index_exists) + { /* Store dependency on operator classes */ for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) { @@ -911,6 +928,7 @@ index_create(Oid heapRelationId, DEPENDENCY_AUTO); } } + } else { /* Bootstrap mode - assert we weren't asked for constraint support */ diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 7bf64e2..593db28 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -271,7 +271,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio rel->rd_rel->reltablespace, classObjectId, coloptions, (Datum) 0, true, false, false, false, - true, false, false); + true, false, false, false); /* * Store the toast table's OID in the parent relation's pg_class row diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 9407d0f..65390d8 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -122,6 +122,7 @@ DefineIndex(RangeVar *heapRelation, bool is_alter_table, bool check_rights, bool skip_build, + bool index_exists, bool quiet, bool concurrent) { @@ -483,6 +484,7 @@ DefineIndex(RangeVar *heapRelation, isconstraint, deferrable, initdeferred, allowSystemTableMods, skip_build || concurrent, + index_exists, concurrent); if (!concurrent) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 403e55a..6714612 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -336,7 +336,7 @@ static void ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) static void copy_relation_data(SMgrRelation rel, SMgrRelation dst, ForkNumber forkNum, bool istemp); static const char *storage_name(char c); - +static Oid get_pkey_index_oid(IndexStmt *idx_stmt); /* ---------------------------------------------------------------- * DefineRelation @@ -4799,6 +4799,191 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName, tab->new_changeoids = true; } } +/* + * If the PRIMARY KEY clasue has WITH INDEX option set, then prepare to use + * that index as the primary key. + */ +static Oid +get_pkey_index_oid(IndexStmt *idx_stmt) +{ + Oid index_oid = InvalidOid; /* Oid of the index to create/replace primary key with */ + ListCell *l; + ListCell *prev = NULL; + ListCell *option = NULL; + Relation rel; + + rel = relation_openrv(idx_stmt->relation, AccessExclusiveLock); + + foreach(l, idx_stmt->options) + { + DefElem *def = (DefElem*)lfirst(l); + int i; + char *index_name; + ListCell *cell; + Relation index_rel; + Form_pg_index index_form; + + if (def->defnamespace != NULL || strcmp(def->defname, "index") != 0) + { + prev = l; + continue; + } + + option = l; + + /* + * If we don't do this, WITH INDEX option will reach DefineIndex(), and + * it will throw a fit. + */ + if (OidIsValid(index_oid)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("only one WITH INDEX option can be specified for a primary key"))); + + if (!IsA(def->arg, String)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("syntax error"), + errdetail("WITH INDEX option for primary key should be a string value."))); + + index_name = strVal(def->arg); + + /* Look for the index in the same schema as the table */ + index_oid = get_relname_relid(index_name, RelationGetNamespace(rel)); + + if (!OidIsValid(index_oid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("relation \"%s\" not found", index_name))); + + /* This will throw an error if it is not an index */ + index_rel = index_open(index_oid, AccessExclusiveLock); + + /* Check that it does not have an associated constraint */ + if (OidIsValid(get_index_constraint(index_oid))) + ereport(ERROR, + (errmsg("index \"%s\" is associated with a constraint", + index_name))); + + /* Perform validity checks on the index */ + index_form = index_rel->rd_index; + + if (!index_form->indisvalid) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("index \"%s\" is not valid", index_name))); + + if (!index_form->indisready) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("index \"%s\" is not ready", index_name))); + + if (index_form->indrelid != RelationGetRelid(rel)) + elog(ERROR, "index \"%s\" belongs some other table", index_name); + + if (!index_form->indisunique) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a unique index", index_name), + errdetail("cannot create primary key using a non-unique index."))); + + if (index_rel->rd_indextuple != NULL && + !heap_attisnull(index_rel->rd_indextuple, Anum_pg_index_indexprs)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("index \"%s\" contains expressions", index_name), + errdetail("cannot create primary key using an expression index."))); + + if (index_rel->rd_indextuple != NULL && + !heap_attisnull(index_rel->rd_indextuple, Anum_pg_index_indpred)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a partial index", index_name), + errdetail("cannot create primary key using a partial index."))); + + /* Match the PRIMARY KEY clasue from the ALTER statement with the index */ + if (index_form->indnatts != list_length(idx_stmt->indexParams)) + elog(ERROR, "primary key definition does not match the index"); + + /* XXX: Assert here? */ + if (index_form->indnatts > rel->rd_att->natts) + elog(ERROR, "index \"%s\" has more columns than the table", + index_name); + + i = 0; + foreach(cell, idx_stmt->indexParams) + { + IndexElem *elem = (IndexElem*)lfirst(cell); + int16 attnum = index_form->indkey.values[i]; + char *attname; + + if (elem->name == NULL) + { + Assert(elem->expr != NULL); + + /* Grammar already prevents this by disallowing expressions. */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("PRIMARY KEY clause contains expressions"), + errdetail("cannot create primary key using an expression index."))); + } + + /* + * We need not worry about attisdropped, since this index's + * existence guarantees that the column exists. + */ + attname = NameStr(rel->rd_att->attrs[attnum-1]->attname); + + if (strcmp(elem->name, attname) != 0) + elog(ERROR, "index columns do not match primary key definition"); + + ++i; + } + + /* Currently only B-tree indexes are suupported for primary keys */ + if (index_rel->rd_rel->relam != BTREE_AM_OID) + elog(ERROR, "\"%s\" is not a B-Tree index", index_name); + + relation_close(index_rel, NoLock); + + /* + * Do not break out of the loop. Use this opprtunity to catch + * multiple 'WITH INDEX' clauses. + */ + } + + if (OidIsValid(index_oid)) + { + /* Remove the WITH INDEX clause. DefineIndex() does not understand it.*/ + idx_stmt->options = list_delete_cell(idx_stmt->options, option, prev); + + /* + * Assign a constraint name, if the clause doesn't have one. + * If we don't do it here, that implies DefineIndex() will choose the + * name, and after that it is too late to rename the index to match + * constraint name since doing that will complain 'constraint already + * exists'. + */ + if (idx_stmt->idxname == NULL) + { + idx_stmt->idxname = ChooseIndexName(RelationGetRelationName(rel), + RelationGetNamespace(rel), + NULL, + /* Don't need this, but it won't hurt */ + idx_stmt->excludeOpNames, + true, + true); + } + + /* Rename index to maintain consistency with the rest of the code */ + RenameRelation(index_oid, idx_stmt->idxname, OBJECT_INDEX); + + relation_close(rel, NoLock); + + } + + return index_oid; +} /* * ALTER TABLE ADD INDEX @@ -4814,6 +4999,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel, bool check_rights; bool skip_build; bool quiet; + Oid index_oid = InvalidOid; Assert(IsA(stmt, IndexStmt)); @@ -4824,11 +5010,26 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel, /* suppress notices when rebuilding existing index */ quiet = is_rebuild; + if (stmt->primary && stmt->options) + { + index_oid = get_pkey_index_oid(stmt); + + /* If we have the WITH INDEX option set, use that for the primary key */ + if (OidIsValid(index_oid)) + { + /* We override the params set above */ + skip_build = true; + + /* We don't want the 'will create implicit index' message */ + quiet = true; + } + } + /* The IndexStmt has already been through transformIndexStmt */ DefineIndex(stmt->relation, /* relation */ stmt->idxname, /* index name */ - InvalidOid, /* no predefined OID */ + index_oid, /* predefined OID, if any */ stmt->accessMethod, /* am name */ stmt->tableSpace, stmt->indexParams, /* parameters */ @@ -4843,8 +5044,38 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel, true, /* is_alter_table */ check_rights, skip_build, + OidIsValid(index_oid), quiet, false); + + /* + * Mark the index as indisprimary. We can't do this before DefineIndex() + * because it complains about duplicate primary key. + */ + if (stmt->primary && OidIsValid(index_oid)) + { + Relation pg_index; + HeapTuple indexTuple; + Form_pg_index indexForm; + + pg_index = heap_open(IndexRelationId, RowExclusiveLock); + + indexTuple = SearchSysCacheCopy1(INDEXRELID, + ObjectIdGetDatum(index_oid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for index %u", index_oid); + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + + indexForm->indisprimary = true; + simple_heap_update(pg_index, &indexTuple->t_self, indexTuple); + CatalogUpdateIndexes(pg_index, indexTuple); + + heap_freetuple(indexTuple); + heap_close(pg_index, RowExclusiveLock); + + /* Make these changes visible to later commands */ + CommandCounterIncrement(); + } } /* diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 75cb354..59c52bd 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -903,6 +903,7 @@ standard_ProcessUtility(Node *parsetree, false, /* is_alter_table */ true, /* check_rights */ false, /* skip_build */ + false, /* index_exists */ false, /* quiet */ stmt->concurrent); /* concurrent */ } diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 66a6002..80ddce6 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -44,6 +44,7 @@ extern Oid index_create(Oid heapRelationId, bool initdeferred, bool allow_system_table_mods, bool skip_build, + bool index_exists, bool concurrent); extern void index_drop(Oid indexId); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 1dc1a7d..189e133 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -35,6 +35,7 @@ extern void DefineIndex(RangeVar *heapRelation, bool is_alter_table, bool check_rights, bool skip_build, + bool index_exists, bool quiet, bool concurrent); extern void ReindexIndex(RangeVar *indexRelation); diff --git a/src/test/regress/expected/pkey_with_index.out b/src/test/regress/expected/pkey_with_index.out new file mode 100644 index 0000000..38141f0 --- /dev/null +++ b/src/test/regress/expected/pkey_with_index.out @@ -0,0 +1,59 @@ +CREATE TABLE rpi_test( a int , b varchar(10), c char); +-- add some data so that all tests have something to work with. +INSERT INTO rpi_test VALUES( 1, 2 ); +INSERT INTO rpi_test VALUES( 3, 4 ); +INSERT INTO rpi_test VALUES( 5, 6 ); +CREATE UNIQUE INDEX rpi_uniq_idx ON rpi_test(a , b); +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_uniq_idx'); +CREATE UNIQUE INDEX rpi_uniq2_idx ON rpi_test(b , a); +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey, ADD primary key(b, a) WITH (INDEX = 'rpi_uniq2_idx'); +DROP INDEX rpi_uniq_idx; +ERROR: index "rpi_uniq_idx" does not exist +DROP INDEX rpi_uniq2_idx; +ERROR: index "rpi_uniq2_idx" does not exist +-- Negative test cases +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = 3 ); +ERROR: syntax error +DETAIL: WITH INDEX option for primary key should be a string value. +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = del ); +ERROR: syntax error +DETAIL: WITH INDEX option for primary key should be a string value. +ALTER TABLE rpi_test ADD PRIMARY KEY(b, a) WITH (INDEX = 'rpi_idx_doesnt_exist'); +ERROR: relation "rpi_idx_doesnt_exist" not found +ALTER TABLE rpi_test ADD UNIQUE (a); +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "rpi_test_a_key" for table "rpi_test" +ALTER TABLE rpi_test ADD PRIMARY KEY(a) WITH (INDEX = 'rpi_test_a_key'); +ERROR: index "rpi_test_a_key" is associated with a constraint +CREATE INDEX rpi_idx1 ON rpi_test(a , b); +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_idx1'); -- should fail; non-unique +ERROR: "rpi_idx1" is not a unique index +DETAIL: cannot create primary key using a non-unique index. +CREATE UNIQUE INDEX rpi_idx2 ON rpi_test(a , b); +-- should fail; WITH INDEX option specified more than once. +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = 'rpi_idx2', INDEX = 'rpi_idx2' ); +ERROR: only one WITH INDEX option can be specified for a primary key +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_idx2'); +ERROR: multiple primary keys for table "rpi_test" are not allowed +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey; +ALTER TABLE rpi_test ADD primary key(a, b); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "rpi_test_pkey" for table "rpi_test" +CREATE UNIQUE INDEX rpi_idx3 ON rpi_test(a); +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey, ADD PRIMARY KEY(a, b) WITH (INDEX = 'rpi_idx3'); -- should fail +ERROR: primary key definition does not match the index +DROP INDEX rpi_idx3; +SELECT relname, relkind FROM pg_class WHERE relname like E'rpi\\_%' ORDER BY relkind DESC, relname ASC; + relname | relkind +----------------+--------- + rpi_test | r + rpi_idx1 | i + rpi_idx2 | i + rpi_test_a_key | i + rpi_test_pkey | i +(5 rows) + +DROP TABLE rpi_test; +SELECT relname, relkind FROM pg_class WHERE relname like E'rpi\\_%'; + relname | relkind +---------+--------- +(0 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 3b99e86..83a1987 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -91,7 +91,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # NB: temp.sql does a reconnect which transiently uses 2 connections, # so keep this parallel group to at most 19 tests # ---------- -test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml pkey_with_index # run stats by itself because its delay may be insufficient under heavy load test: stats diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index b348f0e..74ed275 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -125,3 +125,4 @@ test: largeobject test: with test: xml test: stats +test: pkey_with_index diff --git a/src/test/regress/sql/pkey_with_index.sql b/src/test/regress/sql/pkey_with_index.sql new file mode 100644 index 0000000..ec21b21 --- /dev/null +++ b/src/test/regress/sql/pkey_with_index.sql @@ -0,0 +1,54 @@ + +CREATE TABLE rpi_test( a int , b varchar(10), c char); + +-- add some data so that all tests have something to work with. + +INSERT INTO rpi_test VALUES( 1, 2 ); +INSERT INTO rpi_test VALUES( 3, 4 ); +INSERT INTO rpi_test VALUES( 5, 6 ); + +CREATE UNIQUE INDEX rpi_uniq_idx ON rpi_test(a , b); +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_uniq_idx'); + +CREATE UNIQUE INDEX rpi_uniq2_idx ON rpi_test(b , a); +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey, ADD primary key(b, a) WITH (INDEX = 'rpi_uniq2_idx'); + +DROP INDEX rpi_uniq_idx; +DROP INDEX rpi_uniq2_idx; + + + +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = 3 ); +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = del ); +ALTER TABLE rpi_test ADD PRIMARY KEY(b, a) WITH (INDEX = 'rpi_idx_doesnt_exist'); + +ALTER TABLE rpi_test ADD UNIQUE (a); +ALTER TABLE rpi_test ADD PRIMARY KEY(a) WITH (INDEX = 'rpi_test_a_key'); + +CREATE INDEX rpi_idx1 ON rpi_test(a , b); + +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_idx1'); -- should fail; non-unique + +CREATE UNIQUE INDEX rpi_idx2 ON rpi_test(a , b); + +-- should fail; WITH INDEX option specified more than once. +ALTER TABLE rpi_test ADD PRIMARY KEY (a, b) WITH ( INDEX = 'rpi_idx2', INDEX = 'rpi_idx2' ); + +ALTER TABLE rpi_test ADD primary key(a, b) WITH (INDEX = 'rpi_idx2'); + +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey; + +ALTER TABLE rpi_test ADD primary key(a, b); + +CREATE UNIQUE INDEX rpi_idx3 ON rpi_test(a); + +ALTER TABLE rpi_test DROP CONSTRAINT rpi_test_pkey, ADD PRIMARY KEY(a, b) WITH (INDEX = 'rpi_idx3'); -- should fail + +DROP INDEX rpi_idx3; + +SELECT relname, relkind FROM pg_class WHERE relname like E'rpi\\_%' ORDER BY relkind DESC, relname ASC; + +DROP TABLE rpi_test; + +SELECT relname, relkind FROM pg_class WHERE relname like E'rpi\\_%'; +