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 1ifbtt-00037w-9v for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Dec 2019 03:45:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ifbtq-00053O-UR for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Dec 2019 03:45:54 +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 1ifbtq-00053G-EW for pgsql-bugs@lists.postgresql.org; Fri, 13 Dec 2019 03:45:54 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ifbtf-0008Ic-68 for pgsql-bugs@lists.postgresql.org; Fri, 13 Dec 2019 03:45:53 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 1896622708; Thu, 12 Dec 2019 22:45:42 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Thu, 12 Dec 2019 22:45:42 -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=vlSZPELMNp7U0QxWL6pe+m5zmh0 HZjvTP6a4SSMI2Sw=; b=aSxAqKs+33jOrhaaxOq45qG6I5V+gSabhFskldC8ZYY w7ok2RS6fyP/RpB6OtVTJRw8AurjzzWqTnxJvysljtfytCh4jJ+qY/sv4wwkOaW0 bQrnQYDrg1ln+vgkNlQbbmBaoFTQkseK1H7b0xOkOxKwMZ0ZkxwyDSn1ywtr1HnR CUz0oxxP3QHRXIs/h1I48CBhxbsLreE63xghN3L4PCpmpQMs1JLlHr6aLBb+1Ni7 VJ7LxMYnoa08i/23+EOqzXd6a8qXIYurUuPuIkiegIdhBQ594GDJThUtRRGk3CVV rDsjl2u+67LpFUoCc0T58AH8RHeAIDaQl2NhPOZ8kcQ== 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=vlSZPE LMNp7U0QxWL6pe+m5zmh0HZjvTP6a4SSMI2Sw=; b=O4FGU2T3cS9rzpELJblTZ8 SwXlJLENSdokL3oaXf6QRbjAqS0CjMImq4ibmOgNBYejLzNbk5ngbun+No9N3tt8 A47Fex0SZWmP1EGNj8QxigYJg8JxIGtSwGSYs5hLu8+hh3899aengEInSuOWjmHM ohevlztcYRFpRsNcaX+FxGob2vXPZ7dtOVNoTNd99sBLvRZoVYN61QlKE7vRvKZC YjHbVjIwuCZZjdIx4wasG/zOAQ7SabNF10BM/kjrA7NG0k0dIgrzq4nACFbBEMZT wT3dxCJAydi1oq75eKJ6bo1GeW8X7DHL0BSSPEvw8Zc/yh0E/MxPHaJghyL8nTrQ == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrudelkedgiedvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucfkphepuddukedrvdeguddrvdehtddrkeeknecurfgrrhgr mhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiiinecuvehluh hsthgvrhfuihiivgeptd X-ME-Proxy: Received: from paquier.xyz (mp76f1fa58.ap.nuro.jp [118.241.250.88]) by mail.messagingengine.com (Postfix) with ESMTPA id 0754A30600D4; Thu, 12 Dec 2019 22:45:39 -0500 (EST) Date: Fri, 13 Dec 2019 12:45:36 +0900 From: Michael Paquier To: Andres Freund Cc: Tom Lane , Manuel Rigger , PostgreSQL mailing lists Subject: Re: REINDEX CONCURRENTLY unexpectedly fails Message-ID: <20191213034536.GD1942@paquier.xyz> References: <19198.1573663534@sss.pgh.pa.us> <20191114080855.GG1910@paquier.xyz> <3808.1573754033@sss.pgh.pa.us> <20191115024512.GE1849@paquier.xyz> <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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="9l24NVCWtSuIVIod" Content-Disposition: inline In-Reply-To: <20191212213709.neopqccvdo724eha@alap3.anarazel.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --9l24NVCWtSuIVIod Content-Type: multipart/mixed; boundary="OaZoDhBhXzo6bW1J" Content-Disposition: inline --OaZoDhBhXzo6bW1J Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Dec 12, 2019 at 01:37:09PM -0800, Andres Freund wrote: > On 2019-11-20 12:54:08 +0900, Michael Paquier wrote: >> ON COMMIT DELETE ROWS does a physical truncation of the relation >> files. And as DROP INDEX CONCURRENTLY cannot be run in a transaction >> block, you would never face a case where you have no past TIDs which >> could be referred to when setting the index as invalid. >=20 > It's probably not reachable, but it strikes me as really fragile and > dangerous. If e.g. somehow ON COMMIT DROP tables could exist when DROP > CONCURRENTLY were run, the index_concurrently_set_dead() could very well > target a row that's since been deleted in an earlier transaction. Hmm. That joins with your point downthread about future changes.. >> Now I don't actually object to enforce the non-concurrent path in >> index_drop() for *all* temporary relations. Anyway, that makes sense >> in itself on performance grounds, similarly to the create path, so did >> that by enforcing the flag in index_drop() (doDeletion would be >> tempting but I took the problem at its root). And added some tests >> for the drop path and an extra assertion. >=20 > Cool. >=20 > I still think we'd be well served to add a few CheckTableNotInUse() type > checks... Sure. We have already one in drop_index, so DROP INDEX is covered, as well as reindex_index() which is taken by all non-concurrent REINDEX commands. Adding one in ReindexRelationConcurrently() may make sense.. >> Considered that, but ON COMMIT DROP does not make sense because it >> requires a transaction context, which is why I did not add one. And >> it seems to me that there is not much value to just check after CIC or >> REINDEX's restriction to not run in a transaction block? I added >> tests for these two, but I am of the opinion that they don't bring >> much. >=20 > I think because CIC now falls back to non-concurrent mode, it's > worthwhile to exercise this path. It seems far from unlikely that the > code gets moved around enough that suddenly CIC is allowed in > transactions when targetting temp tables. That's a good point, we have no guarantee that nobody would play with this area in the future. Well, the patch has those tests anyway since the last version, so I have not touched them. >> I think that documenting it is good for the end-user as well. >=20 > Why? Even if using a temporary table, the commands are not allowed within a transaction block, but we still track them in wait events so seeing an event related only to a non-concurrent path when using CONCURRENTLY can be confusing. >> + /* >> + * Enforce non-concurrent drop if the relation does not support this >> + * option. >> + */ >> + if (!RelationSupportsConcurrently(get_rel_persistence(indexId))) >> + concurrent =3D false; >> + >=20 > Echoing Alvaro, I'm less than convinced by this name. I would really keep "Relation" in this part of the naming as this can be used for an index or its parent table, so in the updated attached I have gone with RelationSupportsConcurrentIndexing(), which is a suggestion from Alvaro. > Copying this to some, but not all, the places where > RelationSupportsConcurrently() is called doesn't seem helpful... Not sure I follow your point here. The following code paths are currently checked in the patch using this routine: - index_drop, both used by DROP INDEX and REINDEX CONCURRENTLY. This routine is called basically via performMultipleDeletions(). For REINDEX CONCURRENTLY, this cannot be actually reached, but not for DROP INDEX CONCURRENTLY. The logic to decide which drop behavior to choose is done in RemoveRelations(). And while we don't support dropping multiple objects with CONCURRENTLY, we have no way to say now for each object which lock level should be used for the drop, so it seems safer to me now to enforce non-concurrent to be used directly in index_drop rather than doing so at a higher level. - ReindexIndex, ReindexTable and ReindexMultipleTables, to check if the non-concurrent or concurrent paths need to be called for respectively REINDEX INDEX, TABLE and SCHEMA/DATABASE/SYSTEM. - RelationSupportsConcurrentIndexing, as the entry point for CREATE INDEX. + /* + * Enforce non-concurrent build if the relation does not support this + * option. + */ Or are you suggesting to remove this comment from the two places where it is used because it does not prove to help much? > If we want to add docs, I'd say at most something like "For temporary > tables index creation is always non-concurrent, as no other session can > access them, and non-concurrent index creation is cheaper.". Sounds like a better wording to me. Documenting it still seems rather important to me as I suspect that it could surprise some users. -- Michael --OaZoDhBhXzo6bW1J Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="reindex-conc-temp-v3.patch" Content-Transfer-Encoding: quoted-printable diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 27d9e537d3..24e3c017cd 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(char relpersistence); + extern void FormIndexDatum(IndexInfo *indexInfo, TupleTableSlot *slot, EState *estate, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index e9955707fa..5809779adc 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2017,6 +2017,13 @@ index_drop(Oid indexId, bool concurrent, bool concur= rent_lock_mode) LOCKTAG heaplocktag; LOCKMODE lockmode; =20 + /* + * Enforce non-concurrent drop if the relation does not support this + * option. + */ + if (!RelationSupportsConcurrentIndexing(get_rel_persistence(indexId))) + concurrent =3D false; + /* * To drop an index safely, we must grab exclusive lock on its parent * table. Exclusive lock on the index alone is insufficient because @@ -2109,6 +2116,9 @@ index_drop(Oid indexId, bool concurrent, bool concurr= ent_lock_mode) */ CacheInvalidateRelcache(userHeapRelation); =20 + /* Temporary tables cannot be processed concurrently */ + Assert(userHeapRelation->rd_rel->relpersistence !=3D RELPERSISTENCE_TEMP= ); + /* 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); @@ -2491,6 +2501,30 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2, return true; } =20 +/* + * RelationSupportsConcurrentIndexing + * + * Check if a relation supports concurrent builds or not. This is + * used as a sanity check prior processing CREATE INDEX, DROP INDEX + * or REINDEX when using CONCURRENTLY. + */ +bool +RelationSupportsConcurrentIndexing(char relpersistence) +{ + /* + * 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 (relpersistence =3D=3D RELPERSISTENCE_TEMP) + return false; + + return true; +} + /* ---------------- * BuildSpeculativeIndexInfo * Add extra state to IndexInfo record diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexc= mds.c index 374e2d0efe..4dc8a696fe 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -550,6 +550,13 @@ DefineIndex(Oid relationId, lockmode =3D stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock; rel =3D table_open(relationId, lockmode); =20 + /* + * Enforce non-concurrent build if the relation does not support this + * option. + */ + if (!RelationSupportsConcurrentIndexing(rel->rd_rel->relpersistence)) + stmt->concurrent =3D false; + namespaceId =3D RelationGetNamespace(rel); =20 /* Ensure that it makes sense to index this kind of relation */ @@ -2349,7 +2356,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(persistence)) ReindexRelationConcurrently(indOid, options); else reindex_index(indOid, false, persistence, @@ -2442,7 +2449,8 @@ ReindexTable(RangeVar *relation, int options, bool co= ncurrent) 0, RangeVarCallbackOwnsTable, NULL); =20 - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(get_rel_persistence(heapOid))) { result =3D ReindexRelationConcurrently(heapOid, options); =20 @@ -2648,7 +2656,8 @@ ReindexMultipleTables(const char *objectName, Reindex= ObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); =20 - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(get_rel_persistence(relid))) { (void) ReindexRelationConcurrently(relid, options); /* ReindexRelationConcurrently() does the verbose output */ @@ -2771,6 +2780,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Open relation to get its indexes */ heapRelation =3D table_open(relationOid, ShareUpdateExclusiveLock); =20 + /* Temporary tables cannot be processed concurrently */ + Assert(heapRelation->rd_rel->relpersistence !=3D RELPERSISTENCE_TEMP); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -2864,6 +2876,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Save the list of relation OIDs in private context */ oldcontext =3D MemoryContextSwitchTo(private_context); =20 + /* Temporary tables cannot be processed concurrently */ + Assert(get_rel_persistence(heapId) !=3D RELPERSISTENCE_TEMP); + /* Track the heap relation of this index for session locks */ heapRelationIds =3D list_make1_oid(heapId); =20 @@ -2939,6 +2954,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/test/regress/expected/create_index.out b/src/test/regress/= expected/create_index.out index 645ae2cf34..4be4379f77 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1398,6 +1398,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 -- @@ -2381,6 +2406,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 73a55ead4b..0df4eec80a 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -489,6 +489,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 -- @@ -960,6 +985,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..8cb01107fa 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 index creation 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..0f8b4ed3a9 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 index creation 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/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 10881ab03a..d26b746505 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -162,6 +162,11 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DAT= ABASE | SYSTEM } [ CONCURR — see . + + For temporary tables, REINDEX is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + =20 --OaZoDhBhXzo6bW1J-- --9l24NVCWtSuIVIod Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl3zCWAACgkQnvQgOdby QH0+hA//W72fYauRiij6eJl9K/nA/vF/D/uEi197HpefmIxDIFOPKGBZQqJgACig zQE+x9TfS55QrAXN2ZgsIeJVD37jT0sHfZW7yFu+KFvR9HikfbN+7oSpa0CUDzOy zshiBsoF9GsAQz/vZ/RIy3a2nA2vGQpKAuqVOxDDanxcYRadXChljZ8bwK1nN+hQ rIj82P0LuAGPO4KhSJ2jPEP5MbsjUsvQ4CzpUq5X9KbT0IJl3+KAQgEztxhC9EM/ eqIV/pGBR0b8DAJmCdPYn/VwgAyQ9VKkwPYreeoMYjsFZ5dvi/SOBFqtvasPwLeq HvHpCZmMteulBrhF3QDyIQcYHxLbQ7TezlFh+cG3FzY64INUuXarLIyMRXepjQpe xzMv2JfHWhaxgi/9EihFBawb9cH0jgargAXxhfUF9f0BRQacPfBS+gT/MMoojE+f vrpWHEb9hwmA4qTrsF1QghqTxGmmauL0fEXG7s0zR2Or+TZd08QSu/6jMdvqnzLz 6nYSYHoNIQj/aE8rnkzS7t98oEvqPQVD0ulqIuLthsjtae5ODJUn/1CvfyCzAp37 O7Z1v2XmwXN/jgfPuYM8b8K02MiMIyaXw5kx2DMcO/pUcjapVTObN27w+73cOIvD 9lEz1hRt4TK6mKnLuTZJn2fMMTsUbmf0zZgj7uCWEzBMBWtA1EI= =/2ou -----END PGP SIGNATURE----- --9l24NVCWtSuIVIod--