Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tl7am-009SuT-Q3 for pgsql-general@arkaria.postgresql.org; Thu, 20 Feb 2025 14:31:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tl7al-007aCM-Bs for pgsql-general@arkaria.postgresql.org; Thu, 20 Feb 2025 14:31:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tl7al-007aCD-05 for pgsql-general@lists.postgresql.org; Thu, 20 Feb 2025 14:31:55 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tl7aj-001yT2-0d for pgsql-general@lists.postgresql.org; Thu, 20 Feb 2025 14:31:54 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-7272c68780aso487731a34.3 for ; Thu, 20 Feb 2025 06:31:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740061911; x=1740666711; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Dyx4QO0WogAD+3/YtUuBQfWrv9aVcSOtiFb942dYC4E=; b=UMKk+ug348C93TrMa4tuvndKaUXkiLu22ELHgLzQwJP4JlQWLaZPfN8Cvdlsa9t8bc YtoH4S4s+pkOwc/ua12WIdpENLuHu+TgW2rDQwpPU5uaPjapeSeyg4TQ7xSxbPW+6GZG Fl6D6GJ2y2bz2y1DSdsVqzM6wMvNmvW4hywAtxp8MBMJFvPlSxDnmJylTwSWnA7y/r/p TQOhIbxG1Rn1INr0Q58Ir3OhlAEtsge8r5i+KCWao1NS68/dZ5RkkO6u3vbnxufRUvlm wX7Ki0nHi5DAA4tQ8j/7Ig2/gJQBOkmNCRNklC5APPUcJ50XAryvqTjMcsPnNF9E6ecx hSww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740061911; x=1740666711; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Dyx4QO0WogAD+3/YtUuBQfWrv9aVcSOtiFb942dYC4E=; b=Doii0I81xgLZ4C0xAog2eA+FfDSo9TdAQYZKqxIpQQtmwp0fIYFbVyK4gsjwnZG251 Mj7rB1DebmKDzCVThlGLVFxvBLw5km7LlEPi4DCuWSTUrTLhUJR2GvhArgsx4UyFfyr6 VCN3aQyxZMDbJUMIagy+RIEKmS3OQg126UEbzecj4U+R856MsrXn9XONunixXV2bRlpv 8UV3BwSt+2Qsq/08eu5UG1vRJ3YEXR2p3wbxTk/C1W1SYCdIuoFpzHL8TK6Wk/ZF4V9Y 63JCCmsspOXM2VpQf1DTUCfvQFZi+DTZHdF3k1uziM8tNQRuFexVBijYxCRmFyvqvrnz xFCA== X-Gm-Message-State: AOJu0YySOM6JItOczmuYDdgggNH1fT+H+WfT9aLLbQskp/2QiQcr6PVO 0TZBAEoYjLDSNs54gvqwDRbquDHW/ce31HfWqQzAnkfv6k0s8jIJqVXbOORgpabkpUXAqA2YhT+ oeKF0WGLhOT2Dm2jNpWsD4eEz9iqdovff X-Gm-Gg: ASbGncv4xZ5hwzUt43FxcAfzWvfoi50WndCc2seMnc4cP4e3itRDJ0l47cdl+siUn0u o4KFR5dSydcuLyTa3ZhYHMEWGlya0tQqLMZM9Gm3RzcfiYsFUBvRTq0SR6nHAiOCJp5NxYkuJaw I= X-Google-Smtp-Source: AGHT+IH6LgUH4m7PK28uDonjL+XQ1YxjPQ9LBbCsXpXgdRIWtbGy3SshzmPZbja+tOABiosgc6+Xt2Fs3HBotPZvsuI= X-Received: by 2002:a05:6830:730f:b0:727:3001:7f7d with SMTP id 46e09a7af769-727437998a0mr1634431a34.4.1740061911521; Thu, 20 Feb 2025 06:31:51 -0800 (PST) MIME-Version: 1.0 From: Dominique Devienne Date: Thu, 20 Feb 2025 15:31:35 +0100 X-Gm-Features: AWEUYZk20PE8pCI2tlC3sRJvZcQ-NE3BhGcIDSgPL9hjD4_1wVdl4F7T2CjfmfE Message-ID: Subject: #XX000: ERROR: tuple concurrently updated To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001fa25c062e93bc4e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001fa25c062e93bc4e Content-Type: text/plain; charset="UTF-8" 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 --0000000000001fa25c062e93bc4e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 th= e data.

The two restores are independent, i.e. cre= ate different non-overlapping schemas.
But both are "registe= red" in the same pre-existing schema, SCH1.
And both run wit= hin a single transaction, that does everything, DDLs and DMLs.
The first session was doing a longer bigger restore, and was s= tarted 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 "S= CH2:RO", "SCH2:RW", "SCH2:SU": #XX000: ERROR:=C2= =A0 tuple concurrently updated
=C2=A0
Thus I'= ;m trying to understand what's going on.
As noted above, SCH1 is the pre-existing schema, while SCH2 is the ne= w one created by the restore.

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

=
When I researc= h #XX000, it often= mentions doing a SELECT FOR UPDATE, but here I'm doing GRANTs, so I do= n't think that applies.

Is the issue related to trying to change SCHEMA ACLs for S= CH1 concurrently, in two long running transactions? How am I supposed to re= solve this?

I have to confess being a little surprised and blindsided by this issue.
Help unde= rstanding what's going on, and how to fix it, would be greatly apprecia= ted.
Thanks= , --DD
--0000000000001fa25c062e93bc4e--