From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgpool-general@lists.postgresql.org <pgpool-general@lists.postgresql.org>
Cc: Achilleas Mantzios <itdev@gatewaynet.com>
Subject: low level protocol, implicit transactions , "idle in transaction" issue
Date: Wed, 22 Jul 2026 17:32:55 +0300
Message-ID: <25939319-d77c-4df9-9e65-dfb1637ccd33@cloud.gatewaynet.com> (raw)
Dear pgpool team
This is our setup :
Quarkus (Agroal) -> pgbouncer-1.25.1 -> pgpool 4.7.2 -> pgsql 18.3
We noticed that with no explicit transactions, at start up SQL queries
(application_name, search_path, etc), we experienced random 'idle in
transaction' connections from pgpool -> pgsql.
Bypassing pgpool, i.e. pgbouncer -> directly to pgsql , the issue was
not there.
So, bypassing the past excellent relation and collaboration I had with
you guys, I got into the temptation to use our local AI cli agent
(against our local GLM-5.2-NVFP4 ) from within pgpool 4.7.2 code base
and try to see what's going on.
So it gave it a thorough examination, looked at past issues, etc and it
came up with a patch. It applied the patch , compiled and installed, and
I only had to restart pgpool.
The problem seems gone .
I attach the patch. Please review and tell me your thoughts. I Know I
should maybe go the classic route, mailing list, advice from you, full
logging, etc till I demo the issue, but the time is pushing us hard.
Thank you!
Attachments:
[text/x-patch] do_query_sync.patch (2.6K, ../25939319-d77c-4df9-9e65-dfb1637ccd33@cloud.gatewaynet.com/3-do_query_sync.patch)
download | inline diff:--- a/src/protocol/pool_process_query.c+++ b/src/protocol/pool_process_query.c@@ -1951,6 +1951,7 @@
int num_close_complete;
int state;
bool data_pushed;
+ bool sent_sync = false; /* true if we sent a Sync (not Flush) */
data_pushed = false;
@@ -2101,15 +2102,26 @@
/*
* Send sync or flush message. If we are in an explicit transaction,
* sending "sync" is safe because it will not break unnamed portal.
- * Also this is desirable because if no user queries are sent after- * do_query(), COMMIT command could cause statement time out, because- * flush message does not clear the alarm for statement time out which- * has been set when do_query() issues query.+ *+ * In the extended-query path we now ALWAYS send Sync. The previous+ * code sent Flush when tstate != 'T', but tstate is only updated+ * from simple-query BEGIN/COMMIT CommandComplete tags; it is NOT+ * updated from the ReadyForQuery ('Z') transaction-status byte in+ * the extended path, and it is never 'T' for implicit (autocommit)+ * transactions. With Flush, the implicit transaction opened by the+ * backend around this Parse/Bind/Execute is never closed, leaving+ * the server connection "idle in transaction" after do_query()+ * returns. This is observable with clients that use the extended+ * protocol with implicit transactions (e.g. Quarkus/Agroal) behind+ * pgbouncer, and only when memory_cache_enabled = on, because that+ * is what makes pgpool issue these internal catalog lookups via+ * do_query() outside of an explicit transaction. Sync closes the+ * implicit transaction; the named statement/portal we use here+ * ("pgpool<PID>") is unaffected, and the unnamed portal is rebuilt+ * by the next user Parse anyway.
*/
- if (backend->tstate == 'T')- pool_write(backend, "S", 1); /* send "sync" message */- else- pool_write(backend, "H", 1); /* send "flush" message */+ pool_write(backend, "S", 1); /* send "sync" message */+ sent_sync = true;
len = htonl(sizeof(len));
pool_write_and_flush(backend, &len, sizeof(len));
}
@@ -2220,7 +2232,7 @@
return;
/* If "sync" message was issued, 'Z' is expected. */
- if (doing_extended && backend->tstate == 'T')+ if (doing_extended && sent_sync)
state |= COMMAND_COMPLETE_RECEIVED;
break;
@@ -2232,7 +2244,7 @@
* If "sync" message was issued, 'Z' is expected, else we are
* done with 'C'.
*/
- if (!doing_extended || backend->tstate != 'T')+ if (!doing_extended || !sent_sync)
state |= COMMAND_COMPLETE_RECEIVED;
/*
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 using the --to and --cc options:
reply via email
To: pgpool-general@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, pgpool-general@lists.postgresql.org, itdev@gatewaynet.com
Subject: Re: low level protocol, implicit transactions , "idle in transaction" issue
In-Reply-To: <25939319-d77c-4df9-9e65-dfb1637ccd33@cloud.gatewaynet.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