Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKVZt-00000000tGj-3oeO for pgsql-bugs@arkaria.postgresql.org; Sat, 24 Oct 2026 06:50:06 +0000 Received: from localhost ([127.0.0.1]:41650 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xKVZt-00000000OUt-189f for pgsql-bugs@arkaria.postgresql.org; Sat, 24 Oct 2026 06:50:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:37220) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKFbx-0000000G4UG-2Rwq for pgsql-bugs@lists.postgresql.org; Fri, 23 Oct 2026 13:47:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKFbu-00000000acD-35Cc for pgsql-bugs@lists.postgresql.org; Fri, 23 Oct 2026 13:47:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=FGP0i7tQxtDqh5ET6mJOoFTQnNb6JuNWjJSwStPbF+I=; b=qKTfSLPvNjrA/OsvlK1pU9U5rv gwJrX8EDeTPCZFYx/CCx6lMua++SqYnb6gkhHqGHDhxkjuedTl0PiMI6sQyO7+EsK5A4dn7DJ2n1Q aMhZHz9DvuZ+uNe93M8BXsH444/oTOSSCyu6hk+GuEOVw+cXRC7t5PoWbh0rdmIwyHzSlck/mn8Xg BzcEvzVCCZkxtJbOEY5fAVUqttdH0ftYhsSRpjq3MuGQ14POypAMfnQ36CbNR1hXQk8NlMgsHwoAg iIn8VBT92UrZWiBDCFq4ACGbDM59SzQNEmMvLqkm7wN1G8mhMBRSNn5h2ki3WRl4SoPv9Rylh2jyD iIFxbgsw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKFbt-00000001Z91-09O6 for pgsql-bugs@lists.postgresql.org; Fri, 23 Oct 2026 13:47:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xKFbr-00000004ZfS-30ma for pgsql-bugs@lists.postgresql.org; Fri, 23 Oct 2026 13:47:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19792: BUG: Deadlock between WaitIO and read-stream look-ahead under io_method=io_uring (18.6) To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: barton.tomas@gmail.com Reply-To: barton.tomas@gmail.com, pgsql-bugs@lists.postgresql.org Date: Fri, 23 Oct 2026 13:46:30 +0000 Message-ID: <19792-9da3b35b8f4b3e60@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19792 Logged by: Tomas Barton Email address: barton.tomas@gmail.com PostgreSQL version: 18.6 Operating system: Debian 12 Description: =20 PostgreSQL 18.6 (Debian 18.6-1.pgdg12+2), Debian 12, kernel 6.1.187, io_method=3Dio_uring. io_method =3D io_uring Storage: local ext4 on md RAID, no I/O errors in dmesg Extensions in play: pg_stat_statements (no custom AMs; the affected table has only btree indexes) Three backends hung indefinitely and could not be cancelled or terminated (`statement_timeout`, `pg_cancel_backend`, `pg_terminate_backend` all ineffective, as expected for LWLock / buffer-IO waits): | pid | backend | wait event | what it is doing | |---|---|---|---| | 3689628 | client backend (A) | IPC:BufferIo | `INSERT ... ON CONFLICT DO UPDATE`, in `_bt_split` -> `_bt_allocbuf` -> `ReadBuffer(block 628)` -> `StartBufferIO` -> `WaitIO`, holding exclusive content lock on leaf block 627 | | 2213934 | autovacuum worker (B) | LWLock:BufferContent | `btvacuumcleanup` -> `btvacuumscan` -> `btvacuumpage` -> `_bt_lockbuf(block 627, BT_READ)` | | 2214042 | client backend (C) | LWLock:BufferContent | backward index-only scan (`MAX(last_update)`), `_bt_search` -> `_bt_relandgetbuf` -> `_bt_lockbuf(block 627)` | Block 627 and 628 both belong to the btree index `last_update_similar_items_thenlper_gte_large_recent` (relfilenode 3378615556) on table `"db-prod".similar_items_thenlper_gte_large_recent` (oid 3371211934). The index is on a timestamp column, so inserts land on the rightmost leaf (627) and split frequently. Block 628 is a free page returned by the FSM. ## Mechanism 1. Backend B (autovacuum, no dead tuples, in the index-cleanup pass) walks the index through a read stream. The stream issued the read of block 628 as look-ahead. `pg_aios` shows that I/O as handle **17445**, generation **9035984**, owner pid **2213934**, state **SUBMITTED**. 2. Backend A, splitting leaf 627 (exclusive content lock held), got block 628 from the FSM and called `ReadBuffer`. `StartBufferIO` found `BM_IO_IN_PROGRESS` already set by B. At that moment B had set the flag but **had not yet staged the I/O**, so `buf->io_wref` was still invalid. The `WaitIO` frame of A confirms this: its local copy is `iow =3D {aio_index =3D 4294967295, ...}`. A therefore took the fallback branch and went to sleep on the buffer's condition variable (`ConditionVariableSleep(cv, WAIT_EVENT_BUFFER_IO)`). 3. B then staged and submitted the I/O. `buffer_stage_common` sets `buf_hdr->io_wref` but does **not** broadcast the buffer's I/O condition variable, so A is not woken. The buffer header now reads `io_wref =3D {aio_index =3D 17445, generation_lower =3D 903598= 4}` while A still sleeps. 4. B proceeded to `_bt_lockbuf(627, BT_READ)` and blocked on the content lock held by A. 5. Under io_uring, the completion of handle 17445 is only processed by its owner (B) or by a backend that waits on that wait reference (`pgaio_wref_wait`). B is blocked on an LWLock and A never re-reads `io_wref` (nothing wakes it). The completion sits in B's ring forever. Backend C and any future reader of block 627 queue behind A. In `io_method=3Dworker` the I/O worker runs the completion callback, which calls `TerminateBufferIO` and broadcasts the condition variable, so the same race is harmless there. The relevant code in REL_18_STABLE is unchanged since the original AIO commits: `WaitIO()` sleeps on the buffer CV when `io_wref` is invalid, and `buffer_stage_common()` sets `io_wref` without a broadcast. Master (post commits 74eafea / 999dec9, March 2026) has the same fallback; `AsyncReadBuffers()` there even comments: "It's possible that another backend has started IO on the buffer but not yet set its wait reference. In this case, we have no choice but to wait for either the wait reference to be valid or the IO to be done." Nothing signals the first of those two transitions. Possibly related (different trigger, same family): "Potential deadlock in pgaio_io_wait()", Thomas Munro / Heikki Linnakangas, pgsql-hackers, Aug-Oct 2025 (https://www.postgresql.org/message-id/CA%2BhUKG%2BmZYrSdnhk-XrBYO18H829K77= S9gMKUsykOiTJtqB43g%40mail.gmail.com). Also BUG #19725 (io_uring read failures on 18.6). ## Suggested fix Either broadcast `BufferDescriptorGetIOCV(buf)` after setting `buf_hdr->io_wref` in `buffer_stage_common()` (so a waiter in `WaitIO()` re-checks and switches to `pgaio_wref_wait()`), or make `WaitIO()` sleep with a timeout / re-check loop when it observes `BM_IO_IN_PROGRESS` with an invalid `io_wref`. ## Recovery Fast shutdown cannot complete (the backends do not reach `CHECK_FOR_INTERRUPTS`). Immediate shutdown or SIGKILL of the INSERT backend, followed by crash recovery. `io_method` was changed to `worker` on restart. --- # Evidence ## 1. pg_stat_activity (monitoring view, before diagnosis) ``` pid | duration | cpu | mem | state | wait_event | client | user | query ---------+-----------------+-----+---------+--------+----------------------= --+-------------------+-------------+--------------------------------------= ------------------------ 2213934 | 1d 18h 18m 19s | 0% | 3668 kB | active | LWLock:BufferContent | autovacuum worker | | autovacuum: VACUUM db-prod.similar_items_thenlper_... 3689628 | 1d 18h 18m 18s | 0% | 29 MB | active | IPC:BufferIo | 10.0.3.1 | recommender | INSERT INTO "db-prod"."similar_items_thenlper_gte_... 2214042 | 1d 18h 18m 3s | 0% | 3880 kB | active | LWLock:BufferContent | 10.0.2.13 | recommender | SET LOCAL statement_timeout =3D 600000; SELECT CURRENT_TIME... ``` ## 2. OS state of the three processes (no D state, nothing pending in the kernel) ``` PID STAT WCHAN CMD 2213934 Ss futex_wait_queue postgres: 18/main: autovacuum worker recommender 2214042 Ss futex_wait_queue postgres: 18/main: recommender recommender 10.0.3.1(43106) SELECT 3689628 Ssl do_epoll_wait postgres: 18/main: recommender recommender 10.0.2.13(33290) INSERT =3D=3D /proc/2213934/stack [<0>] futex_wait_queue+0x60/0x90 [<0>] futex_wait+0x185/0x270 [<0>] do_futex+0x106/0x1b0 [<0>] __x64_sys_futex+0x8e/0x1d0 [<0>] do_syscall_64+0x5d/0xb0 [<0>] entry_SYSCALL_64_after_hwframe+0x6e/0xd8 =3D=3D /proc/3689628/stack [<0>] do_epoll_wait+0x698/0x7d0 [<0>] __x64_sys_epoll_wait+0x6f/0x110 [<0>] do_syscall_64+0x5d/0xb0 [<0>] entry_SYSCALL_64_after_hwframe+0x6e/0xd8 =3D=3D /proc/2214042/stack [<0>] futex_wait_queue+0x60/0x90 [<0>] futex_wait+0x185/0x270 [<0>] do_futex+0x106/0x1b0 [<0>] __x64_sys_futex+0x8e/0x1d0 [<0>] do_syscall_64+0x5d/0xb0 [<0>] entry_SYSCALL_64_after_hwframe+0x6e/0xd8 # threads of the INSERT backend (io_uring worker idle) =3D=3D /proc/3689628/task/1773077 iou-wrk-3689628 S [<0>] io_wqe_worker+0x1b9/0x3a0 [<0>] ret_from_fork+0x22/0x30 =3D=3D /proc/3689628/task/3689628 postgres S [<0>] do_epoll_wait+0x698/0x7d0 [<0>] __x64_sys_epoll_wait+0x6f/0x110 [<0>] do_syscall_64+0x5d/0xb0 [<0>] entry_SYSCALL_64_after_hwframe+0x6e/0xd8 ``` dmesg contains nothing since a routine md data-check on 2026-09-24. ## 3. Settings ``` io_method ----------- io_uring io_workers ------------ 6 ``` ## 4. Relations involved ``` oid | relname | relkind | relfilenode ------------+-----------------------------------------------------+--------= -+------------- 3371211934 | similar_items_thenlper_gte_large_recent | r | 3371211934 3378609811 | similar_items_thenlper_gte_large_recent_pkey | i | 3378609811 3378615556 | last_update_similar_items_thenlper_gte_large_recent | i | 3378615556 ``` ## 5. pg_stat_progress_vacuum for the autovacuum (pid 2213934) ``` pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuple_bytes | dead_tuple_bytes | num_dead_item_ids | indexes_total | indexes_processed | delay_time ---------+-------+-------------+------------+---------------------+--------= ---------+-------------------+--------------------+--------------------+---= -------------------+------------------+-------------------+---------------+= -------------------+------------ 2213934 | 16386 | recommender | 3371211934 | cleaning up indexes | 1204577 | 1204577 | 1204577 | 0 | 2147483648 | 0 | 0 | 2 | 1 | 0 ``` ## 6. pg_aios: the I/O owned by the autovacuum that nobody completes ``` pid | io_id | io_generation | state | target_desc ---------+-------+---------------+-----------+-----------------------------= -------------- 2213934 | 17445 | 9035984 | SUBMITTED | block 628 in file "base/16386/3378615556" ``` The INSERT's own handle (acquired before `StartBufferIO`, never used): ``` pid | io_id | io_generation | state | operation | ... | target_desc ---------+-------+---------------+------------+-----------+-----+----------= --- 3689628 | 10321 | 29264366 | HANDED_OUT | | | ``` (Other rows in `pg_aios` at the time were short-lived reads from unrelated sessions; all of those backends had exited by the next check.) ## 7. Buffer header of the buffer the INSERT waits on (block 628), read via gdb from pid 3689628 ``` (gdb) p *(BufferDesc *)0x7fab2868ad80 $1 =3D {tag =3D {spcOid =3D 1663, dbOid =3D 16386, relNumber =3D 3378615556= , forkNum =3D MAIN_FORKNUM, blockNum =3D 628}, buf_id =3D 994552, state =3D {value =3D 2248671235}, wait_backend_pgp= rocno =3D -1, freeNext =3D -2, io_wref =3D {aio_index =3D 17445, generation_upper =3D 0, generation_= lower =3D 9035984}, content_lock =3D {tranche =3D 62, state =3D {value =3D 1073741824}, w= aiters =3D {head =3D -1, tail =3D -1}}} (gdb) p/x ((BufferDesc *)0x7fab2868ad80)->state.value $2 =3D 0x86080003 ``` Decoded: refcount 3, usagecount 2, flags =3D BM_PERMANENT | BM_IO_IN_PROGRE= SS | BM_TAG_VALID. Not BM_VALID, no BM_IO_ERROR. `io_wref` matches handle 17445 / generation 9035984 above. Note the contrast with the INSERT's `WaitIO` frame below, whose copy of `iow` has `aio_index =3D 4294967295` (invalid): the reference became valid after A we= nt to sleep. ## 8. Backtrace of the INSERT backend (pid 3689628), `bt full` Frames 0-20 with locals. The large `io_pages` scratch array in frame 10 and the `events` array in frame 39 are elided as noise. ``` #0 0x00007fb342f28ef3 in epoll_wait () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x0000561a358ba97b in WaitEventSetWaitBlock (nevents=3D1, occurred_events=3D0x7ffcb93c2b50, cur_timeout=3D-1, set=3D0x561a53fcb8f0) at ./build/../src/backend/storage/ipc/waiteventset.c:1191 #2 WaitEventSetWait (set=3D0x561a53fcb8f0, timeout=3Dtimeout@entry=3D-1, occurred_events=3Doccurred_events@entry=3D0x7ffcb93c2b50, nevents=3Dnevents@entry=3D1, wait_event_info=3Dwait_event_info@entry=3D1342= 17736) at ./build/../src/backend/storage/ipc/waiteventset.c:1139 #3 0x0000561a358af5e5 in WaitLatch (latch=3D, wakeEvents=3DwakeEvents@entry=3D33, timeout=3Dtimeout@entry=3D-1, wait_event_info=3Dwait_event_info@entry=3D134217736) at ./build/../src/backend/storage/ipc/latch.c:196 #4 0x0000561a358bc215 in ConditionVariableTimedSleep (cv=3Dcv@entry=3D0x7fb30e72af00, timeout=3Dtimeout@entry=3D-1, wait_event_info=3Dwait_event_info@entry=3D134217736) at ./build/../src/backend/storage/lmgr/condition_variable.c:163 #5 0x0000561a358bc3be in ConditionVariableSleep (cv=3Dcv@entry=3D0x7fb30e72af00, wait_event_info=3Dwait_event_info@entry=3D134217736) at ./build/../src/backend/storage/lmgr/condition_variable.c:98 #6 0x0000561a3589c191 in WaitIO (buf=3Dbuf@entry=3D0x7fab2868ad80) at ./build/../src/backend/storage/buffer/bufmgr.c:6032 buf_state =3D iow =3D {aio_index =3D 4294967295, generation_upper =3D 0, generation_lower =3D 28473836} cv =3D 0x7fb30e72af00 #7 0x0000561a3589d20d in StartBufferIO (buf=3D0x7fab2868ad80, forInput=3DforInput@entry=3Dtrue, nowait=3Dnowait@entry=3Dfalse) at ./build/../src/backend/storage/buffer/bufmgr.c:6076 #8 0x0000561a3589d3ac in ReadBuffersCanStartIOOnce (nowait=3Dfalse, buffer=3D) at ./build/../src/backend/storage/buffer/bufmgr.c:1566 #9 ReadBuffersCanStartIO (nowait=3Dfalse, buffer=3D) at ./build/../src/backend/storage/buffer/bufmgr.c:1594 #10 AsyncReadBuffers (operation=3Doperation@entry=3D0x7ffcb93c3230, nblocks_progress=3Dnblocks_progress@entry=3D0x7ffcb93c317c) at ./build/../src/backend/storage/buffer/bufmgr.c:1871 buffers =3D 0x7ffcb93c322c flags =3D 8 forknum =3D MAIN_FORKNUM persistence =3D 112 'p' nblocks_done =3D 0 blocknum =3D 628 io_buffers =3D 0x7ffcb93c322c io_buffers_len =3D 0 ioh =3D 0x7fb3363fd390 ioh_flags =3D 1 io_pages =3D {... elided ...} io_context =3D IOCONTEXT_NORMAL io_object =3D IOOBJECT_RELATION did_start_io =3D #11 0x0000561a3589fcac in StartReadBuffersImpl (allow_forwarding=3Dfalse, flags=3D8, nblocks=3D0x7ffcb93c317c, blockNum=3D628, buffers=3D0x7ffcb93c32= 2c, operation=3D0x7ffcb93c3230) at ./build/../src/backend/storage/buffer/bufmgr.c:1437 actual_nblocks =3D 1 maxcombine =3D 0 #12 StartReadBuffer (operation=3Doperation@entry=3D0x7ffcb93c3230, buffer=3Dbuffer@entry=3D0x7ffcb93c322c, blocknum=3Dblocknum@entry=3D628, flags=3Dflags@entry=3D8) at ./build/../src/backend/storage/buffer/bufmgr.c:= 1525 nblocks =3D 1 #13 0x0000561a358a10dd in ReadBuffer_common (strategy=3D, mode=3D, blockNum=3D, forkNum=3D, smgr_persistence=3D, smgr=3D, rel=3D) at ./build/../src/backend/storage/buffer/bufmgr.c:1255 operation =3D {rel =3D 0x7fab1e2e94e8, smgr =3D 0x561a541d2150, persistence =3D 112 'p', forknum =3D MAIN_FORKNUM, strategy =3D 0x0, buffer= s =3D 0x7ffcb93c322c, blocknum =3D 628, flags =3D 8, nblocks =3D 1, nblocks_done = =3D 0, io_wref =3D {aio_index =3D 4294967295, generation_upper =3D 0, generation_l= ower =3D 0}, io_return =3D {...}} buffer =3D 994553 flags =3D 8 #14 ReadBufferExtended (reln=3D, forkNum=3DforkNum@entry=3DMAIN_FORKNUM, blockNum=3D628, mode=3Dmode@entry=3DRBM_NORMAL, strategy=3Dstrategy@entry=3D0x0) at ./build/../src/backend/storage/buffer/bufmgr.c:815 #15 0x0000561a358a15ee in ReadBuffer (reln=3D, blockNum=3D) at ./build/../src/backend/storage/buffer/bufmgr.c:760 #16 0x0000561a355a157c in _bt_allocbuf (rel=3Drel@entry=3D0x7fab1e2e94e8, heaprel=3Dheaprel@entry=3D0x561a54175460) at ./build/../src/backend/access/nbtree/nbtpage.c:906 buf =3D blkno =3D 628 page =3D #17 0x0000561a3559e8f1 in _bt_split (postingoff=3D0, nposting=3D0x0, orignewitem=3D0x0, newitem=3D0x561a54403018, newitemsz=3D16, newitemoff=3D8= , cbuf=3D0, buf=3D992779, itup_key=3D, heaprel=3D0x561a54175460, rel=3D0x7fab1e2e94e8) at ./build/../src/backend/access/nbtree/nbtinsert.c:1= 720 origpagenumber =3D 627 maxoff =3D 7 firstrightoff =3D 6 afterleftoff =3D 2 isleaf =3D true isrightmost =3D true newitemonleft =3D false sbuf =3D 0 spage =3D 0x0 origpage =3D 0x7fad19111000 "\035\353\n" leftpage =3D 0x561a543ff248 "\035\353\n" lefthighkey =3D 0x561a543fdb90 #18 _bt_insertonpg (rel=3D0x7fab1e2e94e8, heaprel=3D0x561a54175460, itup_key=3D, buf=3D992779, cbuf=3D0, stack=3D0x561a543fd490, itup=3D0x561a54403018, itemsz=3D, newitemoff=3D8, postingoff=3D, split_only_page=3D) at ./build/../src/backend/access/nbtree/nbtinsert.c:1217 isroot =3D false isrightmost =3D true isonly =3D false #19 0x0000561a355a0571 in _bt_doinsert (rel=3Drel@entry=3D0x7fab1e2e94e8, itup=3Ditup@entry=3D0x561a54403018, checkUnique=3DcheckUnique@entry=3DUNIQUE_CHECK_NO, indexUnchanged=3DindexUnchanged@entry=3Dfalse, heapRel=3DheapRel@entry=3D0x561a54175460) at ./build/../src/backend/access/nbtree/nbtinsert.c:260 insertstate =3D {itup =3D 0x561a54403018, itemsz =3D 16, itup_key = =3D 0x561a542f3320, buf =3D 992779, bounds_valid =3D true, low =3D 8, stricthig= h =3D 8, postingoff =3D 0} #20 0x0000561a355a6b23 in btinsert (rel=3D0x7fab1e2e94e8, values=3D, isnull=3D, ht_ctid=3D0x561a543fcad8, heapRel=3D0x561a54175460, checkUnique=3DUNIQUE_CHECK_NO, indexUnchanged=3D, indexInfo=3D) at ./build/../src/backend/access/nbtree/nbtree.c:215 #21 0x0000561a35707243 in ExecInsertIndexTuples (resultRelInfo=3DresultRelInfo@entry=3D0x561a5427da50, slot=3Dslot@entry=3D0x561a543fcaa8, estate=3D, update=3Dupdate@entry=3Dtrue, noDupErr=3DnoDupErr@entry=3Dfalse, specConflict=3DspecConflict@entry=3D0x0, arbiterIndexes=3D, onlySummarizing=3D) at ./build/../src/backend/executor/execIndexing.c:449 #22 0x0000561a357336ee in ExecUpdateEpilogue (context=3Dcontext@entry=3D0x7ffcb93c3c80, updateCxt=3D, resultRelInfo=3DresultRelInfo@entry=3D0x561a5427da50, tupleid=3Dtupleid@entry=3D0x7ffcb93c3baa, oldtuple=3Doldtuple@entry=3D0x0, slot=3Dslot@entry=3D0x561a543fcaa8) at ./build/../src/backend/executor/nodeModifyTable.c:2330 #23 0x0000561a35734f27 in ExecUpdate (context=3Dcontext@entry=3D0x7ffcb93c3= c80, resultRelInfo=3DresultRelInfo@entry=3D0x561a5427da50, tupleid=3Dtupleid@entry=3D0x7ffcb93c3baa, oldtuple=3Doldtuple@entry=3D0x0, oldSlot=3D, oldSlot@entry=3D0x561a5427f348, slot=3D0x561a543= fcaa8, canSetTag=3D) at ./build/../src/backend/executor/nodeModifyTable.c:2709 #24 0x0000561a35735c6e in ExecOnConflictUpdate (returning=3D, canSetTag=3D, excludedSlot=3D, conflictTid=3D0x7ffcb93c3baa, resultRelInfo=3D0x561a5427da50, context=3D0x7ffcb93c3c80) at ./build/../src/backend/executor/nodeModifyTable.c:2925 #25 ExecInsert (context=3Dcontext@entry=3D0x7ffcb93c3c80, resultRelInfo=3DresultRelInfo@entry=3D0x561a5427da50, slot=3D, slot@entry=3D0x561a543fcd00, canSetTag=3D, inserted_tuple=3Dinserted_tuple@entry=3D0x0, insert_destrel=3Dinsert_destrel@entry=3D0x0) at ./build/../src/backend/executor/nodeModifyTable.c:1152 #26 0x0000561a3573714e in ExecModifyTable (pstate=3D0x561a5427d848) at ./build/../src/backend/executor/nodeModifyTable.c:4509 #27 0x0000561a35707eda in ExecProcNode (node=3D0x561a5427d848) at ./build/../src/include/executor/executor.h:315 #28 ExecutePlan (dest=3D0x561a5400eed8, direction=3D, numberTuples=3D0, sendTuples=3Dfalse, operation=3DCMD_INSERT, queryDesc=3D0x561a5429b2b8) at ./build/../src/backend/executor/execMain.c:1= 711 #29 standard_ExecutorRun (queryDesc=3D0x561a5429b2b8, direction=3D, count=3D0) at ./build/../src/backend/executor/execMain.c:366 #30 0x00007fb342c434e5 in pgss_ExecutorRun (queryDesc=3D0x561a5429b2b8, direction=3DForwardScanDirection, count=3D0) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:1036 #31 0x0000561a358db264 in ProcessQuery (plan=3D0x561a558c8948, sourceText=3D0x561a542fa9f0 "\n INSERT INTO \"db-prod\".\"similar_items_thenlper_gte_large_recent\" (itemid, similar_items, last_update)\n VALUES ('AmZHZVwxWbZ5', '[{\"id\": \"A7GBDfQ8rMhD\", \"r"..., params=3D0x0, queryEnv=3D0x0, dest=3D0x561a5400e= ed8, qc=3D0x7ffcb93c4110) at ./build/../src/backend/tcop/pquery.c:161 #32 0x0000561a358dbe79 in PortalRunMulti (portal=3Dportal@entry=3D0x561a540e4a48, isTopLevel=3DisTopLevel@entry=3Dtr= ue, setHoldSnapshot=3DsetHoldSnapshot@entry=3Dfalse, dest=3Ddest@entry=3D0x561a= 5400eed8, altdest=3Daltdest@entry=3D0x561a5400eed8, qc=3Dqc@entry=3D0x7ffcb93c4110) at ./build/../src/backend/tcop/pquery.c:1272 #33 0x0000561a358dc326 in PortalRun (portal=3Dportal@entry=3D0x561a540e4a48, count=3Dcount@entry=3D9223372036854775807, isTopLevel=3DisTopLevel@entry=3D= true, dest=3Ddest@entry=3D0x561a5400eed8, altdest=3Daltdest@entry=3D0x561a5400eed= 8, qc=3Dqc@entry=3D0x7ffcb93c4110) at ./build/../src/backend/tcop/pquery.c:788 #34 0x0000561a358d8189 in exec_simple_query (query_string=3D0x561a542fa9f0 "...INSERT INTO \"db-prod\".\"similar_items_thenlper_gte_large_recent\" ...") at ./build/../src/backend/tcop/postgres.c:1274 #35 0x0000561a358d9e2c in PostgresMain (dbname=3D, username=3D) at ./build/../src/backend/tcop/postgres.c:4770 #36 0x0000561a358d46bf in BackendMain (startup_data=3D, startup_data_len=3D) at ./build/../src/backend/tcop/backend_startup.c:124 #37 0x0000561a35835745 in postmaster_child_launch (child_type=3DB_BACKEND, child_slot=3D20, ...) at ./build/../src/backend/postmaster/launch_backend.c:290 #38 0x0000561a35839039 in BackendStartup (client_sock=3D0x7ffcb93c4630) at ./build/../src/backend/postmaster/postmaster.c:3569 #39 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1703 #40 0x0000561a3583abbd in PostmasterMain (argc=3Dargc@entry=3D5, argv=3Dargv@entry=3D0x561a53fca7f0) at ./build/../src/backend/postmaster/postmaster.c:1401 #41 0x0000561a3553bb7c in main (argc=3D5, argv=3D0x561a53fca7f0) at ./build/../src/backend/main/main.c:227 ``` Note: `buf=3D992779` in frames 17-19 is block 627 (held exclusively); `buff= er =3D 994553` in frame 13 is block 628 (the buffer being waited on, header dumped in section 7). ## 9. Backtrace of the autovacuum worker (pid 2213934) ``` #0 0x00007fb342ea5e96 in ?? () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x00007fb342eb0d90 in ?? () from /lib/x86_64-linux-gnu/libc.so.6 #2 0x0000561a3582df72 in PGSemaphoreLock (sema=3D0x7fab1e8dc838) at ./build/src/backend/port/pg_sema.c:326 #3 0x0000561a358c4c0d in LWLockAcquire (lock=3D0x7fab2866f230, mode=3DLW_SHARED) at ./build/../src/backend/storage/lmgr/lwlock.c:1299 #4 0x0000561a355a1dc9 in _bt_lockbuf (rel=3Drel@entry=3D0x561a5415a430, buf=3Dbuf@entry=3D992779, access=3Daccess@entry=3D1) at ./build/../src/backend/access/nbtree/nbtpage.c:1042 #5 0x0000561a355a732b in btvacuumpage (vstate=3Dvstate@entry=3D0x7ffcb93c3= 720, buf=3D992779) at ./build/../src/backend/access/nbtree/nbtree.c:1383 #6 0x0000561a355a7d98 in btvacuumscan (info=3Dinfo@entry=3D0x7ffcb93c39e0, stats=3Dstats@entry=3D0x561a54105450, callback=3Dcallback@entry=3D0x0, callback_state=3Dcallback_state@entry=3D0x0, cycleid=3Dcycleid@entry=3D0) at ./build/../src/backend/access/nbtree/nbtree.c:1312 #7 0x0000561a355a80a8 in btvacuumcleanup (stats=3D0x561a54105450, info=3D0x7ffcb93c39e0) at ./build/../src/backend/access/nbtree/nbtree.c:1138 #8 btvacuumcleanup (info=3D0x7ffcb93c39e0, stats=3D0x0) at ./build/../src/backend/access/nbtree/nbtree.c:1098 #9 0x0000561a356f3faf in vac_cleanup_one_index (ivinfo=3Divinfo@entry=3D0x7ffcb93c39e0, istat=3Distat@entry=3D0x0) at ./build/../src/backend/commands/vacuum.c:2674 #10 0x0000561a35598c99 in lazy_cleanup_one_index (vacrel=3D0x561a541049b0, estimated_count=3Dtrue, reltuples=3D2712141, istat=3D0x0, indrel=3D) at ./build/../src/backend/access/heap/vacuumlazy.c:3173 #11 lazy_cleanup_all_indexes (vacrel=3D0x561a541049b0) at ./build/../src/backend/access/heap/vacuumlazy.c:3061 #12 lazy_scan_heap (vacrel=3D0x561a541049b0) at ./build/../src/backend/access/heap/vacuumlazy.c:1558 #13 heap_vacuum_rel (rel=3D, params=3D, bstrategy=3D) at ./build/../src/backend/access/heap/vacuumlazy.c:839 #14 0x0000561a356f1afa in table_relation_vacuum (bstrategy=3D0x561a5418f9e8, params=3D0x7ffcb93c3f80, rel=3D0x561a54156ff0) at ./build/../src/include/access/tableam.h:1677 #15 vacuum_rel (relid=3D3371211934, relation=3D, params=3Dparams@entry=3D0x7ffcb93c3f80, bstrategy=3Dbstrategy@entry=3D0x561a5418f9e8) at ./build/../src/backend/commands/vacuum.c:2317 #16 0x0000561a356f2f4a in vacuum (relations=3D0x561a5419a7d8, params=3Dparams@entry=3D0x561a54198810, bstrategy=3Dbstrategy@entry=3D0x561a5418f9e8, vac_context=3Dvac_context@entry=3D0x561a5419a5e0, isTopLevel=3DisTopLevel@entry=3Dtrue) at ./build/../src/backend/commands/vacuum.c:649 #17 0x0000561a35831c7f in autovacuum_do_vac_analyze (bstrategy=3D0x561a5418f9e8, tab=3D0x561a54198808) at ./build/../src/backend/postmaster/autovacuum.c:3196 #18 do_autovacuum () at ./build/../src/backend/postmaster/autovacuum.c:2457 #19 0x0000561a3583224a in AutoVacWorkerMain (startup_data=3D, startup_data_len=3D) at ./build/../src/backend/postmaster/autovacuum.c:1590 #20 0x0000561a35835745 in postmaster_child_launch (child_type=3Dchild_type@entry=3DB_AUTOVAC_WORKER, child_slot=3D560, ...) at ./build/../src/backend/postmaster/launch_backend.c:290 #21 0x0000561a358377bd in StartChildProcess (type=3DB_AUTOVAC_WORKER) at ./build/../src/backend/postmaster/postmaster.c:3962 #22 0x0000561a35839ac8 in StartAutovacuumWorker () at ./build/../src/backend/postmaster/postmaster.c:4026 #23 process_pm_pmsignal () at ./build/../src/backend/postmaster/postmaster.c:3783 #24 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1696 #25 0x0000561a3583abbd in PostmasterMain (argc=3Dargc@entry=3D5, argv=3Dargv@entry=3D0x561a53fca7f0) at ./build/../src/backend/postmaster/postmaster.c:1401 #26 0x0000561a3553bb7c in main (argc=3D5, argv=3D0x561a53fca7f0) at ./build/../src/backend/main/main.c:227 ``` `buf=3D992779` is block 627, the leaf held exclusively by the INSERT. The r= ead stream feeding `btvacuumscan` had already issued block 628 (handle 17445, section 6). ## 10. Backtrace of the SELECT backend (pid 2214042), collateral ``` #0 0x00007fb342ea5e96 in ?? () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x00007fb342eb0d90 in ?? () from /lib/x86_64-linux-gnu/libc.so.6 #2 0x0000561a3582df72 in PGSemaphoreLock (sema=3D0x7fab1e8d7938) at ./build/src/backend/port/pg_sema.c:326 #3 0x0000561a358c4c0d in LWLockAcquire (lock=3D0x7fab2866f230, mode=3DLW_SHARED) at ./build/../src/backend/storage/lmgr/lwlock.c:1299 #4 0x0000561a355a1d5f in _bt_lockbuf (access=3D1, buf=3D992779, rel=3D0x561a5418f170) at ./build/../src/backend/access/nbtree/nbtpage.c:1042 #5 _bt_relandgetbuf (rel=3Drel@entry=3D0x561a5418f170, obuf=3D, blkno=3D, access=3Daccess@entry=3D1) at ./build/../src/backend/access/nbtree/nbtpage.c:1011 #6 0x0000561a355a9f68 in _bt_search (access=3D, bufP=3D, key=3D, heaprel=3D, rel=3D) at ./build/../src/backend/access/nbtree/nbtsearch.c:= 186 #7 _bt_search (rel=3D0x561a5418f170, heaprel=3D, key=3D, bufP=3D0x561a541f6808, access=3D) at ./build/../src/backend/access/nbtree/nbtsearch.c:107 #8 0x0000561a355aa791 in _bt_first (scan=3Dscan@entry=3D0x561a541f32c8, dir=3Ddir@entry=3DBackwardScanDirection) at ./build/../src/backend/access/nbtree/nbtsearch.c:1520 #9 0x0000561a355a6bcf in btgettuple (scan=3D0x561a541f32c8, dir=3DBackwardScanDirection) at ./build/../src/backend/access/nbtree/nbtree.c:245 #10 0x0000561a3559b739 in index_getnext_tid (scan=3Dscan@entry=3D0x561a541f= 32c8, direction=3Ddirection@entry=3DBackwardScanDirection) at ./build/../src/backend/access/index/indexam.c:637 #11 0x0000561a3572bfd4 in IndexOnlyNext (node=3D0x561a541d8ed0) at ./build/../src/backend/executor/nodeIndexonlyscan.c:122 #12 0x0000561a3572edc9 in ExecProcNode (node=3D0x561a541d8ed0) at ./build/../src/include/executor/executor.h:315 #13 ExecLimit (pstate=3D0x561a541d8bc0) at ./build/../src/backend/executor/nodeLimit.c:95 #14 0x0000561a3573da1d in ExecProcNode (node=3D) at ./build/../src/include/executor/executor.h:315 #15 ExecSetParamPlan (node=3D0x561a541da6d0, econtext=3Decontext@entry=3D0x561a541da230) at ./build/../src/backend/executor/nodeSubplan.c:1125 #16 0x0000561a35704c7a in ExecEvalParamExec (econtext=3D0x561a541da230, op=3D, state=3D0x561a541da5d0) at ./build/../src/backend/executor/execExprInterp.c:3064 #17 ExecInterpExpr (state=3D0x561a541da5d0, econtext=3D0x561a541da230, isnull=3D) at ./build/../src/backend/executor/execExprInterp.c:1322 #18 0x0000561a3573a1f5 in ExecEvalExprNoReturn (econtext=3D0x561a541da230, state=3D0x561a541da5d0) at ./build/../src/include/executor/executor.h:419 #19 ExecEvalExprNoReturnSwitchContext (econtext=3D0x561a541da230, state=3D0x561a541da5d0) at ./build/../src/include/executor/executor.h:460 #20 ExecProject (projInfo=3D0x561a541da5c8) at ./build/../src/include/executor/executor.h:492 #21 ExecResult (pstate=3D) at ./build/../src/backend/executor/nodeResult.c:135 #22 0x0000561a35707eda in ExecProcNode (node=3D0x561a541da128) at ./build/../src/include/executor/executor.h:315 #23 ExecutePlan (dest=3D0x561a541de868, direction=3D, numberTuples=3D0, sendTuples=3Dtrue, operation=3DCMD_SELECT, queryDesc=3D0x561a541d8508) at ./build/../src/backend/executor/execMain.c:1= 711 #24 standard_ExecutorRun (queryDesc=3D0x561a541d8508, direction=3D, count=3D0) at ./build/../src/backend/executor/execMain.c:366 #25 0x00007fb342c434e5 in pgss_ExecutorRun (queryDesc=3D0x561a541d8508, direction=3DForwardScanDirection, count=3D0) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:1036 #26 0x0000561a358dad97 in PortalRunSelect (portal=3Dportal@entry=3D0x561a540efec8, forward=3Dforward@entry=3Dtrue, co= unt=3D0, count@entry=3D9223372036854775807, dest=3Ddest@entry=3D0x561a541de868) at ./build/../src/backend/tcop/pquery.c:921 #27 0x0000561a358dc1fb in PortalRun (portal=3Dportal@entry=3D0x561a540efec8, count=3Dcount@entry=3D9223372036854775807, isTopLevel=3DisTopLevel@entry=3D= true, dest=3Ddest@entry=3D0x561a541de868, altdest=3Daltdest@entry=3D0x561a541de86= 8, qc=3Dqc@entry=3D0x7ffcb93c4110) at ./build/../src/backend/tcop/pquery.c:765 #28 0x0000561a358d8189 in exec_simple_query (query_string=3D0x561a5400d0a8 "SET LOCAL statement_timeout =3D 600000; \n SELECT\n CURRENT_TIMESTAMP,\n MAX(last_update) as last_update_timestamp,\n (CURRENT_TIMESTAMP - MAX(last_update))"...) at ./build/../src/backend/tcop/postgres.c:1274 #29 0x0000561a358d9e2c in PostgresMain (dbname=3D, username=3D) at ./build/../src/backend/tcop/postgres.c:4770 #30 0x0000561a358d46bf in BackendMain (startup_data=3D, startup_data_len=3D) at ./build/../src/backend/tcop/backend_startup.c:124 #31 0x0000561a35835745 in postmaster_child_launch (child_type=3DB_BACKEND, child_slot=3D36, ...) at ./build/../src/backend/postmaster/launch_backend.c:290 #32 0x0000561a35839039 in BackendStartup (client_sock=3D0x7ffcb93c4630) at ./build/../src/backend/postmaster/postmaster.c:3569 #33 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1703 #34 0x0000561a3583abbd in PostmasterMain (argc=3Dargc@entry=3D5, argv=3Dargv@entry=3D0x561a53fca7f0) at ./build/../src/backend/postmaster/postmaster.c:1401 #35 0x0000561a3553bb7c in main (argc=3D5, argv=3D0x561a53fca7f0) at ./build/../src/backend/main/main.c:227 ``` Same LWLock address (0x7fab2866f230) and same buffer (992779 =3D block 627)= as the autovacuum.