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 1ovMQc-0003qf-JT for pgsql-general@arkaria.postgresql.org; Wed, 16 Nov 2022 17:42:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ovMQb-0003QN-Gu for pgsql-general@arkaria.postgresql.org; Wed, 16 Nov 2022 17:42:25 +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 1ovMQa-0003Q5-B7 for pgsql-general@lists.postgresql.org; Wed, 16 Nov 2022 17:42:25 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ovMQX-0007pK-7P for pgsql-general@postgresql.org; Wed, 16 Nov 2022 17:42:23 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id C92D43200312; Wed, 16 Nov 2022 12:42:18 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Wed, 16 Nov 2022 12:42:19 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc: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=1668620538; x= 1668706938; bh=+/DvwXgt/+WPc+4UBhNKAW10xGazuA9M2xwnIooV8tI=; b=0 whn5Dp2tnuRN2zoWs6dECVnjZTCUu4093jwwizPN8cWZDt2t+0oOHOS0O/VlOI2w muA3jqz9QX7O70bMOSSRKj624+AqHi4qnhmlKcxtFEsbupanE4AyriE/R0r4XHn4 IdT7Tl4z3jDeZGQW+QQSTpXZvyHmvqv2RrH2VrtnZ3+XT5dKNaoLTdTycu94wiMS hQzDT7KYeTpiylh4/a7xIkhsDkpLPW8bI6qw5wQ1ikZVwVpAwUivR+6heXhTX1gJ n5RnRG60CmhAd4oL9/uHU0heH872x+coxpZGGZWLcz0ZSqx0GQ/Fg+9tP3Qa5fK9 LZ+LbaVroR90AAeBtuWJA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc: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=1668620538; x= 1668706938; bh=+/DvwXgt/+WPc+4UBhNKAW10xGazuA9M2xwnIooV8tI=; b=c RNcs2v0kTUdCpifysBt7r1qPoK85TOTn/tRiPMzqRNLH7MAQ7HWpz/NjVpyNadXg L8eE5ozhnenI/iNjAUnSKqzAmXnB8HJNFQezKlomBN0ffgrr7+q9kjUfZkBXNnKT qcGpfJArK0yXLptI+4Ou4w9cpGQOgMHCCGxyKsiYUERcMxPDBYibXsv2j9w7c6w/ aKoQHG/42YUBL3fy/T+eGEYownk/hsRqj3ByPsJHmPLxlQptMGHpCPjuUpP/c/Xk oCpsSQaKLFzUSdbNx+Ku1xm02ORKdnh30+h8gXnWaBnu/2dkAgJB+Kq0pg1lUz2B um4BppEGY73Wl0bJi9EXw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrgeeigddutdefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepuffvvehfhffkffgfgggjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepgfevudejieduhfduieeiueektdevieeiieeghedu keeifeehgfeigeegvdevveevnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 16 Nov 2022 12:42:17 -0500 (EST) Subject: Re: PostgreSQL server "idle in transaction" To: Matthias Apitz , Tom Lane Cc: Postgres General References: <20221115122813.GA9@sh4-5.1blu.de> <1070974.1668526091@sss.pgh.pa.us> <20221116085105.GB9@sh4-5.1blu.de> From: Adrian Klaver Message-ID: <4ab443bf-2f44-24ed-56d3-3fc3db0203fc@aklaver.com> Date: Wed, 16 Nov 2022 09:42:16 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.0 MIME-Version: 1.0 In-Reply-To: <20221116085105.GB9@sh4-5.1blu.de> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/16/22 12:51 AM, Matthias Apitz wrote: > El día Dienstag, November 15, 2022 a las 10:28:11 -0500, Tom Lane escribió: > >> Adrian Klaver writes: >>> On 11/15/22 04:28, Matthias Apitz wrote: >>>> 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. >> >>> 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. >> >> Right. By default, ecpg would start a new transaction block for that. >> See >> >> https://www.postgresql.org/docs/current/ecpg-commands.html#ECPG-TRANSACTIONS >> > > Thank you. This page makes it clear why the last search started a > transaction, which at the end is pending due to a missing COMMIT. > > On the other hand, when we would set EXEC SQL SET AUTOCOMMIT TO ON, when does > the transaction block starts exactly (which could be rolled back), as > there is no EXEC SQL BEGIN? I don't use ecpg, but the docs mention: "The embedded SQL interface also supports autocommit of transactions (similar to psql's default behavior)" In psql: test_(postgres)(5432)=# select 1/0; ERROR: division by zero test_(postgres)(5432)=# rollback ; WARNING: there is no transaction in progress ROLLBACK test_(postgres)(5432)=# select 1/1; ?column? ---------- 1 versus explicit transaction: test_(postgres)(5432)=# begin ; BEGIN test_(postgres)(5432)=# select 1/0; ERROR: division by zero test_(postgres)(5432)=# select 1/1; ERROR: current transaction is aborted, commands ignored until end of transaction block test_(postgres)(5432)=# rollback ; ROLLBACK test_(postgres)(5432)=# select 1/1; ?column? ---------- 1 > > Thanks again > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com