PostgreSQL事务回卷实战案例详析（postcard造句）一篇读懂

文章摘要

这篇文章描述了如何配置事务ID（TransactionId）的限制，以防止事务ID溢出并触发自动真空（autovacuum）功能。主要内容包括： 1. **事务ID限制设置** - 通过`SetTransactionIdLimit`函数设置事务ID的上限，包括`xidWrapLimit`、`xidStopLimit`、`xidWarnLimit`和`xidVacLimit`。 - `xidWrapLimit`和`xidStopLimit`通过移位运算和溢出检查设置，确保其在合理范围内。 - `xidVacLimit`根据`autovacuum_freeze_max_age`参数设置。 2. **锁获取与配置** - 使用锁（LWLock）获取`XidGenLock`，并配置共享内存缓存中的事务ID相关变量。 3. **错误报告与处理** - 当事务ID超过设置上限时，触发错误报告和自动真空操作。 - 提供错误提示，帮助管理员解决事务ID溢出问题。文章的核心内容是通过事务ID限制机制，确保事务管理的稳定性，并在溢出时自动触发自动真空功能，避免数据丢失或系统崩溃。

void
SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
{
TransactionId xidVacLimit;
TransactionId xidWarnLimit;
TransactionId xidStopLimit;
TransactionId xidWrapLimit;
TransactionId curXid;

Assert(TransactionIdIsNormal(oldest_datfrozenxid));

xidWrapLimit=oldest_datfrozenxid + (MaxTransactionId >> 1);
if (xidWrapLimit < FirstNormalTransactionId)
xidWrapLimit +=FirstNormalTransactionId;

xidStopLimit=xidWrapLimit – 1000000;
if (xidStopLimit < FirstNormalTransactionId)
xidStopLimit -=FirstNormalTransactionId;

xidWarnLimit=xidStopLimit – 10000000;
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -=FirstNormalTransactionId;

xidVacLimit=oldest_datfrozenxid + autovacuum_freeze_max_age;
if (xidVacLimit < FirstNormalTransactionId)
xidVacLimit +=FirstNormalTransactionId;

LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
ShmemVariableCache->oldestXid=oldest_datfrozenxid;
ShmemVariableCache->xidVacLimit=xidVacLimit;
ShmemVariableCache->xidWarnLimit=xidWarnLimit;
ShmemVariableCache->xidStopLimit=xidStopLimit;
ShmemVariableCache->xidWrapLimit=xidWrapLimit;
ShmemVariableCache->oldestXidDB=oldest_datoid;
curXid=XidFromFullTransactionId(ShmemVariableCache->nextFullXid);
LWLockRelease(XidGenLock);

ereport(DEBUG1,
(errmsg(“transaction ID wrap limit is %u, limited by database with OID %u”,
xidWrapLimit, oldest_datoid)));

if (TransactionIdFollowsOrEquals(curXid, xidVacLimit) &&
IsUnderPostmaster && !InRecovery)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);

if (TransactionIdFollowsOrEquals(curXid, xidWarnLimit) && !InRecovery)
{
char *oldest_datname;

if (IsTransactionState())
oldest_datname=get_database_name(oldest_datoid);
else
oldest_datname=NULL;

if (oldest_datname)
ereport(WARNING,
(errmsg(“database “%s” must be vacuumed within %u transactions”,
oldest_datname,
xidWrapLimit – curXid),
errhint(“To avoid a database shutdown, execute a database-wide VACUUM in that database.\n”
“You might also need to commit or roll back old prepared transactions, or drop stale replication slots.”)));
else
ereport(WARNING,
(errmsg(“database with OID %u must be vacuumed within %u transactions”,
oldest_datoid,
xidWrapLimit – curXid),
errhint(“To avoid a database shutdown, execute a database-wide VACUUM in that database.\n”
“You might also need to commit or roll back old prepared transactions, or drop stale replication slots.”)));
}
}

bool
TransactionIdFollowsOrEquals(TransactionId id1, TransactionId id2)
{
int32 diff;
if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2))
return (id1 >=id2);

diff=(int32) (id1 – id2);
return (diff >=0);
}

FullTransactionId
GetNewTransactionId(bool isSubXact)
{

full_xid=ShmemVariableCache->nextFullXid;
xid=XidFromFullTransactionId(full_xid);

if (TransactionIdFollowsOrEquals(xid, ShmemVariableCache->xidVacLimit))
{
TransactionId xidWarnLimit=ShmemVariableCache->xidWarnLimit;
TransactionId xidStopLimit=ShmemVariableCache->xidStopLimit;
TransactionId xidWrapLimit=ShmemVariableCache->xidWrapLimit;
Oid oldest_datoid=ShmemVariableCache->oldestXidDB;

if (IsUnderPostmaster &&
TransactionIdFollowsOrEquals(xid, xidStopLimit))
{
char *oldest_datname=get_database_name(oldest_datoid);

if (oldest_datname)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg(“database is not accepting commands to avoid wraparound data loss in database “%s””,
oldest_datname),
errhint(“Stop the postmaster and vacuum that database in single-user mode.\n”
“You might also need to commit or roll back old prepared transactions, or drop stale replication slots.”)));

}