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 1hLVoS-0007io-2P for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Apr 2019 16:41: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 1hLVoQ-0005Xl-K5 for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Apr 2019 16:40:58 +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 1hLVoQ-0005Xe-89 for pgsql-hackers@lists.postgresql.org; Tue, 30 Apr 2019 16:40:58 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLVoJ-0004CM-HO for pgsql-hackers@lists.postgresql.org; Tue, 30 Apr 2019 16:40:57 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x3UGekW2005204; Tue, 30 Apr 2019 12:40:46 -0400 From: Tom Lane To: Andres Freund cc: Michael Paquier , Postgres hackers Subject: Re: REINDEX INDEX results in a crash for an index of pg_class since 9.6 In-reply-to: <30579.1556639470@sss.pgh.pa.us> References: <20190425210309.cnofpjnwsrhdnahd@alap3.anarazel.de> <31933.1556226753@sss.pgh.pa.us> <20190426020248.iyev66bkpf2abxqd@alap3.anarazel.de> <7882.1556290296@sss.pgh.pa.us> <12189.1556575627@sss.pgh.pa.us> <20190429220924.lfptluzkujb26y2n@alap3.anarazel.de> <20190430030342.jmbgnih4q3cdoufz@alap3.anarazel.de> <26051.1556599053@sss.pgh.pa.us> <26528.1556599820@sss.pgh.pa.us> <20190430070552.jzqgcy4ihalx7nur@alap3.anarazel.de> <30579.1556639470@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 30 Apr 2019 11:51:10 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <5202.1556642445.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 30 Apr 2019 12:40:46 -0400 Message-ID: <5203.1556642446@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk I wrote: > I haven't been able to reproduce this locally yet, but my guess is that > the REINDEX wants to update some row that was already updated by the > concurrent transaction, so it has to wait to see if the latter commits > or not. And, of course, waiting while holding AccessExclusiveLock on > any index of pg_class is a Bad Idea (TM). But I can't quite see why > we'd be doing something like that during the reindex ... Ah-hah: the secret to making it reproducible is what prion is doing: -DRELCACHE_FORCE_RELEASE -DCATCACHE_FORCE_RELEASE Here's a stack trace from reindex's side: #0 0x00000033968e9223 in __epoll_wait_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x0000000000787cb5 in WaitEventSetWaitBlock (set=3D0x22d52f0, timeout=3D= -1, = occurred_events=3D0x7ffc77117c00, nevents=3D1, = wait_event_info=3D) at latch.c:1080 #2 WaitEventSetWait (set=3D0x22d52f0, timeout=3D-1, = occurred_events=3D0x7ffc77117c00, nevents=3D1, = wait_event_info=3D) at latch.c:1032 #3 0x00000000007886da in WaitLatchOrSocket (latch=3D0x7f90679077f4, = wakeEvents=3D, sock=3D-1, timeout=3D-1, = wait_event_info=3D50331652) at latch.c:407 #4 0x000000000079993d in ProcSleep (locallock=3D, = lockMethodTable=3D) at proc.c:1290 #5 0x0000000000796ba2 in WaitOnLock (locallock=3D0x2200600, owner=3D0x221= 3470) at lock.c:1768 #6 0x0000000000798719 in LockAcquireExtended (locktag=3D0x7ffc77117f90, = lockmode=3D, sessionLock=3D,= = dontWait=3Dfalse, reportMemoryError=3Dtrue, locallockp=3D0x0) at lock.= c:1050 #7 0x00000000007939b7 in XactLockTableWait (xid=3D2874, = rel=3D, ctid=3D, = oper=3DXLTW_InsertIndexUnique) at lmgr.c:658 #8 0x00000000004d4841 in heapam_index_build_range_scan ( heapRelation=3D0x7f905eb3fcd8, indexRelation=3D0x7f905eb3c5b8, = indexInfo=3D0x22d50c0, allow_sync=3D, anyvisible=3D= false, = progress=3Dtrue, start_blockno=3D0, numblocks=3D4294967295, = callback=3D0x4f8330 <_bt_build_callback>, callback_state=3D0x7ffc77118= 4f0, = scan=3D0x2446fb0) at heapam_handler.c:1527 #9 0x00000000004f9db0 in table_index_build_scan (heap=3D0x7f905eb3fcd8, = index=3D0x7f905eb3c5b8, indexInfo=3D0x22d50c0) at ../../../../src/include/access/tableam.h:1437 #10 _bt_spools_heapscan (heap=3D0x7f905eb3fcd8, index=3D0x7f905eb3c5b8, = indexInfo=3D0x22d50c0) at nbtsort.c:489 #11 btbuild (heap=3D0x7f905eb3fcd8, index=3D0x7f905eb3c5b8, indexInfo=3D0x= 22d50c0) at nbtsort.c:337 #12 0x0000000000547e33 in index_build (heapRelation=3D0x7f905eb3fcd8, = indexRelation=3D0x7f905eb3c5b8, indexInfo=3D0x22d50c0, isreindex=3Dtru= e, = parallel=3D) at index.c:2724 #13 0x0000000000548b97 in reindex_index (indexId=3D2662, = skip_constraint_checks=3Dfalse, persistence=3D112 'p', options=3D0) at index.c:3349 #14 0x00000000005490f1 in reindex_relation (relid=3D,= = flags=3D5, options=3D0) at index.c:3592 #15 0x00000000005ed295 in ReindexTable (relation=3D0x21e2938, options=3D0,= = concurrent=3D) at indexcmds.c:2422 #16 0x00000000007b5f69 in standard_ProcessUtility (pstmt=3D0x21e2cf0, = queryString=3D0x21e1f18 "REINDEX TABLE pg_class;", = context=3DPROCESS_UTILITY_TOPLEVEL, params=3D0x0, queryEnv=3D0x0, = dest=3D0x21e2de8, completionTag=3D0x7ffc77118d80 "") at utility.c:790 #17 0x00000000007b1689 in PortalRunUtility (portal=3D0x2247c38, pstmt=3D0x= 21e2cf0, = isTopLevel=3D, setHoldSnapshot=3D, = dest=3D0x21e2de8, completionTag=3D) at pquery.c:1= 175 #18 0x00000000007b2611 in PortalRunMulti (portal=3D0x2247c38, isTopLevel=3D= true, = setHoldSnapshot=3Dfalse, dest=3D0x21e2de8, altdest=3D0x21e2de8, = completionTag=3D0x7ffc77118d80 "") at pquery.c:1328 #19 0x00000000007b2eb0 in PortalRun (portal=3D0x2247c38, = count=3D9223372036854775807, isTopLevel=3Dtrue, run_once=3Dtrue, dest=3D= 0x21e2de8, = altdest=3D0x21e2de8, completionTag=3D0x7ffc77118d80 "") at pquery.c:79= 6 #20 0x00000000007af2ab in exec_simple_query ( query_string=3D0x21e1f18 "REINDEX TABLE pg_class;") at postgres.c:1215 So basically, the problem here lies in trying to re-verify uniqueness of pg_class's indexes --- there could easily be entries in pg_class that haven't committed yet. I don't think there's an easy way to make this not deadlock against concurrent DDL. For sure I don't want to disable the uniqueness checks. regards, tom lane