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 1iXEvW-0002nB-Bo for pgsql-bugs@arkaria.postgresql.org; Wed, 20 Nov 2019 01:37:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iXEvS-0007Jb-QY for pgsql-bugs@arkaria.postgresql.org; Wed, 20 Nov 2019 01:36:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXEvR-0007JU-NO for pgsql-bugs@lists.postgresql.org; Wed, 20 Nov 2019 01:36:58 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iXEvN-0000nB-8q for pgsql-bugs@lists.postgresql.org; Wed, 20 Nov 2019 01:36:56 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 36770220E7; Tue, 19 Nov 2019 20:36:51 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Tue, 19 Nov 2019 20:36:51 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=eRUFIA6HEDld09TFEShDyWe/SyT v6RM/i0dVsJrcke0=; b=kbYWhsAZRAdK0nHUX2nuC5C48IOIHZcmEB4iaeuJLxb Pc99aclK/r1u1DZyFp3VsimGmyIymdEc6ywNrxdeNTgMgfi//MPk63fq/HZAHbBi 6z0Wtw5TpKtNfLGvb/EAfrYmjelNo96cKKHzHUVrfcud+BV4LyAslGOFemJ7up1s DeDXvybMxMuc6m224sURMJyAFMvSFFgw2j3QwLeI12ZPwUdsDOKg9dO+dXJamBwr ZUCDRwDYkHfQ6qEaa28Le4h2z2ZaU32VCdC9ffwJnjemy4KwZ4uLMnPYFxa3pNzJ 0cCJtG8TGt8FU/g4yHvz3UZQvFT6QGKUtG8fsEpEDpQ== 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=eRUFIA 6HEDld09TFEShDyWe/SyTv6RM/i0dVsJrcke0=; b=QZEnGDzcVWs/BvpYkSW3dq CHJ9+TqMTE2ElTbDEgc/06Xd4TUVe3wth4uJhUvBiqAu7qqfKS9SaC3ZEgqRF8T8 /j9jmWmHb+ijzDa1sZg1hplBFGE70KVrejh+H49cRnoK/8ABF6vUUZ2eP5JQEQKi 8jJG/1CLLplPE7rEP0SVhI0I5SJCqzaVb5laWzenDYx6oa45EABB+we9alB2yFOH TqjhKWiVJsJu6zjlPnL9+ku7CJeNpuUqPlsMFJGAR8xy6cpY8UIExCSaPn87FZTA UD+U2vSxAleMzrpxOYnVg8ZZdlLF/Ax4tfSt8xBI73t5zrNNTEmsQ125y322+qrg == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrudegledgfeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvffukfhfgggtuggjsehttdertddttddvnecuhfhrohhmpeetnhgurhgv shcuhfhrvghunhguuceorghnughrvghssegrnhgrrhgriigvlhdruggvqeenucfkphepie ejrdduiedtrddvudekrddvfeejnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrnhgurhgv shesrghnrghrrgiivghlrdguvgenucevlhhushhtvghrufhiiigvpedt X-ME-Proxy: Received: from intern.anarazel.de (c-67-160-218-237.hsd1.ca.comcast.net [67.160.218.237]) by mail.messagingengine.com (Postfix) with ESMTPA id 6612080061; Tue, 19 Nov 2019 20:36:50 -0500 (EST) Date: Tue, 19 Nov 2019 17:36:49 -0800 From: Andres Freund To: Michael Paquier Cc: Tom Lane , Manuel Rigger , PostgreSQL mailing lists Subject: Re: REINDEX CONCURRENTLY unexpectedly fails Message-ID: <20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de> 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> <20191115080706.GI1849@paquier.xyz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20191115080706.GI1849@paquier.xyz> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, I'm on vacation till early December, so I'll not respond quickly.... On 2019-11-15 17:07:06 +0900, Michael Paquier wrote: > 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. Probably worth fixing separately? > 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. I'm extremely doubtful this works correctly. E.g., what prevents the tids for index_concurrently_set_dead() from being recycled and pointing to a different row after one of the internal transactions? > diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.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 = stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock; > rel = table_open(relationId, lockmode); > > + /* > + * 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 == RELPERSISTENCE_TEMP) > + stmt->concurrent = false; I don't think "ignore concurrent index creation" is a good description - they're still created. I'd rephrase it as "For temporary tables build index non-concurrently", or something in that vein. I think we also need to mention somewhere that it's actually crucial to ignore them, as otherwise we'd run into problems with on commit truncate / drop. Probably worthwhile to centralize check and comments into one place, to avoid duplication / them diverging. Perhaps something like IndexCreationSupportsConcurrent() or IndexCreationForceNonConcurrent()? > @@ -2771,6 +2797,9 @@ ReindexRelationConcurrently(Oid relationOid, int options) > /* Open relation to get its indexes */ > heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); > > + /* Temporary tables are not processed concurrently */ > + Assert(heapRelation->rd_rel->relpersistence != RELPERSISTENCE_TEMP); s/are not/can not/? > +-- 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; > -- I'd also add a test for ON COMMIT DROP. > -- Try some concurrent index drops > -- DROP INDEX CONCURRENTLY likely has nearly exactly the same problem, right? > diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.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 EXISTS ] — see endterm="sql-createindex-concurrently-title"/>. > > + > + This option is ignored for temporary relations. > + > > > > 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 | DATABASE | SYSTEM } [ CONCURR > — see endterm="sql-reindex-concurrently-title"/>. > > + > + This option is ignored for temporary relations. > + > > > I think either this needs to be more verbose, explaining that there's no harm from the option being ignored, or it should be ignored as an implementation detail. Greetings, Andres Freund