Date: Fri, 18 Apr 2025 07:31:52 +0000 (UTC)
From: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>
Reply-To: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>
To: Pgsql-general <pgsql-general@postgresql.org>, 
	Laurenz Albe <laurenz.albe@cybertec.at>
Message-ID: <1852089030.3173833.1744961512201@mail.yahoo.com>
In-Reply-To: <02e2740edf821e2ce84773c83065b45e9be890cc.camel@cybertec.at>
References: <1385521371.3160626.1744955370756.ref@mail.yahoo.com> <1385521371.3160626.1744955370756@mail.yahoo.com> <02e2740edf821e2ce84773c83065b45e9be890cc.camel@cybertec.at>
Subject: Re:  Error while updating a table
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_3173832_1381213279.1744961512200"
Content-Length: 9094
Archived-At: <https://www.postgresql.org/message-id/1852089030.3173833.1744961512201%40mail.yahoo.com>
Precedence: bulk

------=_Part_3173832_1381213279.1744961512200
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 Thanks Laurenz,
Now I need to do 2 things.
1.=C2=A0 Correct my application to catch errors and do either Rollback or c=
ommit.=C2=A0 =C2=A0This part is running smoothly from all other computers, =
so far.=C2=A0 Nevertheless I'll check that for any missing catch.2.=C2=A0 F=
ind the faulty entry and do rectify.
For the second part, how to identify that culprit query?=C2=A0 =C2=A0Seems =
this has happened a few days back.=C2=A0 At that time, we had some power is=
sues that made network to fail.=C2=A0=C2=A0
To proceed, we need to find the culprit query?=C2=A0 How? Where?=C2=A0 No i=
dea what was the pid then, as all computers were shut down every night.
Any possibilities?=C2=A0 steps?
Happiness Always
BKR Sivaprakash

    On Friday 18 April, 2025 at 12:24:54 pm IST, Laurenz Albe <laurenz.albe=
@cybertec.at> wrote: =20
=20
 On Fri, 2025-04-18 at 05:49 +0000, sivapostgres@yahoo.com wrote:
> We use PowerBuilder along with PostgreSQL.
>=20
> PostgreSQL 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compi=
led by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
>=20
> Suddenly, one system starts throwing an error while updating a record fro=
m PowerBuilder.
> The same update statement (window) works fine, when run from other comput=
ers.=C2=A0 Error
> occurs only when the statement is run from a particular computer.=C2=A0 T=
he SQL Statement is
>=20
> Update public.co_voucherseries_transaction_branch
> Set=C2=A0 =C2=A0 vouchernumber=C2=A0 =C2=A0=3D <new number>
> Where=C2=A0 companycode=C2=A0 =C2=A0 =C2=A0=3D '100'
> And=C2=A0 =C2=A0 branchcode=C2=A0 =C2=A0 =C2=A0 =3D '001'
> And=C2=A0 =C2=A0 accountperiodid =3D 1
> And=C2=A0 =C2=A0 voucherseries=C2=A0 =C2=A0=3D 'SERIES'
> And=C2=A0 =C2=A0 voucherversion=C2=A0 =3D 'version'
> And=C2=A0 =C2=A0 activestatus=C2=A0 =C2=A0 =3D 'Y'=C2=A0 ;
>=20
> The error is
> SQLSTATE=3D25P02
> ERROR: current transaction is aborted, commands ignored until end of tran=
saction block;
> Error while executing the query
>=20
> Through googling, I found that last transaction was not properly rolled b=
ack.
> The (Postgresql) Linux server will be shut down every night and re-booted=
 next day morning.=C2=A0 =C2=A0
>=20
> Don't know when this error has occurred.=C2=A0=C2=A0
>=20
> Now I need to rectify this error?=C2=A0 How to do it?=C2=A0 Any help is r=
eally appreciated.

You would have to improve the code quality of the application, which does n=
ot seem
to do correct error handling or transaction management.

That error means that the *previous* statement (or one of the statements ea=
rlier in
the same database transaction) has caused an error.=C2=A0 In PostgreSQL, th=
at means that
the transaction is aborted, and the following statements until the end of t=
he
transaction will receive the error you experience.

So one of the following must be the case:

- the previous statement caused an error, but you didn't detect or handle t=
hat

- the previous transaction run on the same database connection caused an er=
ror,
=C2=A0 but you forgot to run ROLLBACK

Yours,
Laurenz Albe


 =20
------=_Part_3173832_1381213279.1744961512200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydpba4d30e7yahoo-style-wrap" style=
=3D"font-family:times new roman, new york, times, serif;font-size:16px;"><d=
iv></div>
        <div dir=3D"ltr" data-setdir=3D"false">Thanks Laurenz,</div><div di=
r=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"f=
alse">Now I need to do 2 things.</div><div dir=3D"ltr" data-setdir=3D"false=
"><br></div><div dir=3D"ltr" data-setdir=3D"false">1.&nbsp; Correct my appl=
ication to catch errors and do either Rollback or commit.&nbsp; &nbsp;This =
part is running smoothly from all other computers, so far.&nbsp; Neverthele=
ss I'll check that for any missing catch.</div><div dir=3D"ltr" data-setdir=
=3D"false">2.&nbsp; Find the faulty entry and do rectify.</div><div dir=3D"=
ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"false"=
>For the second part, how to identify that culprit query?&nbsp; &nbsp;Seems=
 this has happened a few days back.&nbsp; At that time, we had some power i=
ssues that made network to fail.&nbsp;&nbsp;</div><div dir=3D"ltr" data-set=
dir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"false">To proceed, =
we need to find the culprit query?&nbsp; How? Where?&nbsp; No idea what was=
 the pid then, as all computers were shut down every night.</div><div dir=
=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"fa=
lse">Any possibilities?&nbsp; steps?</div><div dir=3D"ltr" data-setdir=3D"f=
alse"><br></div><div dir=3D"ltr" data-setdir=3D"false">Happiness Always<br>=
BKR Sivaprakash</div><div dir=3D"ltr" data-setdir=3D"false"><br></div><div =
dir=3D"ltr" data-setdir=3D"false"><br></div>
       =20
        </div><div id=3D"yahoo_quoted_5712706810" class=3D"yahoo_quoted">
            <div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;color:#26282a;">
               =20
                <div>
                        On Friday 18 April, 2025 at 12:24:54 pm IST, Lauren=
z Albe &lt;laurenz.albe@cybertec.at&gt; wrote:
                    </div>
                    <div><br></div>
                    <div><br></div>
               =20
               =20
                <div><div dir=3D"ltr">On Fri, 2025-04-18 at 05:49 +0000, <a=
 shape=3D"rect" ymailto=3D"mailto:sivapostgres@yahoo.com" href=3D"mailto:si=
vapostgres@yahoo.com">sivapostgres@yahoo.com</a> wrote:<div class=3D"yqt058=
1814272" id=3D"yqtfd35893"><br clear=3D"none">&gt; We use PowerBuilder alon=
g with PostgreSQL.<br clear=3D"none">&gt; <br clear=3D"none">&gt; PostgreSQ=
L 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc =
(Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit<br clear=3D"none">&gt; <br clear=
=3D"none">&gt; Suddenly, one system starts throwing an error while updating=
 a record from PowerBuilder.<br clear=3D"none">&gt; The same update stateme=
nt (window) works fine, when run from other computers.&nbsp; Error<br clear=
=3D"none">&gt; occurs only when the statement is run from a particular comp=
uter.&nbsp; The SQL Statement is<br clear=3D"none">&gt; <br clear=3D"none">=
&gt; Update public.co_voucherseries_transaction_branch<br clear=3D"none">&g=
t; Set&nbsp; &nbsp; vouchernumber&nbsp; &nbsp;=3D &lt;new number&gt;<br cle=
ar=3D"none">&gt; Where&nbsp; companycode&nbsp; &nbsp; &nbsp;=3D '100'<br cl=
ear=3D"none">&gt; And&nbsp; &nbsp; branchcode&nbsp; &nbsp; &nbsp; =3D '001'=
<br clear=3D"none">&gt; And&nbsp; &nbsp; accountperiodid =3D 1<br clear=3D"=
none">&gt; And&nbsp; &nbsp; voucherseries&nbsp; &nbsp;=3D 'SERIES'<br clear=
=3D"none">&gt; And&nbsp; &nbsp; voucherversion&nbsp; =3D 'version'<br clear=
=3D"none">&gt; And&nbsp; &nbsp; activestatus&nbsp; &nbsp; =3D 'Y'&nbsp; ;<b=
r clear=3D"none">&gt; <br clear=3D"none">&gt; The error is<br clear=3D"none=
">&gt; SQLSTATE=3D25P02<br clear=3D"none">&gt; ERROR: current transaction i=
s aborted, commands ignored until end of transaction block;<br clear=3D"non=
e">&gt; Error while executing the query<br clear=3D"none">&gt; <br clear=3D=
"none">&gt; Through googling, I found that last transaction was not properl=
y rolled back.<br clear=3D"none">&gt; The (Postgresql) Linux server will be=
 shut down every night and re-booted next day morning.&nbsp; &nbsp;<br clea=
r=3D"none">&gt; <br clear=3D"none">&gt; Don't know when this error has occu=
rred.&nbsp;&nbsp;<br clear=3D"none">&gt; <br clear=3D"none">&gt; Now I need=
 to rectify this error?&nbsp; How to do it?&nbsp; Any help is really apprec=
iated.</div><br clear=3D"none"><br clear=3D"none">You would have to improve=
 the code quality of the application, which does not seem<br clear=3D"none"=
>to do correct error handling or transaction management.<br clear=3D"none">=
<br clear=3D"none">That error means that the *previous* statement (or one o=
f the statements earlier in<br clear=3D"none">the same database transaction=
) has caused an error.&nbsp; In PostgreSQL, that means that<br clear=3D"non=
e">the transaction is aborted, and the following statements until the end o=
f the<br clear=3D"none">transaction will receive the error you experience.<=
br clear=3D"none"><br clear=3D"none">So one of the following must be the ca=
se:<br clear=3D"none"><br clear=3D"none">- the previous statement caused an=
 error, but you didn't detect or handle that<br clear=3D"none"><br clear=3D=
"none">- the previous transaction run on the same database connection cause=
d an error,<br clear=3D"none">&nbsp; but you forgot to run ROLLBACK<br clea=
r=3D"none"><br clear=3D"none">Yours,<br clear=3D"none">Laurenz Albe<div cla=
ss=3D"yqt0581814272" id=3D"yqtfd49793"><br clear=3D"none"><br clear=3D"none=
"><br clear=3D"none"></div></div></div>
            </div>
        </div></body></html>
------=_Part_3173832_1381213279.1744961512200--