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 1iVWdW-0006PJ-Je for pgsql-bugs@arkaria.postgresql.org; Fri, 15 Nov 2019 08:07:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iVWdT-00082K-UL for pgsql-bugs@arkaria.postgresql.org; Fri, 15 Nov 2019 08:07:19 +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 1iVWdR-00082D-Mb for pgsql-bugs@lists.postgresql.org; Fri, 15 Nov 2019 08:07:19 +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 1iVWdN-0002Rx-Nf for pgsql-bugs@lists.postgresql.org; Fri, 15 Nov 2019 08:07:16 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 2531A2207D; Fri, 15 Nov 2019 03:07:12 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Fri, 15 Nov 2019 03:07:12 -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=ost0D7EtmyjJqY4o2vz2h/PbOUn jQDkgc48pg4t8q8Y=; b=To8Pnc01viwgZZX5YqQwZ6IQLhoR8BdZC4COSXxlq6M UdDyVIgxQ8WH+5BhFbmnrs4xSdmvBIKx/j/UPUDJeC+sQbEniPTrbushMoEVYd8Z +hiL3lJAHymEV/wFcvhV+UvqLwhA4GLuTlZ3Sgc/P+SKQh/jbQmlTXojMwdFMF6X gVwby3VANYD5ieCMWK0FhHspfuYk/OZjKEPuErUNiZwNndy+5YtbI/4W2HZD+Ez1 V59B6iSaoERDOdZd2ltU52iuFm7Ess+OekMxmACxzs6qOYoP4MzaB9YDCSMHDOMz c7KeVbt8YqWBPMwuxSTUuFebNdZ7DkYW7x/VlBjlnjQ== 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=ost0D7 EtmyjJqY4o2vz2h/PbOUnjQDkgc48pg4t8q8Y=; b=ernrmAlsduaRgo+XwNfqNr R/riduafh8xU7q9xBIOKjAKMULcbl9sC1PJ2tRWxNLYTob5q7WTMzDdaiKC0TfwB jrQ1LwWGzQbkXYXW1hzUybYhYLA6HitRkP5x4+TyPEObBLB6j3/Zcy4KZbRf+zaG rWjaGcrJHurWYqI9dEeZLSkvAAn6t14E8zOdzGpp/WqfGgM3gWYjrCCegsgB8G5P es8j9I1yK42/LCHfQyvuEx0esg56kwZITqXDy9CnkiM8V2SLDtE8PMA7giNYNJI1 8VHVBP+uKLW1g7jlN7v6ibZvRBKVsoHgWn9IIiAARL2e7ZvoSbyVdiJaDiDy7MXg == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrudefgedgudduhecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enfghrlhcuvffnffculdejtddmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredt tddvnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesph grqhhuihgvrhdrgiihiieqnecukfhppeduudekrddvgedurddvgeekrdduuddtnecurfgr rhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiiinecuve hluhhsthgvrhfuihiivgeptd X-ME-Proxy: Received: from paquier.xyz (mp76f1f86e.ap.nuro.jp [118.241.248.110]) by mail.messagingengine.com (Postfix) with ESMTPA id 0D30D306005F; Fri, 15 Nov 2019 03:07:09 -0500 (EST) Date: Fri, 15 Nov 2019 17:07:06 +0900 From: Michael Paquier To: Andres Freund Cc: Tom Lane , Manuel Rigger , PostgreSQL mailing lists Subject: Re: REINDEX CONCURRENTLY unexpectedly fails Message-ID: <20191115080706.GI1849@paquier.xyz> References: <20191113154837.mgz2rnl6d7k6tas7@alap3.anarazel.de> <12383.1573660748@sss.pgh.pa.us> <20191113163851.e2enkdpvd3nrobst@alap3.anarazel.de> <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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="ofZMSlrAVk9bLeVm" Content-Disposition: inline In-Reply-To: <20191115032141.GH1849@paquier.xyz> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --ofZMSlrAVk9bLeVm Content-Type: multipart/mixed; boundary="Op27XXJsWz80g3oF" Content-Disposition: inline --Op27XXJsWz80g3oF Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Fri, Nov 15, 2019 at 12:21:41PM +0900, Michael Paquier wrote: > On Thu, Nov 14, 2019 at 06:54:12PM -0800, Andres Freund wrote: >> I think it'd be really user hostile if ReindexMultipleTables() suddenly >> started to error out if there were any temp tables. That clearly can't >> be an option. >=20 > Okay. So, here is a patch to address all that. I have also added a test for REINDEX SCHEMA on a temporary schema. While looking at the problem, I have found a crash if trying to reindex concurrently an index or a table using a temporary relation from a different session because we have been lacking checks with RELATION_IS_OTHER_TEMP() in the concurrent code paths. For a schema or database reindex this was not happening as these are filtered out using isTempNamespace(). Please note that I have not changed index_drop() for the concurrent mode. Per my checks this does not actually cause any direct bugs as this code path just takes care of dropping the dependencies with the index. There could be an argument for changing that on HEAD though, but I am not sure that this is worth the complication either. -- Michael --Op27XXJsWz80g3oF Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="reindex-conc-temp-v1.patch" Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexc= mds.c index 374e2d0efe..7de36ca7e2 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -550,6 +550,15 @@ DefineIndex(Oid relationId, lockmode =3D stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock; rel =3D table_open(relationId, lockmode); =20 + /* + * Ignore concurrent index creation for temporary tables. Such + * relations only work with the current session, so they are not + * subject to concurrency problems. Using a non-concurrent build + * is also more performant. + */ + if (rel->rd_rel->relpersistence =3D=3D RELPERSISTENCE_TEMP) + stmt->concurrent =3D false; + namespaceId =3D RelationGetNamespace(rel); =20 /* Ensure that it makes sense to index this kind of relation */ @@ -2349,7 +2358,12 @@ ReindexIndex(RangeVar *indexRelation, int options, b= ool concurrent) persistence =3D irel->rd_rel->relpersistence; index_close(irel, NoLock); =20 - if (concurrent) + /* + * For temporary indexes, ignore concurrent reindexes. Such relations + * only work with the session assigned to them, so they are not subject + * to concurrent concerns, and a non-concurrent reindex is more efficient. + */ + if (concurrent && persistence !=3D RELPERSISTENCE_TEMP) ReindexRelationConcurrently(indOid, options); else reindex_index(indOid, false, persistence, @@ -2442,7 +2456,12 @@ ReindexTable(RangeVar *relation, int options, bool c= oncurrent) 0, RangeVarCallbackOwnsTable, NULL); =20 - if (concurrent) + /* + * For temporary tables, ignore concurrent reindexes. Such relations + * only work with the session assigned to them, so they are not subject + * to concurrent concerns, and a non-concurrent reindex is more efficient. + */ + if (concurrent && get_rel_persistence(heapOid) !=3D RELPERSISTENCE_TEMP) { result =3D ReindexRelationConcurrently(heapOid, options); =20 @@ -2648,7 +2667,14 @@ ReindexMultipleTables(const char *objectName, Reinde= xObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); =20 - if (concurrent) + /* + * For temporary relations, ignore concurrent reindexes. Such + * relations only work with the session assigned to them, so they + * are not subject to concurrent concerns, and a non-concurrent + * reindex is more efficient. + */ + if (concurrent && + get_rel_persistence(relid) !=3D RELPERSISTENCE_TEMP) { (void) ReindexRelationConcurrently(relid, options); /* ReindexRelationConcurrently() does the verbose output */ @@ -2771,6 +2797,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Open relation to get its indexes */ heapRelation =3D table_open(relationOid, ShareUpdateExclusiveLock); =20 + /* Temporary tables are not 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 +2893,9 @@ ReindexRelationConcurrently(Oid relationOid, int opti= ons) /* Save the list of relation OIDs in private context */ oldcontext =3D MemoryContextSwitchTo(private_context); =20 + /* Temporary tables are not 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 diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/= expected/create_index.out index 1cdb7a9663..112188862b 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1398,6 +1398,17 @@ Indexes: "concur_index5" btree (f2) WHERE f1 =3D 'x'::text "std_index" btree (f2) =20 +-- Temporary tables with concurrent builds +CREATE TEMP TABLE concur_temp (f1 int, f2 text); +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP TABLE concur_temp; +-- On-commit actions +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 TABLE concur_temp; -- -- Try some concurrent index drops -- @@ -2368,6 +2379,43 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::= regclass); (1 row) =20 DROP TABLE concur_exprs_tab; +-- Temporary tables, where CONCURRENTLY is ignored. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text); +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 actions. PRESERVE ROWS is the default, tested above. +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; +-- 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 76598085f7..94764b832a 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -489,6 +489,18 @@ VACUUM FULL concur_heap; REINDEX TABLE concur_heap; \d concur_heap =20 +-- Temporary tables with concurrent builds +CREATE TEMP TABLE concur_temp (f1 int, f2 text); +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP TABLE concur_temp; +-- On-commit actions +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 TABLE concur_temp; + -- -- Try some concurrent index drops -- @@ -954,6 +966,37 @@ 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, where CONCURRENTLY is ignored. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text); +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 actions. PRESERVE ROWS is the default, tested above. +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; +-- 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..e26f450846 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -129,6 +129,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXI= STS ] . + + This option is ignored for temporary relations. + =20 diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 10881ab03a..e5d2b1a06e 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -162,6 +162,9 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATA= BASE | SYSTEM } [ CONCURR — see . + + This option is ignored for temporary relations. + =20 --Op27XXJsWz80g3oF-- --ofZMSlrAVk9bLeVm Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl3OXKoACgkQnvQgOdby QH20ixAAkrQr9fxUy8avt4pTz/XfysAvjtZ9S4kKZ+SsHaNLs/SEEDxAutyo4e4C wPnCrw0dKJF7HClJ3DqeBZpAvNvxwlSaR9pdP2hFg3T5crBFCoJkJ1j7Qy94A87l w00ZOCrYzZIBaHTNnmq+qJ6tQvpdLUEZQEObLEvm6Evm2f5FDYtGGzhvaxO54ngC UFu9M83i7S+fBxh7V8TtmVkGNwD/PGt8NUuHvG/XHAZJANlzNNPr336QgqP4XTRA A/ycGBf/RvqYHatuEekKz8nvyNBV8DExaO0BcTShMpTQHMdKfWW6djdU2ElJ2LWy P0kjNoHXlWNAFUxe9+QRBuVaI/+C9EIWE/o5wTAc/CBoTE+rFPwwW2ry6BxrFEuC lcECmjN2IybZRpjTr27cGrHCdUpF04ZamyXLFm8bdLGL29ZpoZhA/OQxvCQaEZTf vts/jS21twoXXGWUoBRKC7KHESrLFokuD6333lTSMYsOCK/bw9FHKO7E+ZJQN07P t4sQGlL6VZCo+nOf6J67HQD1RM8HRwnPUxGF5hLejC0SX0HRbDQs77VEpb7knsgq Y6q1JKJXAaGPZp267A/XPbvsgLy97H+0KRXJ+U4Q5Mm2I4IZSJLyGSOBQnvtoUoW 5FEbpou2cMMEmB1gh78ej31siej90u/Bz5bujT+AY9n/aWhGBQY= =zWn9 -----END PGP SIGNATURE----- --ofZMSlrAVk9bLeVm--