如何解释 PosgreSQL txid_current() 值 [英] How to interpret PosgreSQL txid_current() value

问题描述

我有以下 psql 语句:

假设:初始 txid:a

select txid_current();---------------一个+1开始;插入选项卡(v1，v2)；插入选项卡(v3，v4)；犯罪;选择 txid_current();---------------一个+3

为什么我看到的交易 ID 是 a+3 不应该是 a+2?

txid_current 是如何工作的?

有没有什么有效的方法可以只返回当前的 txid 而不增加额外的增量?

解决方案

要理解的要点:

• 一切都在交易中.如果您没有使用 BEGIN 和 COMMIT(或 ROLLBACK)显式创建一个，则仅为该语句创建一个.

• 只读 SELECT 不会获得完整的交易 ID，它们只会获得虚拟交易 ID.因此，即使是交易，SELECT 1; 或任何不会增加交易 ID 计数器的值.

• 调用 txid_current() 强制分配事务 ID(如果尚未分配).因此，只读事务现在将具有事务 ID，而以前没有.

当然，txid 也是跨会话分配的.在实践中，如果数据库繁忙，您上面的示例可能会得到 a+1 和 a+429 的 txid.

在应用程序级别将事务 ID 用于任何事情通常是不明智的.特别是:

将xmin 和xmax 视为内部系统级字段，将txid_current() 的结果视为无意义的数值.>

有关 xid 正确和错误用法的详细信息

特别是你不应该:

• 按数值比较 xids 以得出有关其排序的任何类型的结论；
• 添加或减去交易 ID；
• 对交易 ID 进行排序；
• 增加或减少交易 ID
• 将 32 位 xid 类型字段与 64 位 bigint epoch-extended xid 进行比较，即使相等也是如此.

因此，从应用程序的角度来看，xid 既不是单调的，也不是有序的.

您可以安全地:

• 比较两个 64 位 epoch-extended xid 是否相等或不相等；和
• 将 xid 传递给 txid_status(...) 和其他记录为采用 xid 的函数

注意:PostgreSQL 使用 32 位窄 xid，如 xid 类型，64 位 epoch-extended xid 通常表示为 bigint，如 返回的那些txid_current().比较它们的相等性通常似乎适用于新的数据库安装，但是一旦第一个 epoch 环绕发生，它们将不再相等.Pg 甚至没有给你一个简单的方法来查看 SQL 级别的 xid 纪元；你必须:

select (txid_current() >> 32) AS xid_epoch;

获取txid_current()报告的纪元扩展xid的高32位.

所以……无论您想做什么，交易 ID 都可能不是正确的做法.

I have the below psql statements:

Assumption :initial txid: a

select txid_current();
----------------------
a+1

begin;
insert into tab( v1,v2);
insert into tab (v3,v4);
commit;

select txid_current();
----------------------
a+3

Why do I see the transaction ID as a+3 shouldn't it be a+2?

How does txid_current work?

Is there any effective way where I could only return the current txid without the additional increment ?

解决方案

Key points to understand:

• Everything is in a transaction. If you don't explicitly create one with BEGIN and COMMIT (or ROLLBACK) one is created for you just for that statement.

• Read-only SELECTs don't get a full transaction ID, they only get a virtual transaction ID. So even though it's a transaction, SELECT 1; or whatever doesn't increment the transaction ID counter.

• Calling txid_current() forces the allocation of a transaction ID if one wasn't already allocated. So a read-only transaction will now have a transaction ID, where it previously wouldn't.

Of course, txids are also allocated across sessions. In practice your example above might get txid's of a+1 and a+429 if the database is busy.

It's generally not wise to use the transaction ID for anything at the application level. In particular:

Treat xmin and xmax as internal system level fields, and treat the result of txid_current() as a meaningless numeric value.

Details on correct and incorrect uses for xids

In particular you should never:

• Compare xids by numeric value to draw any sort of conclusion about their ordering;
• Add or subtract transaction IDs;
• Sort transaction IDs;
• Increment or decrement transaction IDs
• Compare a 32-bit xid typed field with a 64-bit bigint epoch-extended xid, even for equality.

So from an application perspective xids are neither monotonic nor ordinal.

You can safely:

• compare two 64-bit epoch-extended xids for equality or inequality; and
• pass xids to txid_status(...) and other functions documented as taking an xid

Beware: PostgreSQL uses 32-bit narrow xids like the xid type, and 64-bit epoch-extended xids typically represented as bigint like those returned by txid_current(). Comparing these for equality will generally seem to work on a new database install, but once the first epoch wraparound has occurred and they'll no longer be equal. Pg doesn't even give you an easy way to see the xid epoch at the SQL level; you have to:

select (txid_current() >> 32) AS xid_epoch;

to get the upper 32 bits of the epoch-extended xid reported by txid_current().

So ... whatever you are trying to do, it's likely that the transaction ID is not the right way to do it.

这篇关于如何解释 PosgreSQL txid_current() 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！