MIME-Version: 1.0
References: <CAEzWdqeNp1HRJt7SkQxANB60CMhrGuJsRKtEpu-Ghb_nXUvgoQ@mail.gmail.com>
 <86acb9be-a695-4902-b612-e2fb15339982@aklaver.com>
In-Reply-To: <86acb9be-a695-4902-b612-e2fb15339982@aklaver.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Thu, 9 Jan 2025 22:31:20 +0530
Message-ID: <CAEzWdqcXB956YV_DjENVPtGyi3n-cUpNYkG57g4ZnZrUF2yecQ@mail.gmail.com>
Subject: Re: recovery error while running any statement
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000033a18f062b48eeec"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcXB956YV_DjENVPtGyi3n-cUpNYkG57g4ZnZrUF2yecQ%40mail.gmail.com>
Precedence: bulk

--00000000000033a18f062b48eeec
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jan 9, 2025 at 10:21=E2=80=AFPM Adrian Klaver <adrian.klaver@aklave=
r.com>
wrote:

> On 1/9/25 08:42, yudhi s wrote:
> > Hello Experts,
> > It's postgres aurora version 16. While  running the ALTER command on an=
y
> > 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 out
> > with "/cannot set transaction read-write mode during recovery/".
> >
> > Want to understand , what is the cause of this error and how to fix thi=
s?
>
> Per:
>
>
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Auror=
aPostgreSQL.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?

--00000000000033a18f062b48eeec
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jan 9, 2025=
 at 10:21=E2=80=AFPM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@akla=
ver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">On 1/9/25 08:42, yudhi s wrote:<br>
&gt; Hello Experts,<br>
&gt; It&#39;s postgres aurora version 16. While=C2=A0 running the ALTER com=
mand on any <br>
&gt; object we see an error &quot;/Only RowExclusiveLock or less can be acq=
uired <br>
&gt; on database objects during recovery/&quot;. If I run any DML it gives =
an <br>
&gt; error stating &#39;/cannot execute UPDATE in a read-only transaction/&=
#39; , <br>
&gt; then I tried setting &quot;/set transaction read-write/&quot; and it e=
rroring out <br>
&gt; with &quot;/cannot set transaction read-write mode during recovery/&qu=
ot;.<br>
&gt; <br>
&gt; Want to understand=C2=A0, what is the cause of this error and how to f=
ix this?<br>
<br>
Per:<br>
<br>
<a href=3D"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aur=
ora.AuroraPostgreSQL.html" rel=3D"noreferrer" target=3D"_blank">https://doc=
s.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.h=
tml</a><br>
<br>
Aurora Postgres is not the community edition, you should probably take <br>
this up with AWS support.<br>
<br><br></blockquote><div>Sure will raise a ticket. I was trying to underst=
and=C2=A0though=C2=A0, if it&#39;s possible in postgres to have these sudde=
n &quot;recovery errors&quot; possible because of some long running DML/DDL=
 killed unexpectedly using the &quot;pg_terminate/pg_cancel&quot; 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</div><div>=C2=A0</div></div></div>

--00000000000033a18f062b48eeec--