Message-ID: <fb4028b6-e07f-4d84-a65e-c90bc96e6356@gmail.com>
Date: Tue, 31 Mar 2026 20:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP
 DATABASE
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, =?UTF-8?B?SXdhdGEsIEF5YS/lsqnnlLAg5b2p?=
 <iwata.aya@fujitsu.com>, Peter Smith <smithpb2250@gmail.com>,
 =?UTF-8?B?S3Vyb2RhLCBIYXlhdG8v6buS55SwIOmavOS6ug==?=
 <kuroda.hayato@fujitsu.com>, Pavel Stehule <pavel.stehule@gmail.com>,
 Chao Li <li.evan.chao@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <abev_E-SE94zHP0n@paquier.xyz> <abiUQ5MBGG9MDM1h@paquier.xyz>
 <TYRPR01MB12156CA129F54361D19ECAB56F541A@TYRPR01MB12156.jpnprd01.prod.outlook.com>
 <abjRR1bhoa9N8XEa@paquier.xyz>
 <a99bd177-00e3-4512-ba7c-29e180926467@gmail.com>
 <abkR1aod9_dG-PGM@paquier.xyz> <1020519.1773863522@sss.pgh.pa.us>
 <abtJLEAsf1HZXWdR@paquier.xyz> <ab4Gf2r-Bd6P7BiW@paquier.xyz>
 <f913fba1-da59-404c-9eb3-07c7304be637@gmail.com>
 <acun5kN_JaLFcfq_@paquier.xyz>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <acun5kN_JaLFcfq_@paquier.xyz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/fb4028b6-e07f-4d84-a65e-c90bc96e6356%40gmail.com>
Precedence: bulk

31.03.2026 13:54, Michael Paquier wrote:
> On Tue, Mar 31, 2026 at 10:00:00AM +0300, Alexander Lakhin wrote:
>> So the backend is not completely stuck, but CommitTransactionCommand()
>> may take more than 5 seconds under some circumstances (maybe it's worth
>> investigating which exactly).
> One could blame slow hardware, difficult to say, and I'm puzzled by
> these periodic bumps that don't seem to happen elsewhere.

I managed to get the backtrace of such a sluggish backend:
Using host libthread_db library "/lib/riscv64-linux-gnu/libthread_db.so.1".
0x0000003fb1f4cc26 in posix_fadvise64 () from /lib/riscv64-linux-gnu/libc.so.6
   Id   Target Id                                   Frame
* 1    Thread 0x3fb2a4c620 (LWP 564194) "postgres" 0x0000003fb1f4cc26 in posix_fadvise64 () from 
/lib/riscv64-linux-gnu/libc.so.6
#0  0x0000003fb1f4cc26 in posix_fadvise64 () from /lib/riscv64-linux-gnu/libc.so.6
#1  0x0000002abef79444 in XLogFileClose () at xlog.c:3672
#2  0x0000002abef7cc66 in XLogWrite (WriteRqst=..., tli=tli@entry=1, flexible=flexible@entry=false) at xlog.c:2356
#3  0x0000002abef7dbfc in XLogFlush (record=33561688) at xlog.c:2892
#4  0x0000002abef77976 in RecordTransactionCommit () at xact.c:1516
#5  CommitTransaction () at xact.c:2379
#6  0x0000002abef78938 in CommitTransactionCommandInternal () at xact.c:3224
#7  0x0000002abef78acc in CommitTransactionCommand () at xact.c:3185
#8  0x0000003fb2a3ed88 in initialize_worker_spi (table=0x2abf8bf358) at worker_spi.c:132
#9  worker_spi_main (main_arg=<optimized out>) at worker_spi.c:181
....
(Three test runs produced the same stack trace.)

I think this can explain slow CommitTransactionCommand() and why it
happens not every time. Regarding other animals, I guess they can
experience the same bumps but not exceeding 5 seconds (50 tries). Thus,
from my understanding, for the failure to happen, we need to have slow
storage and initialize_worker_spi() -> CommitTransactionCommand() reaching
XLogFileClose().

Best regards,
Alexander