dbeaver标签保持着’空闲事务中’的事务

huangapple go评论47阅读模式
英文:

dbeaver tab holding 'idle in transaction' transaction

问题

我看到DBeaver选项卡有时会保存一个Postgres事务ID一小时或更长时间。该选项卡设置为'手动提交 - 读已提交',且100%没有未提交的工作。我还看到这不是来自选项卡,而是来自DBeaver称为'Main'的地方。

pg_stat_activity中,事务看起来如下,你可以看到会话处于'idle in transaction'状态。你可以看到backend_xmin已设置,而且查询了pg_catalog.pg_proc,这绝对不是用户执行的。从'Main'中,我看到它在'SHOW TRANSACTION ISOLATION LEVEL'上是'空闲中的事务'。当在选项卡中点击'回滚'时,'空闲中的事务'会立即变为'空闲'状态。

我不想为该用户设置服务器端的idle_in_transaction_session_timeout超时。我已经在DBeaver中设置了'自动结束长时间空闲事务'。如果DBeaver定期发出此类查询,那么即使设置了服务器端的idle_in_transaction_session_timeout,也可能无法解决此问题,因为超时永远不会生效。

如何防止DBeaver保持事务处于打开状态,以使backend_xminbackend_xid过时并危及自动清理工作?

名称            |值                                                                                      |
----------------+----------------------------------------------------------------------------------------+
datid           |16417                                                                                   |
datname         |<removed>                                                                          |
pid             |18974                                                                                   |
leader_pid      |                                                                                         |
usesysid        |16394                                                                                   |
usename         |sys                                                                                     |
application_name|DBeaver - SQLEditor <Script-10.sql>                                           |
client_addr     |10.135.31.67                                                                            |
client_hostname |                                                                                         |
client_port     |65397                                                                                   |
backend_start   |2023-02-08 12:12:45.098 +0000                                                          |
xact_start      |2023-02-08 12:16:23.121 +0000                                                          |
query_start     |2023-02-08 12:16:23.676 +0000                                                          |
state_change    |2023-02-08 12:16:23.677 +0000                                                          |
wait_event_type |Client                                                                                   |
wait_event      |ClientRead                                                                               |
state           |空闲中的事务                                                                            |
backend_xid     |                                                                                         |
backend_xmin    |222236770                                                                                |
query_id        |                                                                                         |
query           |SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2) ORDER BY pp.proname LIMIT 10|
backend_type    |客户端后端
英文:

I am seeing that dbeaver tabs sometimes hold a postgres transaction ID for 1 hour or more. The tab is set to 'manual commit - read committed' and 100% shows NO uncommited work. I have also seen this not coming from a tab but from what Dbeaver calls 'Main'.

The transaction in pg_stat_activity looks like below, you can see the session is idle in transaction. You can see that a backend_xmin is set and that pg_catalog.pg_proc was queried which was 100% not done by the user. From 'Main' I have seen it being idle in transaction on SHOW TRANSACTION ISOLATION LEVEL. When clicking rollback in the tab, the idle in transaction session immediately goes idle.

I do not want to set a server side idle_in_transaction_session_timeout timeout for this user. I have already set 'automatically end long idle transactions' in dbeaver. Setting a server side idle_in_transaction_session_timeout might not even fix this if dbeaver periodically issues such queries, then the timeout never kicks in.

How can I prevent dbeaver from holding transactions open so that backend_xmin or backend_xid get old and endanger autovacuum work?

Name            |Value                                                                                                                                    |
----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
datid           |16417                                                                                                                                    |
datname         |<removed>                                                                                                                                 |
pid             |18974                                                                                                                                    |
leader_pid      |                                                                                                                                         |
usesysid        |16394                                                                                                                                    |
usename         |sys                                                                                                                                      |
application_name|DBeaver - SQLEditor <Script-10.sql>                                                                                      |
client_addr     |10.135.31.67                                                                                                                             |
client_hostname |                                                                                                                                         |
client_port     |65397                                                                                                                                    |
backend_start   |2023-02-08 12:12:45.098 +0000                                                                                                            |
xact_start      |2023-02-08 12:16:23.121 +0000                                                                                                            |
query_start     |2023-02-08 12:16:23.676 +0000                                                                                                            |
state_change    |2023-02-08 12:16:23.677 +0000                                                                                                            |
wait_event_type |Client                                                                                                                                   |
wait_event      |ClientRead                                                                                                                               |
state           |idle in transaction                                                                                                                      |
backend_xid     |                                                                                                                                         |
backend_xmin    |222236770                                                                                                                                |
query_id        |                                                                                                                                         |
query           |SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2) ORDER BY pp.proname LIMIT 10|
backend_type    |client backend

答案1

得分: 0

DBeaver团队表示此问题已解决 - 请查看https://github.com/dbeaver/dbeaver/issues/8303。

英文:

DBeaver team says this is fixed now - See https://github.com/dbeaver/dbeaver/issues/8303

huangapple
  • 本文由 发表于 2023年2月8日 21:12:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/75386312.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定