From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Matthias Apitz <guru@unixarea.de>
To: Postgres General <pgsql-general@postgresql.org>
Subject: Re: PostgreSQL server "idle in transaction"
Date: Tue, 15 Nov 2022 07:08:32 -0800
Message-ID: <af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com> (raw)
In-Reply-To: <20221115122813.GA9@sh4-5.1blu.de>
References: <20221115122813.GA9@sh4-5.1blu.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, guru@unixarea.de
Subject: Re: PostgreSQL server "idle in transaction"
In-Reply-To: <af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com>
References: <20221115122813.GA9@sh4-5.1blu.de> <af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox