Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipO9p-0004Mn-Kc for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Jan 2020 03:06:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ipO9n-0006Ou-Kz for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Jan 2020 03:06:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipO9l-0006Om-OX for pgsql-bugs@lists.postgresql.org; Thu, 09 Jan 2020 03:06:47 +0000 Received: from [64.147.123.24] (helo=wout1-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ipO9d-0001dF-AY for pgsql-bugs@lists.postgresql.org; Thu, 09 Jan 2020 03:06:44 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.west.internal (Postfix) with ESMTP id 9164D9B8; Wed, 8 Jan 2020 22:06:25 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Wed, 08 Jan 2020 22:06:25 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=Wjt8vtcFCTE+lH78ocIm+2uFyBz 6LJf0SgZtaklb0yQ=; b=c7/CMtcXxsDyEFjJ15XCRcXhbAx7dUkz/TfnjVPwHTS by+Q0qwSW/LeJOxAtkxDBXTp8OnWS7o9QOYPpJFe57DUkQ3JaIEme8W4vib6XG5D gt/KoW+aOa+Mpb5Z1EiH3UjpN2oMbU2yq0x8Azc2Md3QVzcIMw/Z/Fga67AV1Uf0 BrWOaRa7r5DPq7Teu/gXsYLq+D4grPHxXXXaXDmwxGP4A0Gs0VfgB5MPVD0P6l2s oLspCIOOg5PIm4TmknMUfr/9x5GzQIfj5z4Enxb3CMfXcqVdxQ/7/5yvGWo8mEdk DE41KsvjXCZIdNtj8wEMpJcLKi8saCEqehvA0MUa2bA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=Wjt8vt cFCTE+lH78ocIm+2uFyBz6LJf0SgZtaklb0yQ=; b=MXR/z1H77iDdP3NbmedXhk SGvL7I5QxUoIYsQ5LhfFI8RANAVE6Dc/Q4x4EAqAGa4ZQBaCrtYKpeGfJWJBaBIm pod12tPBR+DoyTUMbkntnzdszpGTY7lsRrh5CvvSjjrQBD8tsuvpPA4NkMeJR2Bu C8wSIxQUjfw4D2rY+nppH/vzr2s1m0jSZF01LrEbNjVePXoC1Fb0YRnMu4U5zZR1 B1nkEB8Sh3u+EPEfk9mzhRt/lRCGDFJUVUDu3AxBfUDngfnyEbqj/+n8anychR6C N1sGsWd3eAsbePTgijYi/OUCLbDlbngIN4ac3Rp3Q5c28d8SSvKlYM9k2eW1JHtw == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrvdehledgheefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucfkphepudduuddruddtvddrudektddrudekheenucfrrghr rghmpehmrghilhhfrhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiihiienucevlh hushhtvghrufhiiigvpedt X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id C522030600A8; Wed, 8 Jan 2020 22:06:22 -0500 (EST) Date: Thu, 9 Jan 2020 12:06:19 +0900 From: Michael Paquier To: Heikki Linnakangas Cc: Andres Freund , Tom Lane , Manuel Rigger , PostgreSQL mailing lists Subject: Re: REINDEX CONCURRENTLY unexpectedly fails Message-ID: <20200109030619.GC2251@paquier.xyz> References: <20191115025412.p3beiflrd6bx6wgf@alap3.anarazel.de> <20191115032141.GH1849@paquier.xyz> <20191115080706.GI1849@paquier.xyz> <20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de> <20191120035408.GA4243@paquier.xyz> <20191212213709.neopqccvdo724eha@alap3.anarazel.de> <20191213034536.GD1942@paquier.xyz> <20191223015956.GB34339@paquier.xyz> <8f0bde7b-2d96-fb5c-cd0e-553694c1d376@iki.fi> <20200108081930.GF3413@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="/unnNtmY43mpUSKx" Content-Disposition: inline In-Reply-To: <20200108081930.GF3413@paquier.xyz> X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.147.123.24 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --/unnNtmY43mpUSKx Content-Type: multipart/mixed; boundary="ALfTUftag+2gvp1h" Content-Disposition: inline --ALfTUftag+2gvp1h Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Wed, Jan 08, 2020 at 05:19:30PM +0900, Michael Paquier wrote: > I have spent a couple of hours poking at this code, and found two > problems: > 1) The error reporting for PROGRESS_CREATEIDX_COMMAND would report a > concurrent build, but that's not the case if the work happens for a > temporary table in DefineIndex(), so the call to > RelationSupportsConcurrentIndexing needs to happen before any look at > the concurrent flag is done. That's easy enough to fix. > 2) The handling of the patch within index_drop is too weak. As > presented, the patch first locks the OID using a RangeVar. However > for a temporary relation we would first take ShareUpdateExclusiveLock > RemoveRelations() and then upgrade to a AccessExclusiveLock in=20 > index_drop(). I think that actually the check in index_drop() is not > necessary, and that instead we had better do three things: > a) In RangeVarCallbackForDropRelation(), if the relation is temporary, > use AccessExclusiveLock all the time, and we know the OID of the > relation here. > b) After locking the OID with the RangeVar, re-check if the relation > is temporary, and then remove PERFORM_DELETION_CONCURRENTLY is. > c) Add an assertion in index_drop() to be sure that this code path is > never invoked concurrently with a temporary relation. >=20 > I am lacking of time today, I'll continue tomorrow. Okay, so here is an updated patch fixing those issues, with several modifications done to the patch (docs, updates for the assertions, some redesign). Considering again those aspects, I have come up with the same conclusion as what's stated above, though you actually need to make sure that it is RangeVarGetRelidExtended() that has to be careful about the lock to use on the temporary relation, before anything else is done. The callback RangeVarCallbackForDropRelation() also needs to be careful about the relation it looks at and check if the relation supports concurrent indexing. On the other hand, we could also say that we don't care about lock upgrade risks when working on temporary tables because these are not accessed by other sessions, though that's not a sane base to rely on IMO. A solution involving RangeVarGetRelidExtended() feels also like a sledgehammer to smash a peanut, because it has a wider impact. If lock upgrade risks are not worth bothering, this needs to be clearly documented in the patch with more comments. As the patch has been heavily modified, I am switching it back to "Needs Review" for now and I'd like to discuss more about the lock upgrade risks, particularly if it is considered worth the effort for temporary relations. Thoughts are welcome.=20 -- Michael --ALfTUftag+2gvp1h Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="reindex-conc-temp-v4.patch" Content-Transfer-Encoding: quoted-printable diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index a2890c1314..51723a8d8d 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -115,6 +115,8 @@ extern bool CompareIndexInfo(IndexInfo *info1, IndexInf= o *info2, =20 extern void BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii); =20 +extern bool RelationSupportsConcurrentIndexing(Oid relid); + extern void FormIndexDatum(IndexInfo *indexInfo, TupleTableSlot *slot, EState *estate, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 3e59e647e5..2cd44a1259 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2016,6 +2016,13 @@ index_drop(Oid indexId, bool concurrent, bool concur= rent_lock_mode) LOCKTAG heaplocktag; LOCKMODE lockmode; =20 + /* + * A relation not supporting concurrent indexing should never do + * a concurrent index drop or try to use a concurrent lock mode. + */ + Assert(RelationSupportsConcurrentIndexing(indexId) || + (!concurrent && !concurrent_lock_mode)); + /* * To drop an index safely, we must grab exclusive lock on its parent * table. Exclusive lock on the index alone is insufficient because @@ -2108,6 +2115,9 @@ index_drop(Oid indexId, bool concurrent, bool concurr= ent_lock_mode) */ CacheInvalidateRelcache(userHeapRelation); =20 + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(indexId)); + /* save lockrelid and locktag for below, then close but keep locks */ heaprelid =3D userHeapRelation->rd_lockInfo.lockRelId; SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId); @@ -2490,6 +2500,30 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2, return true; } =20 +/* + * RelationSupportsConcurrentIndexing + * + * Check if a relation supports concurrent builds or not. This is + * used prior processing CREATE INDEX, DROP INDEX or REINDEX when using + * CONCURRENTLY to decide if the operation is supported. + */ +bool +RelationSupportsConcurrentIndexing(Oid relid) +{ + /* + * Build indexes non-concurrently for temporary relations. Such + * relations only work with the session assigned to them, so they are + * not subject to concurrent concerns, and a concurrent build would + * cause issues with ON COMMIT actions triggered by the transactions + * of the concurrent build. A non-concurrent reindex is also more + * efficient in this case. + */ + if (get_rel_persistence(relid) =3D=3D RELPERSISTENCE_TEMP) + return false; + + return true; +} + /* ---------------- * BuildSpeculativeIndexInfo * Add extra state to IndexInfo record diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespac= e.c index c82f9fc4b5..1ffc84e349 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -286,6 +286,19 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOC= KMODE lockmode, */ if (relation->relpersistence =3D=3D RELPERSISTENCE_TEMP) { + /* + * When working on a temporary relation, just take an exclusive + * lock. The relation is bound to the session looking after it, + * so other sessions would not look at it, and this comes handy + * in order to avoid lock upgrade problems when trying to do a + * concurrent indexing operation on such a relation. If the lock + * wanted is NoLock, let's assume that the caller knows what it + * does. + */ + if (relation->relpersistence =3D=3D RELPERSISTENCE_TEMP && + lockmode !=3D NoLock) + lockmode =3D AccessExclusiveLock; + if (!OidIsValid(myTempNamespace)) relId =3D InvalidOid; /* this probably can't happen? */ else diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexc= mds.c index 52ce02f898..d63a885638 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -485,6 +485,13 @@ DefineIndex(Oid relationId, GUC_ACTION_SAVE, true, 0, false); } =20 + /* + * Enforce non-concurrent build if the relation does not support this + * option. Do this before any use of the concurrent option is done. + */ + if (!RelationSupportsConcurrentIndexing(relationId)) + stmt->concurrent =3D false; + /* * Start progress report. If we're building a partition, this was already * done. @@ -2347,7 +2354,7 @@ ReindexIndex(RangeVar *indexRelation, int options, bo= ol concurrent) persistence =3D irel->rd_rel->relpersistence; index_close(irel, NoLock); =20 - if (concurrent) + if (concurrent && RelationSupportsConcurrentIndexing(indOid)) ReindexRelationConcurrently(indOid, options); else reindex_index(indOid, false, persistence, @@ -2440,7 +2447,8 @@ ReindexTable(RangeVar *relation, int options, bool co= ncurrent) 0, RangeVarCallbackOwnsTable, NULL); =20 - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(heapOid)) { result =3D ReindexRelationConcurrently(heapOid, options); =20 @@ -2646,7 +2654,8 @@ ReindexMultipleTables(const char *objectName, Reindex= ObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); =20 - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(relid)) { (void) ReindexRelationConcurrently(relid, options); /* ReindexRelationConcurrently() does the verbose output */ @@ -2769,6 +2778,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Open relation to get its indexes */ heapRelation =3D table_open(relationOid, ShareUpdateExclusiveLock); =20 + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(relationOid)); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -2862,6 +2874,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Save the list of relation OIDs in private context */ oldcontext =3D MemoryContextSwitchTo(private_context); =20 + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(heapId)); + /* Track the heap relation of this index for session locks */ heapRelationIds =3D list_make1_oid(heapId); =20 @@ -2937,6 +2952,13 @@ ReindexRelationConcurrently(Oid relationOid, int opt= ions) heapRel =3D table_open(indexRel->rd_index->indrelid, ShareUpdateExclusiveLock); =20 + /* + * Also check for active uses of the relation in the current + * transaction, including open scans and pending AFTER trigger + * events. + */ + CheckTableNotInUse(indexRel, "REINDEX"); + pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX, RelationGetRelid(heapRel)); pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index 421bc28727..3d51bff5e2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1237,7 +1237,11 @@ RemoveRelations(DropStmt *drop) /* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */ if (drop->concurrent) { - flags |=3D PERFORM_DELETION_CONCURRENTLY; + /* + * Note that for temporary relations this lock may get upgraded + * when looking at the relation OID using its RangeVar and in + * RangeVarCallbackForDropRelation(). + */ lockmode =3D ShareUpdateExclusiveLock; Assert(drop->removeType =3D=3D OBJECT_INDEX); if (list_length(drop->objects) !=3D 1) @@ -1328,6 +1332,19 @@ RemoveRelations(DropStmt *drop) continue; } =20 + /* + * This is done after looking at the relation OID as there is no + * way of knowing if the relation supports a concurrent drop + * or not beforehand. + */ + if (drop->concurrent && + RelationSupportsConcurrentIndexing(relOid)) + { + Assert(list_length(drop->objects) =3D=3D 1 && + drop->removeType =3D=3D OBJECT_INDEX); + flags |=3D PERFORM_DELETION_CONCURRENTLY; + } + /* OK, we're ready to delete this one */ obj.classId =3D RelationRelationId; obj.objectId =3D relOid; @@ -1365,6 +1382,14 @@ RangeVarCallbackForDropRelation(const RangeVar *rel,= Oid relOid, Oid oldRelOid, heap_lockmode =3D state->concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock; =20 + /* + * Relations not supporting concurrent indexing cannot use a weaker + * lock. + */ + if (OidIsValid(relOid) && + !RelationSupportsConcurrentIndexing(relOid)) + heap_lockmode =3D AccessExclusiveLock; + /* * If we previously locked some other index's heap, and the name we're * looking up no longer refers to that relation, release the now-useless diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/= expected/create_index.out index 6446907a65..cf1a0ca2f2 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1435,6 +1435,31 @@ Indexes: "concur_index5" btree (f2) WHERE f1 =3D 'x'::text "std_index" btree (f2) =20 +-- 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 -- @@ -2418,6 +2443,55 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::= regclass); (1 row) =20 DROP TABLE concur_exprs_tab; +-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored. +-- ON COMMIT PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_1; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +-- Still fails in transaction blocks +BEGIN; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text) + ON COMMIT DELETE ROWS; +CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_2; +REINDEX INDEX CONCURRENTLY concur_temp_ind_2; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2); +-- Fails when running in a transaction +REINDEX INDEX CONCURRENTLY concur_temp_ind_3; +ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- REINDEX SCHEMA processes all temporary relations +CREATE TABLE reindex_temp_before AS +SELECT oid, relname, relfilenode, relkind, reltoastrelid + FROM pg_class + WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2'); +SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset +REINDEX SCHEMA :temp_schema_name; +SELECT b.relname, + b.relkind, + CASE WHEN a.relfilenode =3D b.relfilenode THEN 'relfilenode is unc= hanged' + ELSE 'relfilenode has changed' END + FROM reindex_temp_before b JOIN pg_class a ON b.oid =3D a.oid + ORDER BY 1; + relname | relkind | case =20 +-------------------+---------+------------------------- + concur_temp_ind_1 | i | relfilenode has changed + concur_temp_ind_2 | i | relfilenode has changed +(2 rows) + +DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; -- -- REINDEX SCHEMA -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/c= reate_index.sql index 3c0c1cdc5e..5bc9da440e 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -501,6 +501,31 @@ VACUUM FULL concur_heap; REINDEX TABLE concur_heap; \d concur_heap =20 +-- 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 -- @@ -972,6 +997,48 @@ SELECT pg_get_indexdef('concur_exprs_index_pred'::regc= lass); SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); DROP TABLE concur_exprs_tab; =20 +-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored. +-- ON COMMIT PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_1; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +-- Still fails in transaction blocks +BEGIN; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text) + ON COMMIT DELETE ROWS; +CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_2; +REINDEX INDEX CONCURRENTLY concur_temp_ind_2; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2); +-- Fails when running in a transaction +REINDEX INDEX CONCURRENTLY concur_temp_ind_3; +COMMIT; +-- REINDEX SCHEMA processes all temporary relations +CREATE TABLE reindex_temp_before AS +SELECT oid, relname, relfilenode, relkind, reltoastrelid + FROM pg_class + WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2'); +SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset +REINDEX SCHEMA :temp_schema_name; +SELECT b.relname, + b.relkind, + CASE WHEN a.relfilenode =3D b.relfilenode THEN 'relfilenode is unc= hanged' + ELSE 'relfilenode has changed' END + FROM reindex_temp_before b JOIN pg_class a ON b.oid =3D a.oid + ORDER BY 1; +DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; + -- -- REINDEX SCHEMA -- diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_i= ndex.sgml index 629a31ef79..ab362a0dc5 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -129,6 +129,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EX= ISTS ] . + + For temporary tables, CREATE INDEX is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + =20 diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index= =2Esgml index 2a8ca5bf68..0aedd71bd6 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. + =20 diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 5aa59d3b75..0cc19b86ee 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -166,6 +166,11 @@ REINDEX [ ( option [, ...] ) ] { IN — see . + + For temporary tables, REINDEX is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + =20 --ALfTUftag+2gvp1h-- --/unnNtmY43mpUSKx Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl4WmKsACgkQnvQgOdby QH364Q/5Aa3l367uBvTYufsqlFg9xLYXrj/myICBvN0LvUwNFcETJo3dG21wICKz ccvY42t/b92LEhb7euOT4DXCWVBrz4HkM9wfIf7SOTpBBE2DPrNgys6OvjBmJtTU +fhroC/ygtCvH8IdQ+SgNYXc86D6Tjoh6OsOKIhuo5KWdL1Zl1qxGsYMwVhB1ThJ CaE4AellFPJgRBz8aSbo8vNsY0Z9a1/uEOrpX4Kok5pFdF/xOUKkfn9Cm9hPyj7H c57cacF6c4QHorzMPqTrycR2WPqK3xY2wLLoU/0oNpeQ6GemcBCYvQG9i0sXFXfG FEcIXt/9scKAdkuzC9xHUT57SL+JJDCRk9auNoSHw7WdK2kQ1SqVivNWFdFNWHmU UVDuA72FMW2SV9iEgdTqDjaD0SVj4QHj15E+d9ghiLHI/BXHXTueUG3js3YTBxhl XU8lB1xQI/c/eJc+7pwudN0GMtipj5Bf/alT/233vIrOuI/xZ/CDEBNQjIseyxZ3 ZPY53ZOwgsRHAv8wipqqKhw/pqhQYbCbUSAok3QSLnV6ai0LmPgvcGz7Rc679vBI GxNGg8WUoChZM1RAAo6jXbxOQjLmDKqz79PeSbLeTIlmur/DFHonBnP7TGFK21AT yRVHxr6J1ejOR1urZlYv3Mqu2hUUtmrgoRepc4pk1IFLxf18CvE= =c7Nf -----END PGP SIGNATURE----- --/unnNtmY43mpUSKx--