From c39f455981770aa707f05d8e338b233e96730e47 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Wed, 22 Jan 2020 09:49:44 +0900 Subject: [PATCH] Fix concurrent indexing operations with temporary tables MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Attempting to use CREATE INDEX, DROP INDEX or REINDEX with CONCURRENTLY on a temporary relation with ON COMMIT actions triggered unexpected errors because those operations use multiple transactions internally to complete their work. Here is for example one confusing error when using ON COMMIT DELETE ROWS: ERROR: index "foo" already contains data Issues related to temporary relations and concurrent indexing are fixed in this commit by enforcing the non-concurrent path to be taken for temporary relations even if using CONCURRENTLY, transparently to the user. Using a non-concurrent path does not matter in practice as locks cannot be taken on a temporary relation by a session different than the one owning the relation, and the non-concurrent operation is more effective. The problem exists with REINDEX since v12 with the introduction of CONCURRENTLY, and with CREATE/DROP INDEX since CONCURRENTLY exists for those commands. In all supported versions, this caused only confusing error messages to be generated. Note that with REINDEX, it was also possible to issue a REINDEX CONCURRENTLY for a temporary relation owned by a different session, leading to a server crash. The idea to enforce transparently the non-concurrent code path for temporary relations comes originally from Andres Freund. Reported-by: Manuel Rigger Author: Michael Paquier, Heikki Linnakangas Reviewed-by: Andres Freund, Álvaro Herrera, Heikki Linnakangas Discussion: https://postgr.es/m/CA+u7OA6gP7YAeCguyseusYcc=uR8+ypjCcgDDCTzjQ+k6S9ksQ@mail.gmail.com Backpatch-through: 9.4 --- doc/src/sgml/ref/create_index.sgml | 5 ++++ doc/src/sgml/ref/drop_index.sgml | 5 ++++ src/backend/catalog/index.c | 9 ++++++++ src/backend/commands/indexcmds.c | 27 ++++++++++++++++------ src/backend/commands/tablecmds.c | 18 ++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 ++++++++++++++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/create_index.out | 25 ++++++++++++++++++++ src/test/regress/sql/create_index.sql | 25 ++++++++++++++++++++ 9 files changed, 129 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 32b424e8f65..ef0dff6c818 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -123,6 +123,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] . + + For temporary tables, CREATE INDEX is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index d66d30edf74..96e5f049f28 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -58,6 +58,11 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] nameDROP INDEX CONCURRENTLY cannot. + + For temporary tables, DROP INDEX is always + non-concurrent, as no other session can access them, and + non-concurrent index drop is cheaper. + diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index e707934f507..f1a944ffd55 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1362,6 +1362,15 @@ index_drop(Oid indexId, bool concurrent) LOCKTAG heaplocktag; LOCKMODE lockmode; + /* + * A temporary relation uses a non-concurrent DROP. Other backends can't + * access a temporary relation, so there's no harm in grabbing a stronger + * lock (see comments in RemoveRelations), and a non-concurrent DROP is + * more efficient. + */ + Assert(get_rel_persistence(indexId) != RELPERSISTENCE_TEMP || + !concurrent); + /* * To drop an index safely, we must grab exclusive lock on its parent * table. Exclusive lock on the index alone is insufficient because diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index f4fd3c128e4..7313310f110 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -303,6 +303,7 @@ DefineIndex(Oid relationId, bool skip_build, bool quiet) { + bool concurrent; char *indexRelationName; char *accessMethodName; Oid *typeObjectId; @@ -332,6 +333,18 @@ DefineIndex(Oid relationId, Snapshot snapshot; int i; + /* + * Force non-concurrent build on temporary relations, even if CONCURRENTLY + * was requested. Other backends can't access a temporary relation, so + * there's no harm in grabbing a stronger lock, and a non-concurrent DROP + * is more efficient. Do this before any use of the concurrent option is + * done. + */ + if (stmt->concurrent && get_rel_persistence(relationId) != RELPERSISTENCE_TEMP) + concurrent = true; + else + concurrent = false; + /* * count attributes in index */ @@ -357,7 +370,7 @@ DefineIndex(Oid relationId, * relation. To avoid lock upgrade hazards, that lock should be at least * as strong as the one we take here. */ - lockmode = stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock; + lockmode = concurrent ? ShareUpdateExclusiveLock : ShareLock; rel = heap_open(relationId, lockmode); relationId = RelationGetRelid(rel); @@ -547,8 +560,8 @@ DefineIndex(Oid relationId, indexInfo->ii_ExclusionStrats = NULL; indexInfo->ii_Unique = stmt->unique; /* In a concurrent build, mark it not-ready-for-inserts */ - indexInfo->ii_ReadyForInserts = !stmt->concurrent; - indexInfo->ii_Concurrent = stmt->concurrent; + indexInfo->ii_ReadyForInserts = !concurrent; + indexInfo->ii_Concurrent = concurrent; indexInfo->ii_BrokenHotChain = false; typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); @@ -599,7 +612,7 @@ DefineIndex(Oid relationId, * A valid stmt->oldNode implies that we already have a built form of the * index. The caller should also decline any index build. */ - Assert(!OidIsValid(stmt->oldNode) || (skip_build && !stmt->concurrent)); + Assert(!OidIsValid(stmt->oldNode) || (skip_build && !concurrent)); /* * Make the catalog entries for the index, including constraints. Then, if @@ -613,8 +626,8 @@ DefineIndex(Oid relationId, coloptions, reloptions, stmt->primary, stmt->isconstraint, stmt->deferrable, stmt->initdeferred, allowSystemTableMods, - skip_build || stmt->concurrent, - stmt->concurrent, !check_rights, + skip_build || concurrent, + concurrent, !check_rights, stmt->if_not_exists); ObjectAddressSet(address, RelationRelationId, indexRelationId); @@ -630,7 +643,7 @@ DefineIndex(Oid relationId, CreateComments(indexRelationId, RelationRelationId, 0, stmt->idxcomment); - if (!stmt->concurrent) + if (!concurrent) { /* Close the heap and we're done, in the non-concurrent case */ heap_close(rel, NoLock); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b8d2e321014..7fcc3db14cb 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -812,7 +812,11 @@ RemoveRelations(DropStmt *drop) /* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */ if (drop->concurrent) { - flags |= PERFORM_DELETION_CONCURRENTLY; + /* + * Note that for temporary relations this lock may get upgraded + * later on, but as no other session can access a temporary + * relation, this is actually fine. + */ lockmode = ShareUpdateExclusiveLock; Assert(drop->removeType == OBJECT_INDEX); if (list_length(drop->objects) != 1) @@ -903,6 +907,18 @@ RemoveRelations(DropStmt *drop) continue; } + /* + * Decide if concurrent mode needs to be used here or not. The + * relation persistence cannot be known without its OID. + */ + if (drop->concurrent && + get_rel_persistence(relOid) != RELPERSISTENCE_TEMP) + { + Assert(list_length(drop->objects) == 1 && + drop->removeType == OBJECT_INDEX); + flags |= PERFORM_DELETION_CONCURRENTLY; + } + /* OK, we're ready to delete this one */ obj.classId = RelationRelationId; obj.objectId = relOid; diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 542b1d8ee06..37886945ff0 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1768,6 +1768,28 @@ get_rel_tablespace(Oid relid) return InvalidOid; } +/* + * get_rel_persistence + * + * Returns the relpersistence associated with a given relation. + */ +char +get_rel_persistence(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + char result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relpersistence; + ReleaseSysCache(tp); + + return result; +} + /* ---------- TRANSFORM CACHE ---------- */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 97115384329..9e7c03388b6 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -102,6 +102,7 @@ extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); extern Oid get_rel_tablespace(Oid relid); +extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes); extern bool get_typisdefined(Oid typid); diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index d7930fe7f5e..b8eba4cdf5d 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2495,6 +2495,31 @@ Indexes: "concur_index5" btree (f2) WHERE f1 = 'x'::text "std_index" btree (f2) +-- Temporary tables with concurrent builds and on-commit actions +-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. +-- PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DROP; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +-- Fails when running in a transaction. +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DELETE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; -- -- Try some concurrent index drops -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index f17edc9c5c2..793b2f5ae3b 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -785,6 +785,31 @@ VACUUM FULL concur_heap; REINDEX TABLE concur_heap; \d concur_heap +-- Temporary tables with concurrent builds and on-commit actions +-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. +-- PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DROP; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +-- Fails when running in a transaction. +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DELETE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; + -- -- Try some concurrent index drops -- -- 2.39.5