Thread: 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 )
ORDERBY 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

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
willkeep 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
PostgreSQLserver it is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL server, here from our
owndebug 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 )
ORDERBY 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> 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?

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

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

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

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.

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

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
transactionswill 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 )
ORDERBY 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