PostgreSQL-如何确定交易是否活跃? [英] PostgreSQL - how to determine whether a transaction is active?

问题描述

让我开放地说:是的，我知道确定是否交易处于活动状态(Postgres)

Let me open by saying: yes, I am aware of Determine if a transaction is active (Postgres)

不幸的是，该问题的唯一答案过于具体，无法提供所提供的用例，并且实际上并未指示事务是否处于活动状态.

Unfortunately the sole answer to that question is far too specific to the use case provided, and doesn't actually indicate whether or not a transaction is active.

建议的select txid_current();技巧如何检查PostgreSQL事务中的未决操作似乎不起作用-我总是从该函数的相邻调用中获得相同的事务ID.可能是因为我正在尝试从pgAdmin进行测试，而pgAdmin透明地开始了交易...? (注意:我实际上并不在乎是否有任何待处理的更改或活动锁，因此查看pg_locks并没有帮助-如果自事务开始以来什么都没碰到怎么办?)

The select txid_current(); trick suggested by How to check for pending operations in a PostgreSQL transaction doesn't appear to work - I always get the same transaction ID from adjacent calls to that function. Possibly this is because I'm trying to test it from pgAdmin, which is transparently starting transactions...? (Note: I don't actually care whether there are any pending changes or active locks, so looking at pg_locks isn't helpful - what if nothing's been touched since the transaction was started?)

因此:如何在PostgreSQL PL/pgSQL代码中确定事务当前是否处于活动状态?

一个可能用例是:有问题的SP/FN将执行其自己的显式事务管理，并且在已经激活的事务中调用它会极大地干扰这一点.我想提出一个错误，以便可以纠正在事务中调用此SP/FN的编码错误.

One possible use case is: the SP/FN in question will be doing its own explicit transaction management, and calling it with a transaction already active will greatly interfere with that. I want to raise an error so that the coding mistake of calling this SP/FN in a transaction can be corrected.

不过，还有其他用例.

理想情况下，我要寻找的是等效于MSSQL的@@TRANCOUNT(尽管我并不在乎交易的嵌套深度 ...)

Ideally what I'm looking for is an equivalent to MSSQL's @@TRANCOUNT (though I don't really care how deeply the transactions may be nested...)

推荐答案

如果要确定事务中是否有任何数据修改，请调用txid_current_if_assigned().如果尚未进行任何修改，它将返回NULL.

If you want to determine if there have been any data modifications in your transaction, call txid_current_if_assigned(). It returns NULL if nothing has been modified yet.

如果您只想知道自己是否在某笔交易中，则可以省去麻烦，因为您总是.

If you only want to know if you are inside some transaction, you can save yourself the trouble, because you always are.

在PostgreSQL v11之前，您不能在函数中使用事务控制语句.

Before PostgreSQL v11, you cannot use transaction control statements in a function.

这篇关于PostgreSQL-如何确定交易是否活跃?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！