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 1iUvfY-0004nM-BM for pgsql-bugs@arkaria.postgresql.org; Wed, 13 Nov 2019 16:39:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iUvfX-0003ly-0l for pgsql-bugs@arkaria.postgresql.org; Wed, 13 Nov 2019 16:38:59 +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 1iUvfW-0003lr-3S for pgsql-bugs@lists.postgresql.org; Wed, 13 Nov 2019 16:38:58 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iUvfT-0007Tj-Jt for pgsql-bugs@lists.postgresql.org; Wed, 13 Nov 2019 16:38:57 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id E20AA220A3; Wed, 13 Nov 2019 11:38:52 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Wed, 13 Nov 2019 11:38:52 -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=ijaBTXLyKOiHgK+XPt9gDnnKMMW wIFU2BvmN4watQis=; b=VVZDsOa2G8g6MHcUJN/B3idOP4at7q1fI/oNX68YBSk ME3Qia0oX60AtwJJUrcbW/CC4Ek7nDunW3uSf9jUpw/eg88iW+oK8Vv8jryCP4U6 /rqwVzdHiBr8XsKTXZy9UMu9DPFcP+fTpCmtzT3se6td59oj9E585bRC9AlFEJye LVORBLCsgbc6+cZ1W6v7WFzWoZZkBsqoLsTOtlHX/62kieYizaLPOcjYFGR4qo3X g9anGSLesrgvgPBSl7GEoEvR/0TUwg1kGBM4s6y+HW6IhTAaRrCNfCFbDo2hEmCw qyQuUXxVl1V6Ogwz0lZeGdzbTvPIIRG99UlQjOoZaQQ== 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=ijaBTX LyKOiHgK+XPt9gDnnKMMWwIFU2BvmN4watQis=; b=yY/4U68o3uOSRSPTkGALzI MHB/74a9StQgt/PMesMH8Bvzzj8OyfP8X/+BkCKy10U3c0P25tdJrwson2nuhoGB Ztlpep0s/8qPeNA3D+LtfiKPPz/XqXliMOEdczTFZFhqdCmW1Hrgo9ZPySGQKugr UqK5cq5+i3w2qx0Ckg0gl0l22EVB93dRx4JzD8w/rdgGglnMcoIXXVAAYP1xalxG fDgCdzaO5vF5EftbvU2DR7nkJbAgqLHkVRV3PzaBdNzcwI8HS31Zk3cdzazzyymp IAdHLJEaMs62P7Dc3g9KwteOSa7ILAEnMUbVf3m1WCXu2qjX2syaFGnY1vF3V9AQ == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrudefuddgleegucetufdoteggodetrfdotf 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 674973060060; Wed, 13 Nov 2019 11:38:52 -0500 (EST) Date: Wed, 13 Nov 2019 08:38:51 -0800 From: Andres Freund To: Tom Lane Cc: Manuel Rigger , PostgreSQL mailing lists Subject: Re: REINDEX CONCURRENTLY unexpectedly fails Message-ID: <20191113163851.e2enkdpvd3nrobst@alap3.anarazel.de> References: <20191113154837.mgz2rnl6d7k6tas7@alap3.anarazel.de> <12383.1573660748@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <12383.1573660748@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2019-11-13 10:59:08 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2019-11-13 15:29:53 +0100, Manuel Rigger wrote: > >> On the latest trunk version, I get an error "index "t0_pkey_ccnew" > >> already contains data" when using REINDEX CONCURRENTLY: > >> > >> CREATE TEMP TABLE t0(c1 INT PRIMARY KEY) ON COMMIT DELETE ROWS; > >> REINDEX TABLE CONCURRENTLY t0; -- unexpected: ERROR: index > >> "t0_pkey_ccnew" already contains data > > > It think we really ought to just refuse CIC (and thereby REINDEX > > CONCURRENTLY) for ON COMMIT DELETE/DROP temp tables. Given that CIC > > internally uses transactions, it makes no sense to use CIC on such a > > table. > > It's not real clear why there would be any point in (RE)INDEX > CONCURRENTLY on a temp table anyway, since no other session could > be using it. Right. I guess it's not necessarily always clear in all contexts that one is dealing with a temp table, rather than a normal table. > +1 for just erroring out, rather than working hard to support such a > case. I wonder if we instead ought to just ignore the CONCURRENTLY when targetting a temp table? That'd be a correct optimization for temp tables, and would fix the issue at hand... Greetings, Andres Freund