pg.ddx.io pgpool-general@postgresql.org mailing list archive
help / color / mirror / Atom feedlow level protocol, implicit transactions , "idle in transaction" issue
16+ messages / 3 participants
[nested] [flat]
* low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-22 14:32 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-22 14:32 UTC (permalink / raw)
To: pgpool-general@lists.postgresql.org <pgpool-general@lists.postgresql.org>; +Cc: Achilleas Mantzios <itdev@gatewaynet.com>
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;
/*
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-22 16:08 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-22 16:08 UTC (permalink / raw)
To: pgpool-general@lists.postgresql.org
sorry I forgot to mention,
before the patch, disabling the query cache in pgpool the problem went away.
On 7/22/26 17:32, Achilleas Mantzios wrote:
>
> 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!
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-23 22:52 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-07-23 22:52 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org
Hi,
> sorry I forgot to mention,
>
> before the patch, disabling the query cache in pgpool the problem went
> away.
>
> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>
>> 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!
So your problem occurs only if following conditions are all met:
1) query cache is enabled
2) no explicit transaction is used
3) extended query protocol is used
Am I correct?
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-24 04:10 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-24 04:10 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgpool-general@lists.postgresql.org
Good day Tatsuo ! It must be noon in Japan now!
On 7/24/26 01:52, Tatsuo Ishii wrote:
> Hi,
>
>> sorry I forgot to mention,
>>
>> before the patch, disabling the query cache in pgpool the problem went
>> away.
>>
>> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>> 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!
> So your problem occurs only if following conditions are all met:
>
> 1) query cache is enabled
> 2) no explicit transaction is used
> 3) extended query protocol is used
>
> Am I correct?
Yes exactly!
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-28 01:01 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-07-28 01:01 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org
>>> sorry I forgot to mention,
>>>
>>> before the patch, disabling the query cache in pgpool the problem went
>>> away.
>>>
>>> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>>> 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!
>> So your problem occurs only if following conditions are all met:
>>
>> 1) query cache is enabled
>> 2) no explicit transaction is used
>> 3) extended query protocol is used
>>
>> Am I correct?
>
> Yes exactly!
So I trited to reprodce the issue using pgproto. Tool chain is:
pgproto->pgpool->PostgreSQL
pgpool.conf is set up by pgpool_setup and I added followings to pgpool.conf.
memory_cache_enabled = on
log_min_messages = debug1
reset_query_list = 'ABORT'
I set reset_query_list to exclude "DISCARD ALL" since if it's
included, PostgreSQL complains that DISCARD ALL cannot be executed
inside transaction. This issue needs to be solved but I think it's not
related to your issue.
Here is the test data for pgproto.
'P' "" "SELECT * FROM t1" 0
'B' "" "" 0 0 0
'D' 'P' ""
'E' "" 0
'S'
'Y'
'X'
This executes parse (SELECT * FROM t1, using unnamed statement), bind
(using unnamed portal), Describye, Execute and Sync.
Here is the result from pg_stat_activity.
psql -p 11002 -c "select state,query from pg_stat_activity where backend_type = 'client backend'" test
state | query
--------+--------------------------------------------------------------------------------
idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
active | select state,query from pg_stat_activity where backend_type = 'client backend'
(2 rows)
So the "state" was "idle", not "idle in transaction". I am wondering
why you get "idle in trasaction" without issuing an explicit
transaction from your side. Is it possible that your tool chain always
start a trasanction internally? What would happen if you bypass
pgbouncer?
Anyway, adding:
log_client_messages = on
will record helpful pgpool.log.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-28 04:34 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 2 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-28 04:34 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgpool-general@lists.postgresql.org
On 7/28/26 04:01, Tatsuo Ishii wrote:
>>>> sorry I forgot to mention,
>>>>
>>>> before the patch, disabling the query cache in pgpool the problem went
>>>> away.
>>>>
>>>> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>>>> 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!
>>> So your problem occurs only if following conditions are all met:
>>>
>>> 1) query cache is enabled
>>> 2) no explicit transaction is used
>>> 3) extended query protocol is used
>>>
>>> Am I correct?
>> Yes exactly!
> So I trited to reprodce the issue using pgproto. Tool chain is:
>
> pgproto->pgpool->PostgreSQL
>
> pgpool.conf is set up by pgpool_setup and I added followings to pgpool.conf.
>
> memory_cache_enabled = on
> log_min_messages = debug1
> reset_query_list = 'ABORT'
>
> I set reset_query_list to exclude "DISCARD ALL" since if it's
> included, PostgreSQL complains that DISCARD ALL cannot be executed
> inside transaction. This issue needs to be solved but I think it's not
> related to your issue.
>
> Here is the test data for pgproto.
>
> 'P' "" "SELECT * FROM t1" 0
> 'B' "" "" 0 0 0
> 'D' 'P' ""
> 'E' "" 0
> 'S'
> 'Y'
> 'X'
>
> This executes parse (SELECT * FROM t1, using unnamed statement), bind
> (using unnamed portal), Describye, Execute and Sync.
>
> Here is the result from pg_stat_activity.
>
> psql -p 11002 -c "select state,query from pg_stat_activity where backend_type = 'client backend'" test
>
> state | query
> --------+--------------------------------------------------------------------------------
> idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
> active | select state,query from pg_stat_activity where backend_type = 'client backend'
> (2 rows)
>
> So the "state" was "idle", not "idle in transaction". I am wondering
> why you get "idle in trasaction" without issuing an explicit
> transaction from your side. Is it possible that your tool chain always
> start a trasanction internally? What would happen if you bypass
> pgbouncer?
IMHO, the only chance for this behavior , is to have multiple statements
inside a query, or maybe the effect of pipelining comes into effect , as
per :
https://www.postgresql.org/docs/current/protocol-flow.html
>
> Anyway, adding:
> log_client_messages = on
> will record helpful pgpool.log.
Thank you.
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-28 09:14 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
1 sibling, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-28 09:14 UTC (permalink / raw)
To: pgpool-general@lists.postgresql.org; +Cc: Achilleas Mantzios <itdev@gatewaynet.com>
Hi Tatsuo
On 7/28/26 07:34, Achilleas Mantzios wrote:
> On 7/28/26 04:01, Tatsuo Ishii wrote:
>
>>>>> sorry I forgot to mention,
>>>>>
>>>>> before the patch, disabling the query cache in pgpool the problem
>>>>> went
>>>>> away.
>>>>>
>>>>> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>>>>> 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!
>>>> So your problem occurs only if following conditions are all met:
>>>>
>>>> 1) query cache is enabled
>>>> 2) no explicit transaction is used
>>>> 3) extended query protocol is used
>>>>
>>>> Am I correct?
>>> Yes exactly!
>> So I trited to reprodce the issue using pgproto. Tool chain is:
>>
>> pgproto->pgpool->PostgreSQL
>>
>> pgpool.conf is set up by pgpool_setup and I added followings to
>> pgpool.conf.
>>
>> memory_cache_enabled = on
>> log_min_messages = debug1
>> reset_query_list = 'ABORT'
>>
>> I set reset_query_list to exclude "DISCARD ALL" since if it's
>> included, PostgreSQL complains that DISCARD ALL cannot be executed
>> inside transaction. This issue needs to be solved but I think it's not
>> related to your issue.
>>
>> Here is the test data for pgproto.
>>
>> 'P' "" "SELECT * FROM t1" 0
>> 'B' "" "" 0 0 0
>> 'D' 'P' ""
>> 'E' "" 0
>> 'S'
>> 'Y'
>> 'X'
>>
>> This executes parse (SELECT * FROM t1, using unnamed statement), bind
>> (using unnamed portal), Describye, Execute and Sync.
>>
>> Here is the result from pg_stat_activity.
>>
>> psql -p 11002 -c "select state,query from pg_stat_activity where
>> backend_type = 'client backend'" test
>>
>> state | query
>> --------+--------------------------------------------------------------------------------
>>
>> idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
>> active | select state,query from pg_stat_activity where
>> backend_type = 'client backend'
>> (2 rows)
>>
>> So the "state" was "idle", not "idle in transaction". I am wondering
>> why you get "idle in trasaction" without issuing an explicit
>> transaction from your side. Is it possible that your tool chain always
>> start a trasanction internally? What would happen if you bypass
>> pgbouncer?
>
> IMHO, the only chance for this behavior , is to have multiple
> statements inside a query, or maybe the effect of pipelining comes
> into effect , as per :
>
> https://www.postgresql.org/docs/current/protocol-flow.html
>
>>
>> Anyway, adding:
>> log_client_messages = on
>> will record helpful pgpool.log.
> Thank you.
I re-tested without the patch.
Here is our config :
pgpool@smadevnu:~ % egrep -e '^[a-z]+' /usr/local/pgpool/etc/pgpool.conf
backend_clustering_mode = streaming_replication
listen_addresses = '*'
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_data_directory0 = '/usr/local/var/lib/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'smadevnu'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
ssl = on
ssl_key = '/usr/local/pgpool/etc/server.key'
ssl_cert = '/usr/local/pgpool/etc/server.crt'
num_init_children = 200
max_pool = 4
log_line_prefix = '%r [%p] %c %m %a %u@%d line:%l ' # printf-style
string to output at beginning of each log line.
log_connections = on
log_disconnections = on
log_statement = off
log_client_messages = on
log_min_messages = debug1 # values in order of decreasing
detail:
logging_collector = on
log_directory = '/usr/local/pgpool/log'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
connection_cache = on
sr_check_user = 'periodic'
sr_check_password = 'foo4foo!@#$%^'
memory_cache_enabled = on
Here is the pgpool log, pls focus on pid = 57366
>>
>> Regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>
>
Attachments:
[text/x-log] sample_pgpool.log (382.1K, ../../23fee34e-1d0a-4592-9add-1a6744f80d34@cloud.gatewaynet.com/3-sample_pgpool.log)
download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-29 00:56 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
1 sibling, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-07-29 00:56 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org
>> So I trited to reprodce the issue using pgproto. Tool chain is:
>>
>> pgproto->pgpool->PostgreSQL
>>
>> pgpool.conf is set up by pgpool_setup and I added followings to
>> pgpool.conf.
>>
>> memory_cache_enabled = on
>> log_min_messages = debug1
>> reset_query_list = 'ABORT'
>>
>> I set reset_query_list to exclude "DISCARD ALL" since if it's
>> included, PostgreSQL complains that DISCARD ALL cannot be executed
>> inside transaction. This issue needs to be solved but I think it's not
>> related to your issue.
>>
>> Here is the test data for pgproto.
>>
>> 'P' "" "SELECT * FROM t1" 0
>> 'B' "" "" 0 0 0
>> 'D' 'P' ""
>> 'E' "" 0
>> 'S'
>> 'Y'
>> 'X'
>>
>> This executes parse (SELECT * FROM t1, using unnamed statement), bind
>> (using unnamed portal), Describye, Execute and Sync.
>>
>> Here is the result from pg_stat_activity.
>>
>> psql -p 11002 -c "select state,query from pg_stat_activity where
>> backend_type = 'client backend'" test
>>
>> state | query
>> --------+--------------------------------------------------------------------------------
>> idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
>> active | select state,query from pg_stat_activity where backend_type =
>> 'client backend'
>> (2 rows)
>>
>> So the "state" was "idle", not "idle in transaction". I am wondering
>> why you get "idle in trasaction" without issuing an explicit
>> transaction from your side. Is it possible that your tool chain always
>> start a trasanction internally? What would happen if you bypass
>> pgbouncer?
>
> IMHO, the only chance for this behavior , is to have multiple
> statements inside a query, or maybe the effect of pipelining comes
> into effect , as per :
>
> https://www.postgresql.org/docs/current/protocol-flow.html
> multiple statements inside a query
is not supported in extended query protocol.
> pipelining
Yes, extended query protocol (including pipelining) with no explicit
transaction will start an implicit transaction in the backend. In fact
I get following error on DISCARD ALL (which was executed because
pgpool's reset_query_list) with no explicit transaction started.
2026-07-29 09:40:19.975: pgproto pid 447427: DEBUG: do_query: extended:1 query:"SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'"
2026-07-29 09:36:06.853: pgproto pid 447075: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 447113 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"
Question is, whether pg_stat_activity.state shows the query state in
the (implicit) transactions as "idle in transaction" or not. To
confirm this, I modified pgproto to allow the pgproto frontend sleep
so that I can check what pg_stat_activity.state looks like after
sending extended query (SELECT) and flush message, and before sending
Sync message[1].
psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
" test
state | backend_xid | query
--------+-------------+---------------------------------------------------------------
active | | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
The state was "active". After the Sync message was sent:
psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
" test
state | backend_xid | query
--------+-------------+-----------------------------------------
idle | | DISCARD ALL
So I don't see "idle in trasaction" here.
>> Anyway, adding:
>> log_client_messages = on
>> will record helpful pgpool.log.
I looked in the pgpool.log and found this:
[57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:383 LOG: Query message from frontend.
[57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:384 DETAIL: query: "BEGIN"
So I suspect the reason "idle in trasaction" was seen is, client
actually create an explicit transaction by sending "BEGIN".
[1] pgproto.data (modification to pgproto is attached)
#----------------------------------------------------------
'P' "" "SELECT 1" 0
'B' "" "" 0 0 0
'D' 'P' ""
'E' "" 0
'P' "" "SELECT 2" 0
'B' "" "" 0 0 0
'D' 'P' ""
'E' "" 0
'H'
# sleep 60000 milli seconds (= 60 seconds)
's' 60000
'S'
'Y'
'X'
#----------------------------------------------------------
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Attachments:
[text/x-patch] pgproto.patch (728B, ../../20260729.095613.1477575373912540234.ishii@postgresql.org/2-pgproto.patch)
download | inline diff:
diff --git a/src/tools/pgproto/main.c b/src/tools/pgproto/main.c
index 18e7f59a9..9c9950c45 100644
--- a/src/tools/pgproto/main.c
+++ b/src/tools/pgproto/main.c
@@ -368,6 +368,7 @@ process_message_type(int kind, char *buf, PGconn *conn)
char *err_msg;
char *data;
char *bufp;
+ long stime;
switch (kind)
{
@@ -461,6 +462,13 @@ process_message_type(int kind, char *buf, PGconn *conn)
process_function_call(buf, conn);
break;
+ case 's': /* frontend sleep request */
+ SKIP_TABS(buf);
+ stime = buffer_read_int(buf, &bufp);
+ fprintf(stderr, "FE=> Sleep %ld\n", stime);
+ usleep((useconds_t) stime * 1000);
+ break;
+
default:
fprintf(stderr, "Unknown kind: %c", kind);
break;
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-29 07:20 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-29 07:20 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgpool-general@lists.postgresql.org
Hi Tatsuo
On 7/29/26 03:56, Tatsuo Ishii wrote:
>>> So I trited to reprodce the issue using pgproto. Tool chain is:
>>>
>>> pgproto->pgpool->PostgreSQL
>>>
>>> pgpool.conf is set up by pgpool_setup and I added followings to
>>> pgpool.conf.
>>>
>>> memory_cache_enabled = on
>>> log_min_messages = debug1
>>> reset_query_list = 'ABORT'
>>>
>>> I set reset_query_list to exclude "DISCARD ALL" since if it's
>>> included, PostgreSQL complains that DISCARD ALL cannot be executed
>>> inside transaction. This issue needs to be solved but I think it's not
>>> related to your issue.
>>>
>>> Here is the test data for pgproto.
>>>
>>> 'P' "" "SELECT * FROM t1" 0
>>> 'B' "" "" 0 0 0
>>> 'D' 'P' ""
>>> 'E' "" 0
>>> 'S'
>>> 'Y'
>>> 'X'
>>>
>>> This executes parse (SELECT * FROM t1, using unnamed statement), bind
>>> (using unnamed portal), Describye, Execute and Sync.
>>>
>>> Here is the result from pg_stat_activity.
>>>
>>> psql -p 11002 -c "select state,query from pg_stat_activity where
>>> backend_type = 'client backend'" test
>>>
>>> state | query
>>> --------+--------------------------------------------------------------------------------
>>> idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
>>> active | select state,query from pg_stat_activity where backend_type =
>>> 'client backend'
>>> (2 rows)
>>>
>>> So the "state" was "idle", not "idle in transaction". I am wondering
>>> why you get "idle in trasaction" without issuing an explicit
>>> transaction from your side. Is it possible that your tool chain always
>>> start a trasanction internally? What would happen if you bypass
>>> pgbouncer?
>> IMHO, the only chance for this behavior , is to have multiple
>> statements inside a query, or maybe the effect of pipelining comes
>> into effect , as per :
>>
>> https://www.postgresql.org/docs/current/protocol-flow.html
>> multiple statements inside a query
> is not supported in extended query protocol.
Cool thanks.
>
>> pipelining
> Yes, extended query protocol (including pipelining) with no explicit
> transaction will start an implicit transaction in the backend. In fact
> I get following error on DISCARD ALL (which was executed because
> pgpool's reset_query_list) with no explicit transaction started.
>
> 2026-07-29 09:40:19.975: pgproto pid 447427: DEBUG: do_query: extended:1 query:"SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'"
> 2026-07-29 09:36:06.853: pgproto pid 447075: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 447113 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"
>
> Question is, whether pg_stat_activity.state shows the query state in
> the (implicit) transactions as "idle in transaction" or not. To
> confirm this, I modified pgproto to allow the pgproto frontend sleep
> so that I can check what pg_stat_activity.state looks like after
> sending extended query (SELECT) and flush message, and before sending
> Sync message[1].
>
> psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
> " test
> state | backend_xid | query
> --------+-------------+---------------------------------------------------------------
> active | | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
>
> The state was "active". After the Sync message was sent:
>
> psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
> " test
> state | backend_xid | query
> --------+-------------+-----------------------------------------
> idle | | DISCARD ALL
>
> So I don't see "idle in trasaction" here.
>
>>> Anyway, adding:
>>> log_client_messages = on
>>> will record helpful pgpool.log.
> I looked in the pgpool.log and found this:
>
> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:383 LOG: Query message from frontend.
> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:384 DETAIL: query: "BEGIN"
>
> So I suspect the reason "idle in trasaction" was seen is, client
> actually create an explicit transaction by sending "BEGIN".
Yes there is an explicit begin, always followed by (as you can see in
the same log) :
[57366] 2026-07-28 09:30:11.089 qSMAdynacom amantzio@dynacom line:459
DETAIL: statement: "", query: "COMMIT"
Yes, no matter how many 'y' I put in pgproto.data I didn't manage to get
the 'idle in transaction', but alone this does not prove there is no
problem.
The effect with the 'idle in transaction' without my patch is consistent.
Would you like to send you a tcpdump from pgpool -> pgsql , without my
patch and with my patch in order to spot the difference ?
>
> [1] pgproto.data (modification to pgproto is attached)
> #----------------------------------------------------------
> 'P' "" "SELECT 1" 0
> 'B' "" "" 0 0 0
> 'D' 'P' ""
> 'E' "" 0
>
> 'P' "" "SELECT 2" 0
> 'B' "" "" 0 0 0
> 'D' 'P' ""
> 'E' "" 0
>
> 'H'
>
> # sleep 60000 milli seconds (= 60 seconds)
> 's' 60000
>
> 'S'
> 'Y'
> 'X'
> #----------------------------------------------------------
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-31 07:56 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-07-31 07:56 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org
>> I looked in the pgpool.log and found this:
>>
>> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:383
>> LOG: Query message from frontend.
>> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:384
>> DETAIL: query: "BEGIN"
>>
>> So I suspect the reason "idle in trasaction" was seen is, client
>> actually create an explicit transaction by sending "BEGIN".
>
>
> Yes there is an explicit begin, always followed by (as you can see in
> the same log) :
>
> [57366] 2026-07-28 09:30:11.089 qSMAdynacom amantzio@dynacom line:459
> DETAIL: statement: "", query: "COMMIT"
>
> Yes, no matter how many 'y' I put in pgproto.data I didn't manage to
> get the 'idle in transaction', but alone this does not prove there is
> no problem.
>
> The effect with the 'idle in transaction' without my patch is
> consistent.
I don't believe the 'idle in transaction' is caused by an implicit
transaction. I think the AI generated comment below is a
hallucination.
+ * 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
I plan to apply attached patch (rebased for master branch) with
modified comment by me. The reason I am going to apply is, not prevent
the "idle in transaction" symptom reported by you (because I cannot
reproduce it); the patch prevent the error when DISCARD ALL is
executed in a reset_query_list:
2026-07-31 16:37:28.194: pgproto pid 503581: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 503597 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"
When extended queries are executed:
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Parse: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Bind: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: D message
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Execute: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Sync
2026-07-31 16:40:58.487: pgproto pid 504738: LOG: DB node id: 0 backend pid: 504771 statement: SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
If query cache is enabled, even frontend sends a sync message,
do_query sends query "SELECT oid FROM pg_catalog.pg_database WHERE
datname = 'test" to get database oid because it needs the data to
register the query cache result. The query is executed in extended
query protocol because do_extended query mode continues. As a result
the implicit transaction started by do_query is not closed and the
error occurs.
The patch closes the implicit transaction started by do_query and the
error is gone.
> Would you like to send you a tcpdump from pgpool -> pgsql , without my
> patch and with my patch in order to spot the difference ?
It's obvious that with the patch pgpool send "sync" instead of
"flush". You don't need to check the tcpdump output.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Attachments:
[text/x-patch] do_query_sync_master.patch (2.5K, ../../20260731.165638.198450343752871098.ishii@postgresql.org/2-do_query_sync_master.patch)
download | inline diff:
diff --git a/src/protocol/pool_process_query.c b/src/protocol/pool_process_query.c
index 202afa2a3..49bda67bc 100644
--- a/src/protocol/pool_process_query.c
+++ b/src/protocol/pool_process_query.c
@@ -1959,6 +1959,7 @@ do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT **result, int
int num_close_complete;
int state;
bool data_pushed;
+ bool sent_sync = false; /* true if we sent a Sync (not Flush) */
data_pushed = false;
@@ -2107,21 +2108,14 @@ do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT **result, int
pool_write(backend, prepared_name, pname_len);
/*
- * 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.
+ * Send Sync message. If we are in an explicit transaction, sending
+ * "sync" is safe because it will not break user's unnamed portal. If
+ * we are not in an explicit transaction, sending a sync message
+ * closes an unnamed portal. But next user's bind message will create
+ * the unnamed portal anyway.
*/
- if (backend->tstate == 'T')
- pool_write(backend, "S", 1); /* send "sync" message */
- else
- {
- pool_write(backend, "H", 1); /* send "flush" message */
- /* remember that we sent queries but did not send sync message */
- pool_get_session_context(true)->pending_sync_map[backend->db_node_id] = true;
- }
+ pool_write(backend, "S", 1); /* send "sync" message */
+ sent_sync = true;
len = htonl(sizeof(len));
pool_write_and_flush(backend, &len, sizeof(len));
}
@@ -2232,7 +2226,7 @@ do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT **result, int
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;
@@ -2244,7 +2238,7 @@ do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT **result, int
* 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;
/*
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-31 12:59 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-07-31 12:59 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgpool-general@lists.postgresql.org, Achilleas Mantzios <itdev@gatewaynet.com>
Hi Tatsuo
On 7/31/26 10:56, Tatsuo Ishii wrote:
>>> I looked in the pgpool.log and found this:
>>>
>>> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:383
>>> LOG: Query message from frontend.
>>> [57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio@dynacom line:384
>>> DETAIL: query: "BEGIN"
>>>
>>> So I suspect the reason "idle in trasaction" was seen is, client
>>> actually create an explicit transaction by sending "BEGIN".
>>
>> Yes there is an explicit begin, always followed by (as you can see in
>> the same log) :
>>
>> [57366] 2026-07-28 09:30:11.089 qSMAdynacom amantzio@dynacom line:459
>> DETAIL: statement: "", query: "COMMIT"
>>
>> Yes, no matter how many 'y' I put in pgproto.data I didn't manage to
>> get the 'idle in transaction', but alone this does not prove there is
>> no problem.
>>
>> The effect with the 'idle in transaction' without my patch is
>> consistent.
> I don't believe the 'idle in transaction' is caused by an implicit
> transaction. I think the AI generated comment below is a
> hallucination.
>
> + * 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
Ok but then how can we explain the system complaining about :
"DISCARD ALL cannot run inside a transaction block" ?
Apparently there was inside a transaction somehow, and upon hitting the
home page the app (Quarkus) apart from the xaction in the logging table
didn't start any other explicitly.
Also the problem never manifested when against plain vanilla postgresql,
or pgbouncer -> postgresql,
And only when against pgbouncer -> pgpool ->
postgresqlmemory_cache_enabled = true
>
> I plan to apply attached patch (rebased for master branch) with
> modified comment by me. The reason I am going to apply is, not prevent
> the "idle in transaction" symptom reported by you (because I cannot
> reproduce it); the patch prevent the error when DISCARD ALL is
> executed in a reset_query_list:
>
> 2026-07-31 16:37:28.194: pgproto pid 503581: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 503597 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"
>
> When extended queries are executed:
> 2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Parse: SELECT 1
> 2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Bind: SELECT 1
> 2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: D message
> 2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Execute: SELECT 1
> 2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Sync
> 2026-07-31 16:40:58.487: pgproto pid 504738: LOG: DB node id: 0 backend pid: 504771 statement: SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
>
> If query cache is enabled, even frontend sends a sync message,
> do_query sends query "SELECT oid FROM pg_catalog.pg_database WHERE
> datname = 'test" to get database oid because it needs the data to
> register the query cache result. The query is executed in extended
> query protocol because do_extended query mode continues. As a result
> the implicit transaction started by do_query is not closed and the
> error occurs.
>
> The patch closes the implicit transaction started by do_query and the
> error is gone.
>
>> Would you like to send you a tcpdump from pgpool -> pgsql , without my
>> patch and with my patch in order to spot the difference ?
> It's obvious that with the patch pgpool send "sync" instead of
> "flush". You don't need to check the tcpdump output.
Anyway , Thanks for the patch Tatsuo !
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English:http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-07-31 22:35 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-07-31 22:35 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org; itdev@gatewaynet.com
>> I don't believe the 'idle in transaction' is caused by an implicit
>> transaction. I think the AI generated comment below is a
>> hallucination.
>>
>> + * 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
>
> Ok but then how can we explain the system complaining about :
>
> "DISCARD ALL cannot run inside a transaction block" ?
>
> Apparently there was inside a transaction somehow,
It does not necessarily mean pg_stat_activity shows it as "idle in
transaction". From my experience, without issuing an explicit
transaction from client, pg_stat_activity shows "idle" or "active",
but never "idle in transaction". I guess PostgreSQL distinguish an
explicit transaction and an implicit transaction.
> and upon hitting
> the home page the app (Quarkus) apart from the xaction in the logging
> table didn't start any other explicitly.
>
> Also the problem never manifested when against plain vanilla
> postgresql, or pgbouncer -> postgresql,
>
> And only when against pgbouncer -> pgpool ->
> postgresqlmemory_cache_enabled = true
Yes, in the case above, pgpool issues do_query which causes open
implicit transaction. But again, I think it does not cause
pg_stat_activity showing "idle in transaction".
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-08-02 06:50 Tatsuo Ishii <ishii@postgresql.org>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-08-02 06:50 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org; itdev@gatewaynet.com
Hi Achilleas,
>> Ok but then how can we explain the system complaining about :
>>
>> "DISCARD ALL cannot run inside a transaction block" ?
>>
>> Apparently there was inside a transaction somehow,
>
> It does not necessarily mean pg_stat_activity shows it as "idle in
> transaction". From my experience, without issuing an explicit
> transaction from client, pg_stat_activity shows "idle" or "active",
> but never "idle in transaction". I guess PostgreSQL distinguish an
> explicit transaction and an implicit transaction.
>
>> and upon hitting
>> the home page the app (Quarkus) apart from the xaction in the logging
>> table didn't start any other explicitly.
>>
>> Also the problem never manifested when against plain vanilla
>> postgresql, or pgbouncer -> postgresql,
>>
>> And only when against pgbouncer -> pgpool ->
>> postgresqlmemory_cache_enabled = true
>
> Yes, in the case above, pgpool issues do_query which causes open
> implicit transaction. But again, I think it does not cause
> pg_stat_activity showing "idle in transaction".
Patch pushed to all supported branches.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
As I wroite in the commit messages, I hoped the patch fixes "DISCARD
ALL cannot run inside a transaction block" error.
However, the original intension of the patch was to fix "idle in
transaction" left in pg_stat_activity. Please try the 4.7 patch if you
like.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-08-02 18:38 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2026-08-02 18:38 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgpool-general@lists.postgresql.org; itdev@gatewaynet.com
On 8/2/26 09:50, Tatsuo Ishii wrote:
> Hi Achilleas,
>
>>> Ok but then how can we explain the system complaining about :
>>>
>>> "DISCARD ALL cannot run inside a transaction block" ?
>>>
>>> Apparently there was inside a transaction somehow,
>> It does not necessarily mean pg_stat_activity shows it as "idle in
>> transaction". From my experience, without issuing an explicit
>> transaction from client, pg_stat_activity shows "idle" or "active",
>> but never "idle in transaction". I guess PostgreSQL distinguish an
>> explicit transaction and an implicit transaction.
>>
>>> and upon hitting
>>> the home page the app (Quarkus) apart from the xaction in the logging
>>> table didn't start any other explicitly.
>>>
>>> Also the problem never manifested when against plain vanilla
>>> postgresql, or pgbouncer -> postgresql,
>>>
>>> And only when against pgbouncer -> pgpool ->
>>> postgresqlmemory_cache_enabled = true
>> Yes, in the case above, pgpool issues do_query which causes open
>> implicit transaction. But again, I think it does not cause
>> pg_stat_activity showing "idle in transaction".
> Patch pushed to all supported branches.
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
>
> As I wroite in the commit messages, I hoped the patch fixes "DISCARD
> ALL cannot run inside a transaction block" error.
>
> However, the original intension of the patch was to fix "idle in
> transaction" left in pg_stat_activity. Please try the 4.7 patch if you
> like.
Thank you Tatsuo for the hard work you are putting into pgpool !
I don't quite feel right about the course of events during this thread,
meaning me resorting to our local AI to pull the iron out of the fire, I
hope to more personal involvement next round! at least I wish so.
>
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-09-06 21:25 Tatsuo Ishii <ishii@postgresql.org>
parent: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tatsuo Ishii @ 2026-09-06 21:25 UTC (permalink / raw)
To: a.mantzios@cloud.gatewaynet.com; +Cc: pgpool-general@lists.postgresql.org; itdev@gatewaynet.com
I found the commit:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
[Fix do_query to send sync rather than flush.]
introduced a bug. Here is a reproducer. Note that pgpool must be
configured to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test by a
reason explained later in this message.
-----------------------------------------------------------------
$ psql -p 11000 -a -f failure.sql test
DROP TABLE test;
DROP TABLE
-- start a pipeline
\startpipeline
-- CREATE a table
CREATE TABLE test(i int);
-- SELECT non-existent table, which raises an error,
-- and aborts the implicit transaction started by the pipeline.
SELECT * from a;
-- Recover from the error and closes the implicit transaction.
\syncpipeline
\endpipeline
CREATE TABLE
psql:failure.sql:11: ERROR: relation "a" does not exist
LINE 1: SELECT * from a;
^
-- Try to SELECT the table created in the previous pipeline.
-- This should fail because the creation of the table "test" was rollbacked.
SELECT * from test;
i
---
(0 rows)
-----------------------------------------------------------------
In this example, a table named "test" is created in a pipeline. Then
a SELECT is executed. Because the SELECT tries to retrieve rows from
non-existent table "a", it causes an error and a roll back of the
implicit transaction started by the pipeline. As a result, the table
"test" created in the pipeline does not exist at the end of the
pipeline. However, a "sync" message was issued by do_query() while
obtaining information regarding table "a". As the sync message caused
commit of the implicit transaction started by the pipeline, creation
of "test" was not roll backed by the subsequent erroneous SELECT.
IMO this is a serious data consistency issue because it breaks the
transaction semantics. If I directly connects to PostgreSQL or pgpool
by the previous commit, and run the script, it ends up with:
SELECT * from test;
psql:failure.sql:14: ERROR: relation "test" does not exist
LINE 1: SELECT * from test;
^
which is the expected behavior.
Another bug:
In the begging of this message, I wrote that pgpool must be configured
to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test. I am
going to explain the reason.
When a pipeline including a write query and a read query starts,
pgpool executes the write query on primary. The subsequent read query
can be run on standby if load balance is enabled. So it is possible
that implicit transaction including a write query runs on primary, and
an implicit transaction including a read query runs on standby. Even
if the transaction running on standby aborts by an error, it does not
affect the transaction on primary. As a result, the transaction on
primary successfully commits and the table "test" is created.
So we have two problems:
(1) An implicit transaction does not roll back when it should, due to
an internal sync message.
(2) An implicit transaction does not roll back when it should, due to
load balance.
To solve (1), I am going to revert the commit [Fix do_query to send
sync rather than flush.] Of course this cancel the fix (issue with
query cache) in the commit, but I think we should solve it in
different way.
For (2), I can't think of a solution right now. I need more time to
think of a solution.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
> On 8/2/26 09:50, Tatsuo Ishii wrote:
>
>> Hi Achilleas,
>>
>>>> Ok but then how can we explain the system complaining about :
>>>>
>>>> "DISCARD ALL cannot run inside a transaction block" ?
>>>>
>>>> Apparently there was inside a transaction somehow,
>>> It does not necessarily mean pg_stat_activity shows it as "idle in
>>> transaction". From my experience, without issuing an explicit
>>> transaction from client, pg_stat_activity shows "idle" or "active",
>>> but never "idle in transaction". I guess PostgreSQL distinguish an
>>> explicit transaction and an implicit transaction.
>>>
>>>> and upon hitting
>>>> the home page the app (Quarkus) apart from the xaction in the logging
>>>> table didn't start any other explicitly.
>>>>
>>>> Also the problem never manifested when against plain vanilla
>>>> postgresql, or pgbouncer -> postgresql,
>>>>
>>>> And only when against pgbouncer -> pgpool ->
>>>> postgresqlmemory_cache_enabled = true
>>> Yes, in the case above, pgpool issues do_query which causes open
>>> implicit transaction. But again, I think it does not cause
>>> pg_stat_activity showing "idle in transaction".
>> Patch pushed to all supported branches.
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
>>
>> As I wroite in the commit messages, I hoped the patch fixes "DISCARD
>> ALL cannot run inside a transaction block" error.
>>
>> However, the original intension of the patch was to fix "idle in
>> transaction" left in pg_stat_activity. Please try the 4.7 patch if you
>> like.
>
> Thank you Tatsuo for the hard work you are putting into pgpool !
>
> I don't quite feel right about the course of events during this
> thread, meaning me resorting to our local AI to pull the iron out of
> the fire, I hope to more personal involvement next round! at least I
> wish so.
>
>>
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: low level protocol, implicit transactions , "idle in transaction" issue
@ 2026-09-07 06:36 Achilleas Mantzios <itdev@gatewaynet.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2026-09-07 06:36 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>; pgpool-general@lists.postgresql.org
Thanks for the follow up!
-Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Management LTD
(As Agents only)
Email : itdev@gatewaynet.com
----- Original Message -----
From: "Tatsuo Ishii" <ishii@postgresql.org>
To: "Achilleas Mantzios" <a.mantzios@cloud.gatewaynet.com>
Cc: pgpool-general@lists.postgresql.org, "ITDEV" <itdev@gatewaynet.com>
Sent: Monday, 7 September, 2026 00:25:21
Subject: Re: low level protocol, implicit transactions , "idle in transaction" issue
I found the commit:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
[Fix do_query to send sync rather than flush.]
introduced a bug. Here is a reproducer. Note that pgpool must be
configured to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test by a
reason explained later in this message.
-----------------------------------------------------------------
$ psql -p 11000 -a -f failure.sql test
DROP TABLE test;
DROP TABLE
-- start a pipeline
\startpipeline
-- CREATE a table
CREATE TABLE test(i int);
-- SELECT non-existent table, which raises an error,
-- and aborts the implicit transaction started by the pipeline.
SELECT * from a;
-- Recover from the error and closes the implicit transaction.
\syncpipeline
\endpipeline
CREATE TABLE
psql:failure.sql:11: ERROR: relation "a" does not exist
LINE 1: SELECT * from a;
^
-- Try to SELECT the table created in the previous pipeline.
-- This should fail because the creation of the table "test" was rollbacked.
SELECT * from test;
i
---
(0 rows)
-----------------------------------------------------------------
In this example, a table named "test" is created in a pipeline. Then
a SELECT is executed. Because the SELECT tries to retrieve rows from
non-existent table "a", it causes an error and a roll back of the
implicit transaction started by the pipeline. As a result, the table
"test" created in the pipeline does not exist at the end of the
pipeline. However, a "sync" message was issued by do_query() while
obtaining information regarding table "a". As the sync message caused
commit of the implicit transaction started by the pipeline, creation
of "test" was not roll backed by the subsequent erroneous SELECT.
IMO this is a serious data consistency issue because it breaks the
transaction semantics. If I directly connects to PostgreSQL or pgpool
by the previous commit, and run the script, it ends up with:
SELECT * from test;
psql:failure.sql:14: ERROR: relation "test" does not exist
LINE 1: SELECT * from test;
^
which is the expected behavior.
Another bug:
In the begging of this message, I wrote that pgpool must be configured
to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test. I am
going to explain the reason.
When a pipeline including a write query and a read query starts,
pgpool executes the write query on primary. The subsequent read query
can be run on standby if load balance is enabled. So it is possible
that implicit transaction including a write query runs on primary, and
an implicit transaction including a read query runs on standby. Even
if the transaction running on standby aborts by an error, it does not
affect the transaction on primary. As a result, the transaction on
primary successfully commits and the table "test" is created.
So we have two problems:
(1) An implicit transaction does not roll back when it should, due to
an internal sync message.
(2) An implicit transaction does not roll back when it should, due to
load balance.
To solve (1), I am going to revert the commit [Fix do_query to send
sync rather than flush.] Of course this cancel the fix (issue with
query cache) in the commit, but I think we should solve it in
different way.
For (2), I can't think of a solution right now. I need more time to
think of a solution.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
> On 8/2/26 09:50, Tatsuo Ishii wrote:
>
>> Hi Achilleas,
>>
>>>> Ok but then how can we explain the system complaining about :
>>>>
>>>> "DISCARD ALL cannot run inside a transaction block" ?
>>>>
>>>> Apparently there was inside a transaction somehow,
>>> It does not necessarily mean pg_stat_activity shows it as "idle in
>>> transaction". From my experience, without issuing an explicit
>>> transaction from client, pg_stat_activity shows "idle" or "active",
>>> but never "idle in transaction". I guess PostgreSQL distinguish an
>>> explicit transaction and an implicit transaction.
>>>
>>>> and upon hitting
>>>> the home page the app (Quarkus) apart from the xaction in the logging
>>>> table didn't start any other explicitly.
>>>>
>>>> Also the problem never manifested when against plain vanilla
>>>> postgresql, or pgbouncer -> postgresql,
>>>>
>>>> And only when against pgbouncer -> pgpool ->
>>>> postgresqlmemory_cache_enabled = true
>>> Yes, in the case above, pgpool issues do_query which causes open
>>> implicit transaction. But again, I think it does not cause
>>> pg_stat_activity showing "idle in transaction".
>> Patch pushed to all supported branches.
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
>>
>> As I wroite in the commit messages, I hoped the patch fixes "DISCARD
>> ALL cannot run inside a transaction block" error.
>>
>> However, the original intension of the patch was to fix "idle in
>> transaction" left in pg_stat_activity. Please try the 4.7 patch if you
>> like.
>
> Thank you Tatsuo for the hard work you are putting into pgpool !
>
> I don't quite feel right about the course of events during this
> thread, meaning me resorting to our local AI to pull the iron out of
> the fire, I hope to more personal involvement next round! at least I
> wish so.
>
>>
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2026-09-07 06:36 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-22 14:32 low level protocol, implicit transactions , "idle in transaction" issue Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-22 16:08 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-23 22:52 ` Tatsuo Ishii <ishii@postgresql.org>
2026-07-24 04:10 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-28 01:01 ` Tatsuo Ishii <ishii@postgresql.org>
2026-07-28 04:34 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-28 09:14 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-29 00:56 ` Tatsuo Ishii <ishii@postgresql.org>
2026-07-29 07:20 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-31 07:56 ` Tatsuo Ishii <ishii@postgresql.org>
2026-07-31 12:59 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-07-31 22:35 ` Tatsuo Ishii <ishii@postgresql.org>
2026-08-02 06:50 ` Tatsuo Ishii <ishii@postgresql.org>
2026-08-02 18:38 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2026-09-06 21:25 ` Tatsuo Ishii <ishii@postgresql.org>
2026-09-07 06:36 ` Achilleas Mantzios <itdev@gatewaynet.com>
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