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 1ovE8h-0002DS-Tp for pgsql-general@arkaria.postgresql.org; Wed, 16 Nov 2022 08:51:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ovE8g-0006Vx-2Z for pgsql-general@arkaria.postgresql.org; Wed, 16 Nov 2022 08:51:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ovE8f-0006Uz-JY for pgsql-general@lists.postgresql.org; Wed, 16 Nov 2022 08:51:21 +0000 Received: from ms-10.1blu.de ([178.254.4.101]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ovE8V-0001IQ-Hh for pgsql-general@postgresql.org; Wed, 16 Nov 2022 08:51:17 +0000 Received: from [178.254.11.41] (helo=sh4-5.1blu.de) by ms-10.1blu.de with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1ovE8Q-0004cP-3x; Wed, 16 Nov 2022 09:51:06 +0100 Date: Wed, 16 Nov 2022 09:51:05 +0100 From: Matthias Apitz To: Tom Lane Cc: Adrian Klaver , Postgres General Subject: Re: PostgreSQL server "idle in transaction" Message-ID: <20221116085105.GB9@sh4-5.1blu.de> Reply-To: Matthias Apitz References: <20221115122813.GA9@sh4-5.1blu.de> <1070974.1668526091@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1070974.1668526091@sss.pgh.pa.us> X-Operating-System: FreeBSD 12.0-CURRENT r314251 (amd64) X-message-flag: Mails containing HTML will not be read! Please send only plain text. User-Agent: Mutt/1.9.4 (2018-02-28) X-Con-Id: 51246 X-Con-U: 0-guru X-Originating-IP: 178.254.11.41 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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? Thanks again matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!