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: Mon, 24 Feb 2025 10:21:50 +0100
Message-ID: <CAFCRh-957e6i9NWXo9iNCS10AGKjSRqZ_CoCP+-67s82Rd8Bjw@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="000000000000b384df062edfdfee"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-957e6i9NWXo9iNCS10AGKjSRqZ_CoCP%2B-67s82Rd8Bjw%40mail.gmail.com>
Precedence: bulk

--000000000000b384df062edfdfee
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:
> > Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
> > "SCH2:RW", "SCH2:SU": #XX000: ERROR:  tuple concurrently updated
>
> 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.
>

Hi Tom, and al.

I have a related question, on role-to-role grants this time.

Above, it was contention on pg_namespace.nspacl in two transactions.

But during those "restore" transactions, I must also make role-to-role
grants,
which AFAIK involve adding rows to pg_auth_members. So they are not subject
to the same "no support for concurrent updates in the catalog-manipulation"
you mentioned, as schema-to-role grants are, right? Because that's an
insert,
not an update? Just want to make sure, as I'm thinking how to change our
code.

Thanks, --DD

--000000000000b384df062edfdfee
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; Error: DDL Error: GRANT USAGE ON SCHEMA &quot;SCH1&quot;, &quot;S=
CH2&quot; 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>
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>Hi Tom, and al.</div><div><br></div><div>I have a r=
elated question, on role-to-role grants this time.</div><div><br></div><div=
>Above, it was contention on pg_namespace.nspacl in two transactions.</div>=
<div><br></div><div>But during those &quot;restore&quot; transactions, I mu=
st also make role-to-role grants,</div><div>which AFAIK involve adding rows=
 to pg_auth_members. So they are not subject</div><div>to the same &quot;no=
 support for concurrent updates in the catalog-manipulation&quot;</div><div=
>you mentioned, as schema-to-role grants are, right? Because that&#39;s an =
insert,</div><div>not an update? Just want to make sure, as I&#39;m thinkin=
g how to change our code.</div><div><br></div><div>Thanks, --DD</div></div>=
</div>

--000000000000b384df062edfdfee--