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
