MIME-Version: 1.0
References: 
 <CAFj8pRB_FH-Pcth9XFcpY2OTasVOP-2DfYOsVxL38igw0O4hdg@mail.gmail.com>
 <OS7PR01MB119647141272DBF9364A1C5AAEAADA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRCRnN4SyZDPbQwTyKZ_kVHfwLG6udCXsXDTG_z9fWwYQg@mail.gmail.com>
 <OS7PR01MB119648203BD748ED008FA5BF5EAABA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRCn0=jn4yaeg1JoxxxnUNeXm1KCouES8Puq_GgBsXrNTQ@mail.gmail.com>
 <OS7PR01MB1196405302B700736580DEE49EAA8A@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRBk7zTKoxAAKAihNQuimztniWHPyRWHKp2iHq_2hH+dvQ@mail.gmail.com>
 <OS7PR01MB11964C83E8E543743581250B0EAB3A@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <TYCPR01MB1131647E781856780072FBAE9E8B0A@TYCPR01MB11316.jpnprd01.prod.outlook.com>
 <CAFj8pRBtApX5WGMOzz4+_xe3ZvBLc6BsrUV13KTeNpAL6ZxFzA@mail.gmail.com>
 <aVHQt-asgKx5dnap@paquier.xyz>
In-Reply-To: <aVHQt-asgKx5dnap@paquier.xyz>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 29 Dec 2025 06:31:11 +0100
Message-ID: 
 <CAFj8pRAc7a+KsLARqpK8mSqdY-Ats8iJCNtWONkprfDeNt_0zg@mail.gmail.com>
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP
 DATABASE
To: Michael Paquier <michael@paquier.xyz>
Cc: "Ryo Matsumura (Fujitsu)" <matsumura.ryo@fujitsu.com>,
	"Aya Iwata (Fujitsu)" <iwata.aya@fujitsu.com>,
 Peter Smith <smithpb2250@gmail.com>,
	Chao Li <li.evan.chao@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000040e31e0647108f92"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRAc7a%2BKsLARqpK8mSqdY-Ats8iJCNtWONkprfDeNt_0zg%40mail.gmail.com>
Precedence: bulk

--00000000000040e31e0647108f92
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

po 29. 12. 2025 v 1:52 odes=C3=ADlatel Michael Paquier <michael@paquier.xyz=
>
napsal:

> On Fri, Dec 26, 2025 at 12:04:47PM +0100, Pavel Stehule wrote:
> > I am not sure if I understand what you prefer.
>
> Seems to me that Iwata-san sides with compatibility.  Spoiler alert: I
> do side with compatibility.  See below for more details.
>
> > 1. When bgworker is marked as INTERRUPTIBLE, then can be cancelled
> > immediately - there is full agreement - I don't see any problem - just
> > probably almost all bgworkers will be not be marked as INTERRUPTIBLE
>
> Yes, these can and should be stopped.
>
> > 2.  When bgworker is not marked as INTERRUPTIBLE, then can it be
> cancelled?
> > How dangerous is this? If it cannot be cancelled, then ALTER should wai=
t
> on
> > lock forever, or there should be some special timeout - like CREATE, DR=
OP
> > DATABASE and waiting on template databases.
>
> Cancellation is a different thing.  bgworkers can be tweaked to answer
> to specific signals with their own handlers.  What we are discussing
> here is if a signal should be sent to a bgworker or not when
> performing specific operations.
>
> > 3. If the user needs to execute ALTER, then probably he manually
> terminates
> > the worker any time. Are currently used workers safe against terminatin=
g?
> > We know so mostly workers will be restarted after timeout - and then it
> is
> > "safe".  It is really safe? Can we expect it? If it is safe, then we ca=
n
> > implement FORCE clause. Probably any worker can be restarted - and
> without
> > safety it is hard to imagine using in the production.
>
> That would be up to an extension developer.  The new behavior can be
> useful in some cases.  Forcing it by default could lead to unwanted
> results.
>
> > I think there is a fundamental question that should be solved before -
> what
> > is a risk of canceling bgworker? The possible reply is so there is not
> any
> > risk for correctly implemented bgworkers. And then there is a question =
if
> > we still need the flag INTERRUPTIBLE? My opinion for this case is
> probably
> > yes, because cancelling can introduce some bigger latency.
>
> I can think about two reasons at the top of my mind, at least:
> 1) Making the background workers non-interruptible is the default
> behavior that we have since 9.3.  Changing the default by allowin
> these to be interrupted could lead to a silent behavior change that
> extension developers are not aware of because the bgworker lubrary
> code would still be compiled as-is, and would still be able to start
> correctly.  This links to my second point.
> 2) Operator error, and these tend to happen a lot.  Somebody with the
> right to create a database could decide to use as template a database
> that a bgworker is linked to, leading to failures with operations
> background workers expect to be able to achieve while online if we
> change the default, because it does things that are critical and
> should not be interrupted (one could compare that to what an
> autovacuum worker does with an antiwraparound work, perhaps, which
> cannot be interrupted).
>
> As a whole, I think that we have more advantages in keeping the
> default, making the possibility to make a bgworker interruptible an
> opt-in choice is extra cream that can be added on top of a cake, and
> one is free to add the extra cream to their portion of the cake.
>

ok

Regards

Pavel


> --
> Michael
>

--00000000000040e31e0647108f92
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">po 29. 12. 2025 v=C2=A01:52 o=
des=C3=ADlatel Michael Paquier &lt;<a href=3D"mailto:michael@paquier.xyz">m=
ichael@paquier.xyz</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex">On Fri, Dec 26, 2025 at 12:04:47PM +0100, Pavel Stehule =
wrote:<br>
&gt; I am not sure if I understand what you prefer.<br>
<br>
Seems to me that Iwata-san sides with compatibility.=C2=A0 Spoiler alert: I=
<br>
do side with compatibility.=C2=A0 See below for more details.<br>
<br>
&gt; 1. When bgworker is marked as INTERRUPTIBLE, then can be cancelled<br>
&gt; immediately - there is full agreement - I don&#39;t see any problem - =
just<br>
&gt; probably almost all bgworkers will be not be marked as INTERRUPTIBLE<b=
r>
<br>
Yes, these can and should be stopped.<br>
<br>
&gt; 2.=C2=A0 When bgworker is not marked as INTERRUPTIBLE, then can it be =
cancelled?<br>
&gt; How dangerous is this? If it cannot be cancelled, then ALTER should wa=
it on<br>
&gt; lock forever, or there should be some special timeout - like CREATE, D=
ROP<br>
&gt; DATABASE and waiting on template databases.<br>
<br>
Cancellation is a different thing.=C2=A0 bgworkers can be tweaked to answer=
<br>
to specific signals with their own handlers.=C2=A0 What we are discussing<b=
r>
here is if a signal should be sent to a bgworker or not when<br>
performing specific operations.<br>
<br>
&gt; 3. If the user needs to execute ALTER, then probably he manually termi=
nates<br>
&gt; the worker any time. Are currently used workers safe against terminati=
ng?<br>
&gt; We know so mostly workers will be restarted after timeout - and then i=
t is<br>
&gt; &quot;safe&quot;.=C2=A0 It is really safe? Can we expect it? If it is =
safe, then we can<br>
&gt; implement FORCE clause. Probably any worker can be restarted - and wit=
hout<br>
&gt; safety it is hard to imagine using in the production.<br>
<br>
That would be up to an extension developer.=C2=A0 The new behavior can be<b=
r>
useful in some cases.=C2=A0 Forcing it by default could lead to unwanted<br=
>
results.<br>
<br>
&gt; I think there is a fundamental question that should be solved before -=
 what<br>
&gt; is a risk of canceling bgworker? The possible reply is so there is not=
 any<br>
&gt; risk for correctly implemented bgworkers. And then there is a question=
 if<br>
&gt; we still need the flag INTERRUPTIBLE? My opinion for this case is prob=
ably<br>
&gt; yes, because cancelling can introduce some bigger latency.<br>
<br>
I can think about two reasons at the top of my mind, at least:<br>
1) Making the background workers non-interruptible is the default<br>
behavior that we have since 9.3.=C2=A0 Changing the default by allowin<br>
these to be interrupted could lead to a silent behavior change that<br>
extension developers are not aware of because the bgworker lubrary<br>
code would still be compiled as-is, and would still be able to start<br>
correctly.=C2=A0 This links to my second point.<br>
2) Operator error, and these tend to happen a lot.=C2=A0 Somebody with the<=
br>
right to create a database could decide to use as template a database<br>
that a bgworker is linked to, leading to failures with operations<br>
background workers expect to be able to achieve while online if we<br>
change the default, because it does things that are critical and<br>
should not be interrupted (one could compare that to what an<br>
autovacuum worker does with an antiwraparound work, perhaps, which<br>
cannot be interrupted).<br>
<br>
As a whole, I think that we have more advantages in keeping the<br>
default, making the possibility to make a bgworker interruptible an<br>
opt-in choice is extra cream that can be added on top of a cake, and<br>
one is free to add the extra cream to their portion of the cake.<br></block=
quote><div><br></div><div>ok</div><div><br></div><div>Regards</div><div><br=
></div><div>Pavel</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">
--<br>
Michael<br>
</blockquote></div></div>

--00000000000040e31e0647108f92--