pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (6+ messages)  latest in thread

Message-ID: <af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com>
Permalink:  ../af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com/
Also on:    postgresql.org/message-id/af10f913-dfbb-0f86-4fdd-10e51ce87f1e@aklaver.com

 · 

reply

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