Re: PostgreSQL server "idle in transaction"

On 11/15/22 04:28, Matthias Apitz wrote:
>
> Hello,
>
> We face with our application servers (ESQL/C written) and a 14.1 server
> on Linux, the situation that the PostgreSQL backend for servers are
> saying "idle in transaction". One can see this in the table
> pg_stat_activity and also on the shell:
>
> $ ps -ef | grep transaction
> postgres 6979 24002 0 11:05 ? 00:00:00 postgres: sisis testdb 127.0.0.1(58620) idle in transaction
>
> testdb=# select * from pg_stat_activity where pid=6979;
> datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
>
> 734526 | testdb | 6979 | | 16384 | sisis | SunRise DBCALL V7.3 (pid=6978) | 127.0.0.1 | | 58620 | 15.11.2022 11:05:50.153359 CET | 15.11.2022 11:05:50.173748 CET | 15.11.2022 11:05:50.174322 CET | 15.11.2022 11:05:50.174346 CET | Client | ClientRead | idle in transaction | | 17444593 | | select name from pg_cursors where name = $1 | client backend
>
> The application server itself has done some initializations, updated a
> table about the fact that it was started, commited the update and waits
> for work to do (which would be come as commands over some network
> socket). The last PostgreSQL related action was looking into the table
> 'pg_cursors' to see if some used CURSOR is still open, which was not the
> case. This last query is still visible in pg_stat_activity.query.
>
> I have below the full ESQL/C log and do not understand, why the
> PostgreSQL server is thinking "idle in transaction". For me with the
> "COMMIT" on the line below marked with ^^^^^ the transaction was closed.
> Am I wrong?

The query being shown 'idle in transaction' is:

select name from pg_cursors where name = $1

From your log:

[6978] [15.11.2022 11:05:50:172]: ECPGtrans on line 1211: action
"commit"; connection "testdb"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[6978] [15.11.2022 11:05:50:173]: ecpg_execute on line 543: query:
select name from pg_cursors where name = $1 ; with 1 parameter(s) on
connection testdb

So that query is being executed after the COMMIT.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com