Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouxYO-0002X3-NX for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 15:08:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ouxYL-0002Qy-MZ for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 15:08:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouxYK-0002Qp-Hh for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 15:08:45 +0000 Received: from wout1-smtp.messagingengine.com ([64.147.123.24]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouxYF-0002Ms-Oa for pgsql-general@postgresql.org; Tue, 15 Nov 2022 15:08:43 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id 94586320031A; Tue, 15 Nov 2022 10:08:35 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Tue, 15 Nov 2022 10:08:35 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1668524915; x= 1668611315; bh=hjfz6JnC/PcQEo595I+G7JB1YBdKbf0PgKG0am930O0=; b=G blvSVh/iYYeitSXGC6MCI8HdPE2gq/wG7+8JM8bgkpJ3waBMpHiSIOvXBPi6876T QHocDvK7cC4PoSMqqlcuDhHpeGtzggpp4Vyhq65C28I/ZcuNBnQ3v8iwWnvsH/NM aepxgIDQ0p/6GvJWlU95Syh4lLPDgZPgpAgC0f2iPU42bHKH4STZu1hGixoK3x31 PEGhy6pbBO037/Ot4ug21/NFPowAiHDQI9m5Hjv8TNnXD0t6X1gIn5+/Bf3NY3qQ z6JZxjlJLvkil+OJLfZeNTSv0cGg1cNPck2rFh90sXCQfWIx7DerhLmu+XcgeQvU T380h+OYj6IO3Hq36UwMw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1668524915; x=1668611315; bh=h jfz6JnC/PcQEo595I+G7JB1YBdKbf0PgKG0am930O0=; b=Fkk7vzm2YwsWZS0MY chpgV0eyMK0RTa+iHxdPwcD5nGT3PP6fBCk1tOwYv/x4SuFctTXqFLshrS8tHNis tif/MUc9kkva6LQR2dp0OeoLNkGJd/gIOPv+ttwuQATpGVzDsh2LtN0Fl04arpsO VKSsYmSQ7hgaaDG6/vgcSJjprS2dQmWkfYWWOajA9IWrLFHwjywbfOR49ZesMvBN cYCaHczpSkHdd1HxMgFzzL4/j4SB40CZL2/9rYdO1IlodV+9Khpxj6iMTEbOIPUt 1AGcMrkwjdyEz9z1iPLh+5y2FKP2+oJkO89ECIr1n5qL2wZMJgqUp+Tx5ifX8OtF nhq4g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrgeeggdejfecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtjeertddtfeejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepvedvkeeihfduhffhtdekleegffduiefgteejgeffgffh teefleeludfgleelgeevnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 15 Nov 2022 10:08:33 -0500 (EST) Message-ID: Date: Tue, 15 Nov 2022 07:08:32 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: PostgreSQL server "idle in transaction" To: Matthias Apitz , Postgres General References: <20221115122813.GA9@sh4-5.1blu.de> Content-Language: en-US From: Adrian Klaver In-Reply-To: <20221115122813.GA9@sh4-5.1blu.de> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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