public inbox for [email protected]
help / color / mirror / Atom feedRe: #XX000: ERROR: tuple concurrently updated
5+ messages / 3 participants
[nested] [flat]
* Re: #XX000: ERROR: tuple concurrently updated
@ 2025-02-20 15:27 Tom Lane <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2025-02-20 15:27 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: #XX000: ERROR: tuple concurrently updated
@ 2025-02-20 15:43 Dominique Devienne <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Dominique Devienne @ 2025-02-20 15:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Thu, Feb 20, 2025 at 4:27 PM Tom Lane <[email protected]> wrote:
> Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: #XX000: ERROR: tuple concurrently updated
@ 2025-02-20 16:07 Greg Sabino Mullane <[email protected]>
parent: Dominique Devienne <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2025-02-20 16:07 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
Since you are willing to break the all one transaction rule, and if the
restores were created via pg_dump, you could use the --section argument to
split things up, run the "pre-data" sections serially, and the rest ("data"
and "post-data") concurrently.
--
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: #XX000: ERROR: tuple concurrently updated
@ 2025-02-24 09:21 Dominique Devienne <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Dominique Devienne @ 2025-02-24 09:21 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Thu, Feb 20, 2025 at 4:27 PM Tom Lane <[email protected]> wrote:
> Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: #XX000: ERROR: tuple concurrently updated
@ 2025-02-24 15:01 Tom Lane <[email protected]>
parent: Dominique Devienne <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-02-24 15:01 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
Dominique Devienne <[email protected]> writes:
> 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.
Yeah, I think "GRANT role" should be relatively immune to that
problem, as long as you're making distinct grants (not same
grantor/grantee/granted roles). Wouldn't hurt to test.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-24 15:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-20 15:27 Re: #XX000: ERROR: tuple concurrently updated Tom Lane <[email protected]>
2025-02-20 15:43 ` Dominique Devienne <[email protected]>
2025-02-20 16:07 ` Greg Sabino Mullane <[email protected]>
2025-02-24 09:21 ` Dominique Devienne <[email protected]>
2025-02-24 15:01 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox