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 1tVw3s-00Cm9y-F5 for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 17:11:12 +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 1tVw3r-0052WJ-Mh for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 17:11:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVw3r-0052WA-8F for pgsql-general@lists.postgresql.org; Thu, 09 Jan 2025 17:11:11 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVw3l-000jzF-0n for pgsql-general@postgresql.org; Thu, 09 Jan 2025 17:11:10 +0000 Received: by mail-oo1-xc31.google.com with SMTP id 006d021491bc7-5f6ffbfb338so195543eaf.3 for ; Thu, 09 Jan 2025 09:11:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736442664; x=1737047464; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=qCQb6HbvcD16wO89vdlwqnuzeo02N804qW9GWXW0MoY=; b=J3JS0zrrMkji5S7J/nUeJKloKPHUSEAfqlpyHSJWAjCUWHBFGWkjCWEtwOxgE52EAX lkOLqrVR01695bO9S7dee6ijn74mELHq8yHMX1wHN+nO1j1SuN9333RBCo1Dc5qscdD8 d7JqCIPbmbGizw8vJwOiG4pPO/8XJEr611bV4OU0Rr9QOar5xVSNeEfBCHXfW693Xz5b yfGzyesfG0GqcBpeKTypcl8dKjbZu1krjrBPVMBL8N5SHOiL10cWTlp9DlwLCFVJ47lD D6fyqKZiCV8a4BPzIT92EoXIC4af1o5C/3SMXzI/9Nf91x9esplQArqc9KInJQ5SWBvp yI4A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736442664; x=1737047464; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=qCQb6HbvcD16wO89vdlwqnuzeo02N804qW9GWXW0MoY=; b=QlLGSNPzvwwfV3RnSAipucoaS/szBKpWXE+17GuTHjocXM9u/BGlHvpCbJGuy2OXoL T2ZG0Na1hhY/VglpLImjTjuU94fJJ3HSP/g42cft5zxO7Il65YIg2nzV70IxI7AmY4nF trPtNs4rYCrur2QUfQCSlWYvoJPyiJ7UoFgSWdRICFNqLlGfXQAQegumybItY8BNzjBy 5FhNhhKcZlsn7TGS1NFm2SAiIMW4QVMeZkSUSqmDQoU8o2RYRCpzXjQgRFi2Vze0W6Ld xFxzAPF9GL69cDaNjG83Hn3qlPNFMOCygwh/baJJhCwJscep/SNy8itPBJCVyBtkpz8Z DA7A== X-Gm-Message-State: AOJu0Yxr7EFJP3liemOktTH8YV7i3P9feqBzA8on5VcX1I7j4bNHThSj RFw0VugtfyqHktNd/94LX8utgs25kaYV4ImRaHgvBzfiCEGkgS4FdqVkFNuICOLn6Jt4PftwBw1 7dBDB5UvddFoHKmuAWkiPgrnCAoXOPg== X-Gm-Gg: ASbGncum2e+vsA6+aOde3tZ257KVS4ILRyeJkeoniobVrLJOXja3BGzF2wRH2pRBa6O IK8ftnHlo6r7uFlCXwW9jCGxxbPQ3IbWD0aFTcFvOMOkxffAVjJ2yTLhpkTQWuKHWdXhv+M1V X-Google-Smtp-Source: AGHT+IG1BWwIy0YDzyq1f4VJehkf95nmX9jkAO6XNyNzRvR4ssptYxfz9k0PMbQiEqF0/NAvnvA+YcgdYV2nutNBBu8= X-Received: by 2002:a05:6870:2f0b:b0:29f:ed90:8d11 with SMTP id 586e51a60fabf-2aa069a90bbmr3988868fac.29.1736442663013; Thu, 09 Jan 2025 09:11:03 -0800 (PST) MIME-Version: 1.0 References: <86acb9be-a695-4902-b612-e2fb15339982@aklaver.com> In-Reply-To: From: Ron Johnson Date: Thu, 9 Jan 2025 12:10:51 -0500 X-Gm-Features: AbW1kvbzUTSv9WohmvQ8lSOFrlyT8drcRoloVBGDKpFFzgKPyPW9pURjCJdZGS4 Message-ID: Subject: Re: recovery error while running any statement To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000001a1421062b49101e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001a1421062b49101e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jan 9, 2025 at 12:01=E2=80=AFPM yudhi s wrote: > > On Thu, Jan 9, 2025 at 10:21=E2=80=AFPM Adrian Klaver > wrote: > >> On 1/9/25 08:42, yudhi s wrote: >> > Hello Experts, >> > It's postgres aurora version 16. While running the ALTER command on >> any >> > object we see an error "/Only RowExclusiveLock or less can be acquired >> > on database objects during recovery/". If I run any DML it gives an >> > error stating '/cannot execute UPDATE in a read-only transaction/' , >> > then I tried setting "/set transaction read-write/" and it erroring ou= t >> > with "/cannot set transaction read-write mode during recovery/". >> > >> > Want to understand , what is the cause of this error and how to fix >> this? >> >> Per: >> >> >> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Auro= raPostgreSQL.html >> >> Aurora Postgres is not the community edition, you should probably take >> this up with AWS support. >> >> >> Sure will raise a ticket. I was trying to understand though , if it's > possible in postgres to have these sudden "recovery errors" possible > because of some long running DML/DDL killed unexpectedly using the > "pg_terminate/pg_cancel" command? And if this error appears in community > postgres and if it stays for a long time what we used to do? > Are you *positive* that you're connected to the *primary* node? Because this is exactly the kind of error you'll see when connected to the *secondary* node. Or maybe, for some reason, AWS failed you over to the secondary node, making it now the primary node, while what you think is the primary node is really the secondary node. That's how "real" Postgresql acts, at least. Not how AWS RDS Postgresql acts (it's all hidden behind a virtual IP that you always connect to), but I don't know anything about Aurora. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001a1421062b49101e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jan 9, 2025 at 12:01=E2=80=AFPM y= udhi s <learnerdatabase99= @gmail.com> wrote:

On Thu, Jan 9, 2025 at 10:21=E2=80=AFPM Adrian Klaver <<= a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klaver= @aklaver.com> wrote:
On 1/9/25 08:42, yudhi s wrote:
> Hello Experts,
> It's postgres aurora version 16. While=C2=A0 running the ALTER com= mand on any
> object we see an error "/Only RowExclusiveLock or less can be acq= uired
> on database objects during recovery/". If I run any DML it gives = an
> error stating '/cannot execute UPDATE in a read-only transaction/&= #39; ,
> then I tried setting "/set transaction read-write/" and it e= rroring out
> with "/cannot set transaction read-write mode during recovery/&qu= ot;.
>
> Want to understand=C2=A0, what is the cause of this error and how to f= ix this?

Per:

https://doc= s.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.h= tml

Aurora Postgres is not the community edition, you should probably take
this up with AWS support.


Sure will raise a ticket. I was trying to underst= and=C2=A0though=C2=A0, if it's possible in postgres to have these sudde= n "recovery errors" possible because of some long running DML/DDL= killed unexpectedly using the "pg_terminate/pg_cancel" command? = And if this error appears in community postgres and if it stays for a long = time what we=C2=A0used to do?=C2=A0

=
Are you positive=C2=A0that you're connected to the primary=C2=A0node?=C2=A0 Because this is exactly the kind of error you= 'll see when connected to the secondary=C2=A0node.
Or maybe, for some reason, AWS failed you over to the secondary= node, making it now the primary node, while what you think is the primary = node is really the secondary node.

That's how = "real" Postgresql acts, at least.=C2=A0 Not how AWS RDS Postgresq= l acts (it's all hidden behind a virtual IP that you always=C2=A0connec= t=C2=A0to), but I don't know anything about Aurora.

--
Death to <Redacted>, and= butter sauce.
Don't boil me, I'm still alive.
<= ;Redacted> lobster!
--0000000000001a1421062b49101e--