MIME-Version: 1.0
References: <202512151349.vlq3mpfniyk3@alvherre.pgsql> <11247.1767609087@localhost>
 <11558.1767609632@localhost> <141054.1767891540@localhost>
 <CADzfLwU-OmxW3t3AoQo9=K7uq4G1yZ-txcetzW3jbcVxV_pJew@mail.gmail.com>
 <137668.1768235610@localhost> <CADzfLwUJSHKGxYw+vMUZ_Hr2YeuxO2Q5w13HKgUUN1725tjY5Q@mail.gmail.com>
 <CADzfLwU78as45To9a=-Qkr5jEg3tMxc5rUtdKy2MTv4r_SDGng@mail.gmail.com> <35686.1768495019@localhost>
In-Reply-To: <35686.1768495019@localhost>
From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Thu, 15 Jan 2026 20:05:02 +0300
Message-ID: <CADzfLwWQRAqi1EQrKFiTgXJsdz_3ZFAWkto97hsYb-bMcpwuwg@mail.gmail.com>
Subject: Re: Adding REPACK [concurrently]
To: Antonin Houska <ah@cybertec.at>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Content-Type: multipart/alternative; boundary="0000000000006f35630648703aac"
Archived-At: <https://www.postgresql.org/message-id/CADzfLwWQRAqi1EQrKFiTgXJsdz_3ZFAWkto97hsYb-bMcpwuwg%40mail.gmail.com>
Precedence: bulk

--0000000000006f35630648703aac
Content-Type: text/plain; charset="UTF-8"

Hello!

Antonin Houska <ah@cybertec.at>:

As the test runs pgbench with --client=30 and the default value of
max_worker_processes is 8, I'm not sure this is a leak. I've increased this
parameter I couldn't see the error anymore.


Hm, as far as I remember only single repack may be executed in test
(because of locking on test itself and also REPACK).

At least still feel suspicious.


I agree that this is due to the missing MVCC safety feature. I commented
that
check in the script for now.


I don't think so. In case of non-MVCC safety we should see 0 or correct
sum. But script failed with 490588...

But should see 500500 (if I correctly calculated sum of numbers from 1 to
1000)...

Besides that, I saw some deadlocks. I think this was due to the fact that
multiple rows are updated per transaction, and that the keys are random, so
it
can happen that two transactions try to update the same rows in different
order. I increased the number of rows in the test table to 10000 and don't
see
the deadlocks anymore.


I think better to use min/max in updates to be sure (update lower id first).

This is tricky. I could reproduce the problem on my FreeBSD box a few times,
never on Linux (no idea if the OS makes the difference since HW is also
quite
different, but CI also seemed to fail more often on FreeBSD.)


You may try to play with no_hot parameter in test - maybe it will provide
some clue.

Best regards,
Mikhail.

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

<div dir=3D"auto"><div dir=3D"auto">Hello!<br><br><div style=3D"min-width:1=
50px" class=3D"elided-text"><div dir=3D"ltr">Antonin Houska &lt;<a href=3D"=
mailto:ah@cybertec.at">ah@cybertec.at</a>&gt;:</div><blockquote style=3D"mi=
n-width:150px;margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">As the test runs pgbench with --client=3D30 and the de=
fault value of<br>max_worker_processes is 8, I&#39;m not sure this is a lea=
k. I&#39;ve increased this<br>parameter I couldn&#39;t see the error anymor=
e.<br></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"auto=
">Hm, as far as I remember only single repack may be executed in test (beca=
use of locking on test itself and also REPACK).</div><div dir=3D"auto"><br>=
</div><div dir=3D"auto">At least still feel suspicious.</div><div dir=3D"au=
to"><br></div><div dir=3D"auto"><br></div><div dir=3D"auto"><div style=3D"m=
in-width:150px" class=3D"elided-text"><blockquote style=3D"min-width:150px;=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex">I agree that this is due to the missing MVCC safety feature. I comme=
nted that<br>check in the script for now.<br></blockquote></div></div><div =
dir=3D"auto"><br></div><div dir=3D"auto"><div style=3D"min-width:150px" cla=
ss=3D"elided-text"><blockquote style=3D"min-width:150px;margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"></blockquote=
></div></div><div dir=3D"auto">I don&#39;t think so. In case of non-MVCC sa=
fety we should see 0 or correct sum. But script failed with 490588...</div>=
<div dir=3D"auto"><br></div><div dir=3D"auto">But should see 500500 (if I c=
orrectly calculated sum of numbers from 1 to 1000)...</div><div dir=3D"auto=
"><br></div><div dir=3D"auto"><div style=3D"min-width:150px" class=3D"elide=
d-text"><blockquote style=3D"min-width:150px;margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">Besides that, I saw som=
e deadlocks. I think this was due to the fact that<br>multiple rows are upd=
ated per transaction, and that the keys are random, so it<br>can happen tha=
t two transactions try to update the same rows in different<br>order. I inc=
reased the number of rows in the test table to 10000 and don&#39;t see<br>t=
he deadlocks anymore.<br></blockquote></div></div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto">I think better to use min/max in updates to be sure (u=
pdate lower id first).</div><div dir=3D"auto"><br></div><div dir=3D"auto"><=
div style=3D"min-width:150px" class=3D"elided-text"><blockquote style=3D"mi=
n-width:150px;margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">This is tricky. I could reproduce the problem on my Fr=
eeBSD box a few times,<br>never on Linux (no idea if the OS makes the diffe=
rence since HW is also quite<br>different, but CI also seemed to fail more =
often on FreeBSD.)<br></blockquote></div></div><div dir=3D"auto"><br></div>=
<div dir=3D"auto">You may try to play with no_hot parameter in test - maybe=
 it will provide some clue.</div><div dir=3D"auto"><br></div><div dir=3D"au=
to">Best regards,</div><div dir=3D"auto">Mikhail.</div></div>

--0000000000006f35630648703aac--