#XX000: ERROR: tuple concurrently updated

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: #XX000: ERROR: tuple concurrently updated
Date: 2025-02-20 14:31:35
Message-ID: CAFCRh-8g-41VhdbbbnP+eezFq1UJ6T2JdQ=iS60hcaqGoJ=x9w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. A tester just tried to restore two custom backups (not official
PostgreSQL ones) concurrently.
The restore process creates a new schema, some roles,and does some
role-to-role grants, before loading the data.

The two restores are independent, i.e. create different non-overlapping
schemas.
But both are "registered" in the same pre-existing schema, SCH1.
And both run within a single transaction, that does everything, DDLs and
DMLs.

The first session was doing a longer bigger restore, and was started first.
While the second session was doing a faster shorter one, and started a few
seconds later, 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.
As noted above, SCH1 is the pre-existing schema, while SCH2 is the new one
created by the restore.

The second session that completes normally did a `GRANT USAGE ON SCHEMA
"SCH1", "SCH3" TO "SCH3:RO", "SCH3:RW", "SCH3:SU"` within its own
transaction.

When I research #XX000, it often mentions doing a SELECT FOR UPDATE, but
here I'm doing GRANTs, so I don't think that applies.

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?

I have to confess being a little surprised and blindsided by this issue.
Help understanding what's going on, and how to fix it, would be greatly
appreciated.

Thanks, --DD

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dominique Devienne 2025-02-20 14:33:57 Re: v18 virtual columns
Previous Message Ron Johnson 2025-02-20 14:30:53 Re: v18 virtual columns