There seems to be a session pooler transaction failure bug in UIB

Help & Support šŸ’» Bug Reports šŸž
1

Can a SQL Query step release a connection to a pool with an open or aborted transaction, allowing another userā€™s action to receive it?

Iā€™ve been testing approaches for standardizing action-scoped non-destructive sql crud operations and hit something that seems like a pretty nasty bug in UIB.

In an action, a sql query step with just begin; followed by a sql query step that had a syntax error left an open transaction which required a manual rollback; call in order to resolve.

While preparing the manual rollback;, a different user in a different app hit {"name":"Error","message":"[Code: 25P02]: current transaction is aborted, commands ignored until end of transaction block"} for all sql query steps until the manual rollback; statement was issued on my end.

I found that to be rather alarming because not only are valid queries being rejected by the transaction at that point, but if the initial query did not have the syntax error, I do not know whether or not transaction would have remained open and collected every query that followed until potentially the next rollback; would have been issues as part of testing, silently undoing changes that were valid.

Nothing substantial was lost due to it, but it certainly does not seem like the session pooler should be sharing unfinished transactions between sql query steps, much less different users entirely.

Hereā€™s some redacted event data from supabase for the initial syntax error query and the unrelated query from a different user being rejected:

[
    {
        "event_message": "current transaction is aborted, commands ignored until end of transaction block",
        "id": "<different_guid>",
        "log_attributes": {
            "host": "<sb_db_id>",
            "identifier": "<sb_pr_id>",
            "parsed.application_name": "Supavisor",
            "parsed.backend_type": "client backend",
            "parsed.command_tag": "UPDATE",
            "parsed.connection_from": "<identical_ipv6>",
            "parsed.database_name": "postgres",
            "parsed.error_severity": "ERROR",
            "parsed.process_id": "2905233",
            "parsed.query": "<an update query>",
            "parsed.query_id": "0",
            "parsed.session_id": "6ab545ab.2c5491",
            "parsed.session_line_num": "2",
            "parsed.session_start_time": "2026-09-24 15:45:47 UTC",
            "parsed.sql_state_code": "25P02",
            "parsed.timestamp": "2026-09-24 15:48:42.154 UTC",
            "parsed.transaction_id": "0",
            "parsed.user_name": "postgres",
            "parsed.virtual_transaction_id": "13/0",
            "project": "<sb_pr_id>"
        },
        "severity_text": "ERROR",
        "source": "postgres_logs",
        "timestamp": "2026-09-24T15:48:42.154000"
    },
    {
        "event_message": "syntax error at or near \"{\"",
        "id": "<different_guid>",
        "log_attributes": {
            "host": "<sb_db_id>",
            "identifier": "<sb_pr_id>",
            "parsed.application_name": "Supavisor",
            "parsed.backend_type": "client backend",
            "parsed.command_tag": "idle in transaction",
            "parsed.connection_from": "<identical_ipv6>",
            "parsed.database_name": "postgres",
            "parsed.error_severity": "ERROR",
            "parsed.process_id": "2905233",
            "parsed.query": "<an insert query>",
            "parsed.query_id": "0",
            "parsed.query_pos": "80",
            "parsed.session_id": "6ab545ab.2c5491",
            "parsed.session_line_num": "1",
            "parsed.session_start_time": "2026-09-24 15:45:47 UTC",
            "parsed.sql_state_code": "42601",
            "parsed.timestamp": "2026-09-24 15:48:11.970 UTC",
            "parsed.transaction_id": "0",
            "parsed.user_name": "postgres",
            "parsed.virtual_transaction_id": "13/136630",
            "project": "<sb_pr_id>"
        },
        "severity_text": "ERROR",
        "source": "postgres_logs",
        "timestamp": "2026-09-24T15:48:11.970000"
    }
]

The bottom entry was the initial error, the top entry was the unrelated query that got caught in the failed transaction somehow. I can tell you that the queries did not depend on the same tables nor schemas, but itā€™s possible there are triggers related to audit logging that might fire in both cases, if thatā€™s relevant. In total there were 17 unrelated queries that were rejected over the course of the incident (about 3 minutes) - 2 updates and 15 selects.

Could be supabase/supavisor related, but Iā€™m using the session pooler connection on :5432, so UIB seemed more likely as an initial culprit.

Hopefully this helps track down whatā€™s happening.

2

Hi @Audio

Thanks for the detailed report and logs. Weā€™ve confirmed an issue in UI Bakeryā€™s datasource connection pooling.

A database connection can currently return to the shared pool while it still has an open or failed transaction. A later query using the same datasource configuration may receive that connection and inherit its transaction state. This explains the 25P02 errors and the matching PostgreSQL session and process IDs in the supplied logs.

Supavisorā€™s session mode preserves the state of its client connection as expected; the problem is that UI Bakery reused that connection without first clearing the unfinished transaction.

As a workaround:

  • Donā€™t split BEGIN , transactional queries, and COMMIT or ROLLBACK across separate SQL Query steps.
  • Execute the complete transaction in a database function or stored procedure called from a single SQL Query step.
  • If a connection has already entered this state, restarting the UI Bakery datasource service or its connection pool will clear it. Sending a separate ROLLBACK may help, but it isnā€™t reliable because the pool could assign that query to another connection.

Iā€™ve passed the details to the dev team for further investigation.