Dirschel, Steve schrieb am 25.01.2023 um 20:36:
When I connect to the database through DBeaver with those 2 default settings changed and find that session in pg_stat_activity column xact_start is populated along with backend_xmin. Those get populated just by logging in.
As you found out in the log, the driver runs DbEaver run multiple SQL statements during the "log in". And the first query will start a transaction, but as autocommit is disabled, nothing will end that transaction.
The problem is users will connect using DBeaver and their sessions will sit idle.
Idle is not a problem, "idle in transaction" is.
It executes the exact same commands except when the 2 default DBeaver settings are changed to show the issue it is issuing a BEGIN and COMMIT around one block of code and then at another part of code it issues a BEGIN, runs some queries, and never issues a COMMIT.
Yes, that's how turning off autocommit works. As soon as a statement is sent through the JDBC driver, the driver will send a BEGIN to start the transaction, but the the application (or the user) is responsible to end it through a COMMIT (or ROLLBACK).
