Re: PostgreSQL server "idle in transaction" - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: PostgreSQL server "idle in transaction" |
Date | |
Msg-id | af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com Whole thread Raw |
In response to | PostgreSQL server "idle in transaction" (Matthias Apitz <guru@unixarea.de>) |
Responses |
Re: PostgreSQL server "idle in transaction"
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-general |
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@aklaver.com
pgsql-general by date: