Pgpool leaves an internal transaction open after autocommitted extended-protocol DML

[apkipa]

Pgpool-II version

4.8devel (1a873669), backend_clustering_mode = raw, connection_cache = on, one configured PostgreSQL node.

Description

In raw mode, an autocommitted extended-protocol UPDATE returns successfully through Pgpool, but the backend remains idle in transaction and retains the row lock. A second client trying to delete that row waits until its statement timeout. Direct PostgreSQL commits the update and the delete succeeds.

is_strict_query() classifies UPDATE as strict DML (pool_process_query.c), and the Parse() path starts an internal transaction and sets allow_close_transaction (pool_proto_modules.c):

if (is_strict_query(query_context->parse_tree))
{
	start_internal_transaction(frontend, backend, query_context->parse_tree);
	allow_close_transaction = 1;
}

start_internal_transaction() sends BEGIN to an idle backend and marks it as internal (pool_process_query.c). Pgpool's ReadyForQuery() handler processes the backend ReadyForQuery ('Z') message (pool_proto_modules.c), but closes the internal transaction only inside if (REPLICATION && allow_close_transaction) (pool_proto_modules.c). REPLICATION excludes CM_RAW (pool.h), so the internal transaction is left open.

Reproduce

1. Start PostgreSQL 16 and Pgpool-II in raw mode with one configured PostgreSQL node and connection_cache = on.
2. Install psycopg and run the following:

#!/usr/bin/env python3
import psycopg


TABLE = "scm_pgpool_txn_probe"


def reset_table() -> None:
    with psycopg.connect(DIRECT_DSN, autocommit=True) as conn:
        conn.execute(f"DROP TABLE IF EXISTS {TABLE}")
        conn.execute(f"CREATE TABLE {TABLE}(id integer primary key, value integer)")
        conn.execute(f"INSERT INTO {TABLE} VALUES (1, 10)")


def run(label: str, dsn: str) -> None:
    reset_table()
    a = psycopg.connect(dsn, autocommit=True)
    b = psycopg.connect(dsn, autocommit=True)
    try:
        with a.cursor() as cur:
            cur.execute(
                f"UPDATE {TABLE} SET value = value + 1 WHERE id = 1 RETURNING value",
                prepare=True,
            )
            print(label, "update", cur.fetchone()[0], flush=True)

        with b.cursor() as cur:
            cur.execute("SET statement_timeout = '5s'")
            try:
                cur.execute(f"DELETE FROM {TABLE} WHERE id = 1 RETURNING id")
                print(label, "delete", cur.fetchone()[0], flush=True)
            except Exception as exc:
                print(label, "delete", f"{type(exc).__name__}: {exc}", flush=True)
    finally:
        b.close()
        a.close()


run("direct", DIRECT_DSN)
run("pgpool", PGPOOL_DSN)

Expected behavior

Both endpoints should commit the autocommitted UPDATE; the second client should delete the row successfully.

Actual behavior

The direct endpoint succeeds. Through Pgpool, the UPDATE returns successfully, but the second client waits on the row lock and receives a statement-timeout error:

direct update 11
direct delete 1
pgpool update 11
pgpool delete QueryCanceled: canceling statement due to statement timeout
CONTEXT:  while deleting tuple (0,1) in relation "scm_pgpool_txn_probe"

[koshino-235]

Thank you for the detailed report and reproducer.
I've attached a patch to fix this issue. Internal transactions are not needed in raw mode, so the patch prevents Pgpool-II from starting them in that mode.
Could you please test the patch and confirm that it resolves the issue?

v1-0001-Fix-inconsistent-internal-transaction-handling-in).patch

[apkipa]

Thanks for the fix, I've verified this resolves the issue.