As per my understanding, I can see that a transaction is holding a snapshot by either of the columns backend_xid or backend_xmin not being NULL in pg_stat_activity.
I am currently investigating cases where backend_xid is not null for sessions from dbeaver and I don't understand why the transaction is requiring a snapshot. This is of interest as long running transaction that are holding a snapshot can cause problems, for autovacuum for instance.
My question is: Can I (serverside) find the reason why a transaction is holding a snapshot? Is there a table where I can see why the transaction is holding a snapshot?
backend_xid is the transaction ID of the session and does not mean that the session has an active snapshot. The documentation says:
Top-level transaction identifier of this backend, if any.
backend_xmin is described as
The current backend's
xminhorizon.
“xmin horizon” is PostgreSQL jargon and refers to the lowest transaction ID that was active when the snapshot was taken. It is an upper limit of what VACUUM is allowed to remove.