Обсуждение: 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
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 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:
But how do you issue a ROLLBACK to a different pid?
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?
--
Angular momentum makes the world go 'round.
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:
That's what I thought (and privately told OP).
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).
--
Angular momentum makes the world go 'round.
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 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