>> 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. Yes, I agree with that. And if I set autocommit off in psql I see the same where a select will start a transaction. >> The problem is users will connect using DBeaver and their sessions >> will sit idle. >Idle is not a problem, "idle in transaction" is. >From my perspective "idle in transaction" isn't necessarily a problem >(although I don't like seeing sessions sitting like that for a long time). >The problem is when pg_stat_activity.backend_xmin is populated- that can >prevent autovacuum from cleaning up old records. Again, if I login to psql, >set auto commit off and run a select I see pg_stat_activity.xact_start >populated but pg_stat_activity.backend_xmin is NOT populated. So that >transaction from psql would not prevent autovacuum from cleaning up. But when >this happens through DBeaver not only is pg_stat_activity.xact_start populated >but pg_stat_activity.backend_xmin is also populated. My main question is what >could DBeaver be doing to get pg_stat_activity.backend_xmin populated? It >doesn't happen when running a test in psql. So at this point I'm unable to >duplicate this issue in psql running the same thing I think DBeaver is >running. Maybe if I asked the question a little differently- in psql if I >set autocommit off and run a select what else do I need to run to get >pg_stat_activity.backend_xmin populated through that session? Is there a >certain "type" of select I could run to get it populated? I know if I insert >or update or delete a row it will get populated but I also know DBeaver is not >executing an insert/update/delete. >> 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). Thanks
