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.klaver@xxxxxxxxxxx
