Re: How to reset a server error '25P02 in_failed_sql_transaction'

Le mar. 31 déc. 2019 à 06:55, Matthias Apitz <guru(at)unixarea(dot)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.