How to reset a server error '25P02 in_failed_sql_transaction'

Le mar. 31 déc. 2019 à 06:55, Matthias Apitz <guru@unixarea.de> a écrit :

Hello,

Due to a wrong human input in the GUI of our application our
application server, from the point of view of the PostgreSQL server it
is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
server, here from our own debug logging the command and the error
message of the server:

posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum
WHERE desk>='*2019' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum FROM
:select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
...

All subsequent correct (SELECT ...) statements get rejected with, for
example:

...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum
WHERE desk>='31.12.1900' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr
ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum FROM
:select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until
end of transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
========posSqlError=======
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN
TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG
server to return to normal operations?

You need to issue a ROLLBACK. then you'll be able to open another
transaction.

--
Guillaume.

Hi,

Which behavior are you looking for ?
If you want the following statements to succeed, I guess that you don't want to be in a transaction context.
In this case, you should have a look at the following link : https://www.postgresql.org/docs/11/ecpg-commands.html
By default, the AUTOCOMMIT is OFF but you can set it to ON and it should solve your issue as only explicit transactions will keep a transaction context.

Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96

-----Original Message-----
From: Matthias Apitz <guru@unixarea.de>
Sent: Tuesday, December 31, 2019 6:55 AM
To: pgsql-general@lists.postgresql.org
Subject: How to reset a server error '25P02 in_failed_sql_transaction'

Hello,

Due to a wrong human input in the GUI of our application our application server, from the point of view of the PostgreSQL server it is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL server, here from our own debug logging the command and the error message of the server:

posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum FROM :select_anw; ==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918 ...

All subsequent correct (SELECT ...) statements get rejected with, for example:

...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum FROM :select_anw; ==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
========posSqlError=======
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG server to return to normal operations?

Thanks

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub

On 12/31/19 3:40 AM, Guillaume Lelarge wrote:

Le mar. 31 déc. 2019 à 06:55, Matthias Apitz <guru@unixarea.de
<mailto:guru@unixarea.de>> a écrit :

Hello,

Due to a wrong human input in the GUI of our application our
application server, from the point of view of the PostgreSQL server it
is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
server, here from our own debug logging the command and the error
message of the server:

posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from
titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 )  ORDER BY desk ASC
, feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum 
FROM :select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
...

All subsequent correct (SELECT ...) statements get rejected with, for
example:

...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from
titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY
desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum 
FROM :select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored
until end of transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
========posSqlError=======
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN
TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG
server to return to normal operations?

You need to issue a ROLLBACK. then you'll be able to open another transaction.

But how do you issue a ROLLBACK to a different pid?

--
Angular momentum makes the world go 'round.

On Tuesday, December 31, 2019, Ron <ronljohnsonjr@gmail.com> wrote:

But how do you issue a ROLLBACK to a different pid?

You cannot. At that point you need to start from scratch.

pg_terminate_backend(*pid int*)

And let the problematic app deal with losing its database connection
however it will.

David J.

On 12/31/19 11:29 AM, David G. Johnston wrote:

On Tuesday, December 31, 2019, Ron <ronljohnsonjr@gmail.com
<mailto:ronljohnsonjr@gmail.com>> wrote:

But how do you issue a ROLLBACK to a different pid?

You cannot.  At that point you need to start from scratch.

pg_terminate_backend(/pid int/)

And let the problematic app deal with losing its database connection
however it will.

David J.

That's what I thought (and privately told OP).

--
Angular momentum makes the world go 'round.

El día martes, diciembre 31, 2019 a las 10:06:22a. m. +0000, Patrick FICHE escribió:

Hi,

Which behavior are you looking for ?
If you want the following statements to succeed, I guess that you don't want to be in a transaction context.
In this case, you should have a look at the following link : https://www.postgresql.org/docs/11/ecpg-commands.html
By default, the AUTOCOMMIT is OFF but you can set it to ON and it should solve your issue as only explicit transactions will keep a transaction context.

Regards,

Hi,

To summarize the different hints:

- terminate the relevant backend process with pg_terminate_backend()
- issue ROLLBACK
- set AUTOCOMMIT TO ON

Thanks, I will try these hints in the order from bottom to up, and as
well to close/re-open the connection to the server, without the GUI
client will note this; every GUI client has its own application server:

GUI (Java in Windows) --> application server (ESQL/C Linux) --> PGserver

Thanks (and btw: Happy New Jear!)

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X

On Tue, 2019-12-31 at 06:55 +0100, Matthias Apitz wrote:

Due to a wrong human input in the GUI of our application our
application server, from the point of view of the PostgreSQL server it
is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
server, here from our own debug logging the command and the error
message of the server:

posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum FROM :select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
...

All subsequent correct (SELECT ...) statements get rejected with, for example:

...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum FROM :select_anw;
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
========posSqlError=======
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG
server to return to normal operations?

Then you must have "autocommit" set to "off", and you must catch the error
in your code and issue an explicit rollback.

Maybe you should have a look at your transaction management in general, because
it is very important that COMMIT is issued once any transaction is completed.
COMMIT on an aborted transaction will automatically cause a rollback.

Did you make sure that your code is not vulnerable to SQL injection?
What is the statement if the user enters a single quote for the date?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com