MIME-Version: 1.0
References: <CAFCRh-8g-41VhdbbbnP+eezFq1UJ6T2JdQ=iS60hcaqGoJ=x9w@mail.gmail.com>
 <2701260.1740065273@sss.pgh.pa.us>
In-Reply-To: <2701260.1740065273@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 20 Feb 2025 16:43:12 +0100
Message-ID: <CAFCRh-8YN2GUkpo8PkdouGf55FzzkjAhV8t-gj0i5zfbz8i_fg@mail.gmail.com>
Subject: Re: #XX000: ERROR: tuple concurrently updated
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000384b56062e94bc40"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8YN2GUkpo8PkdouGf55FzzkjAhV8t-gj0i5zfbz8i_fg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Feb 20, 2025 at 4:27=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Dominique Devienne <ddevienne@gmail.com> writes:
> > Hi. A tester just tried to restore two custom backups (not official
> > PostgreSQL ones) concurrently.
> > ...
> > The second session completed OK.
> > But the first session errors out with:
>
> > Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
> > "SCH2:RW", "SCH2:SU": #XX000: ERROR:  tuple concurrently updated
>
> > Thus I'm trying to understand what's going on.
>
> Since both restores tried to grant some permissions on SCH1, they
> both had to update SCH1's pg_namespace row (specifically nspacl).
> We have no support for concurrent updates in the catalog-manipulation
> code, so if the second run arrives at that step before the first
> one has committed its pg_namespace change, you get this error.
>

Thanks for confirming Tom.


> > Is the issue related to trying to change SCHEMA ACLs for SCH1
> concurrently,
> > in two long running transactions? How am I supposed to resolve this?
>
> The window is probably too small to hit if each restore is committing
> as it goes, but if you run in --single-transaction mode then this
> isn't surprising.  I'd say don't try to run concurrent restores.
>

First, I'm not in psql, but my own code. And as mentioned, I'm already in
the equivalent,
doing everything (DDLs and DMLs) in a single transaction.

Second, not doing concurrent restores is not an option.

So I need to separate operations related to SCH2,3,..., which are all
independent,
and SCH1 which is shared, as separate long running and short transactions,
respectively.
With perhaps some retry logic on the SCH1, just in case. --DD

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Feb 20, 2025 at 4:27=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Dominique Devienne &lt;<a href=3D"mailt=
o:ddevienne@gmail.com" target=3D"_blank">ddevienne@gmail.com</a>&gt; writes=
:<br>
&gt; Hi. A tester just tried to restore two custom backups (not official<br=
>
&gt; PostgreSQL ones) concurrently.<br>
&gt; ...<br>
&gt; The second session completed OK.<br>
&gt; But the first session errors out with:<br>
<br>
&gt; Error: DDL Error: GRANT USAGE ON SCHEMA &quot;SCH1&quot;, &quot;SCH2&q=
uot; TO &quot;SCH2:RO&quot;,<br>
&gt; &quot;SCH2:RW&quot;, &quot;SCH2:SU&quot;: #XX000: ERROR:=C2=A0 tuple c=
oncurrently updated<br>
<br>
&gt; Thus I&#39;m trying to understand what&#39;s going on.<br>
<br>
Since both restores tried to grant some permissions on SCH1, they<br>
both had to update SCH1&#39;s pg_namespace row (specifically nspacl).<br>
We have no support for concurrent updates in the catalog-manipulation<br>
code, so if the second run arrives at that step before the first<br>
one has committed its pg_namespace change, you get this error.<br></blockqu=
ote><div><br></div><div>Thanks for confirming Tom.</div><div>=C2=A0</div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex">
&gt; Is the issue related to trying to change SCHEMA ACLs for SCH1 concurre=
ntly,<br>
&gt; in two long running transactions? How am I supposed to resolve this?<b=
r>
<br>
The window is probably too small to hit if each restore is committing<br>
as it goes, but if you run in --single-transaction mode then this<br>
isn&#39;t surprising.=C2=A0 I&#39;d say don&#39;t try to run concurrent res=
tores.<br></blockquote><div><br></div><div>First, I&#39;m not in psql, but =
my own code. And as mentioned, I&#39;m already in the equivalent,</div><div=
>doing everything (DDLs and DMLs) in a single transaction.</div><div><br></=
div><div>Second, not doing concurrent restores is not an option.</div><div>=
<br></div><div>So I need to separate operations related to SCH2,3,..., whic=
h are all independent,</div><div>and SCH1 which is shared, as separate long=
 running and short transactions, respectively.</div><div>With perhaps some =
retry logic on the SCH1, just in case. --DD</div></div></div>

--000000000000384b56062e94bc40--