From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dominique Devienne <ddevienne@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: #XX000: ERROR: tuple concurrently updated
In-reply-to: <CAFCRh-8g-41VhdbbbnP+eezFq1UJ6T2JdQ=iS60hcaqGoJ=x9w@mail.gmail.com>
References: <CAFCRh-8g-41VhdbbbnP+eezFq1UJ6T2JdQ=iS60hcaqGoJ=x9w@mail.gmail.com>
Comments: In-reply-to Dominique Devienne <ddevienne@gmail.com>
	message dated "Thu, 20 Feb 2025 15:31:35 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2701259.1740065273.1@sss.pgh.pa.us>
Date: Thu, 20 Feb 2025 10:27:53 -0500
Message-ID: <2701260.1740065273@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2701260.1740065273%40sss.pgh.pa.us>
Precedence: bulk

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.

> 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.

			regards, tom lane