英文:
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_xmin或backend_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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论