从源码看pg_show_plans的修复
前言
在8.24号，我提交了一个关于pg_show_plans插件的bug之后，在今天的下午一点左右，该bug修复了，suzuki大师牛逼。
详见：https://github.com/cybertec-postgresql/pg_show_plans/issues/20
经测试，可以正常使用了，普通的查询不会消耗永久事务ID了。
一探究竟
本着负责（头铁）的态度，想看一下是怎么实现的
可以看到，在最新的版本里面，引入了一个新的参数：pg_show_plans.enable_txid :
It controls whether txid is used as a hash key. Default is 'false'. If true, the pg_show_plan function can efficiently process the GC, however, it has the side effect of consuming txid whenever execute a query, even if it doesn't really need it like SELECT 1. This was introduced to be used when trying to reproduce past behavior. Therefore, it is not recommended to set this true.
该参数用于控制是否使用txid作为Hash key，默认是false，如果为true，pg_show_plan函数可以有效地处理 GC，但是，副作用便是会消耗一个永久事务ID，即便是select 1，不建议将此设置为 true，true的话就如之前的情况一样，普通查询会消耗永久事务ID，这会导致事务ID的消耗加剧。
大概看了一下代码，代码改动记录不过二十几行，里面主要添加了一个判断
if (!RecoveryInProgress()) ---原来的
if (!RecoveryInProgress() && pgsp_enable_txid) ---现在的
e->topxid = GetTopTransactionId();
else
/*
* In recovery mode, we use pid as the key instead of txid for garbage
* collection.
* In recovery mode or if pgsp_enable_txid is false, we use pid as the
* key instead of txid for garbage collection.
*/
e->topxid = (uint32) key.pid;
可以看到，逻辑很简单，pgsp_enable_txid默认是false，所以走到了下面一个分支，即e->topxid = (uint32) key.pid，可见，新版本里面使用了进程pid作为了hash key。另外，这里也可以看出，假如是在备库的话，也是走的下面一个逻辑，于是乎，有了这个样板，新版本的修复就简单了，直接复用一下这个逻辑，让他走到这个分支即可，也正是pgsp_enable_txid这个变量来做的事。
至于为什么说提到使用txid可以更加有效的GC，可以看如下代码，假如是true的话，也就是老版本的行为，那么就会走如下的逻辑
if (!RecoveryInProgress() && pgsp_enable_txid)
{
if (TransactionIdDidCommit(entry->topxid)
|| TransactionIdDidAbort(entry->topxid))
{
LWLockRelease(pgsp->lock);
LWLockAcquire(pgsp->lock, LW_EXCLUSIVE);
hash_search(pgsp_hash, &entry->key, HASH_REMOVE, NULL);
LWLockRelease(pgsp->lock);
LWLockAcquire(pgsp->lock, LW_SHARED);
continue;
}
}
在事务提交或事务回滚之后，将项删除，函数hash_search根据传入的第三个参数决定做何种动作
/*
* hash_search -- look up key in table and perform action
* hash_search_with_hash_value -- same, with key's hash value already computed
*
* action is one of:
* HASH_FIND: look up key in table
* HASH_ENTER: look up key in table, creating entry if not present
* HASH_ENTER_NULL: same, but return NULL if out of memory
* HASH_REMOVE: look up key in table, remove entry if present
*
* Return value is a pointer to the element found/entered/removed if any,
* or NULL if no match was found. (NB: in the case of the REMOVE action,
* the result is a dangling pointer that shouldn't be dereferenced!)
*
* HASH_ENTER will normally ereport a generic "out of memory" error if
* it is unable to create a new entry. The HASH_ENTER_NULL operation is
* the same except it will return NULL if out of memory. Note that
* HASH_ENTER_NULL cannot be used with the default palloc-based allocator,
* since palloc internally ereports on out-of-memory.
*
* If foundPtr isn't NULL, then *foundPtr is set true if we found an
* existing entry in the table, false otherwise. This is needed in the
* HASH_ENTER case, but is redundant with the return value otherwise.
*
* For hash_search_with_hash_value, the hashvalue parameter must have been
* calculated with get_hash_value().
*/
void *
hash_search(HTAB *hashp,
const void *keyPtr,
HASHACTION action,
bool *foundPtr)
{
假如pgsp_enable_txid为false，也就是之前的情况，会走下面一个分支，通过pid而不是txid来进行判断，进程结束后或者进程处于空闲的状态进行GC，可以想象，假如某一个pid有loop循环，或者不断进行繁忙的事务操作，那么空闲的时间时间很短，来不及做GC。所以说GC的效率没有使用txid的效率高，毕竟前面一个分支是每个事务结束之后就立马GC
else
{
/*
* In recovery mode, we cannot use txid. Therefore, we check
* whether the pid of the entry is still running and the state of
* the pid is active in each entry.
*
* If the pid of the entry does not already exist, the entry has
* to be removed. And the pid of the entry still exists but the
* state of the pid is not active, we have to also remove the
* entry because it is a garbege plan created by canceling the
* previous transaction.
*
* This part is not efficient because of a for-loop, however, we
* do not care about it because this function is not often
* executed.
*/
intnum_backends = pgstat_fetch_stat_numbackends();
intcurr_backend;
uint32pid = (uint32) entry->topxid;
boolexists = false;
for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
{
LocalPgBackendStatus *local_beentry;
PgBackendStatus *beentry;
local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
beentry = &local_beentry->backendStatus;
if (beentry->st_procpid == pid && beentry->st_state == STATE_RUNNING)
{
exists = true;
break;
}
}
if (!exists)
{
LWLockRelease(pgsp->lock);
LWLockAcquire(pgsp->lock, LW_EXCLUSIVE);
hash_search(pgsp_hash, &entry->key, HASH_REMOVE, NULL);
LWLockRelease(pgsp->lock);
LWLockAcquire(pgsp->lock, LW_SHARED);
continue;
}
}
这个插件也是利用了HOOK
/*
* ExecutorStart hook: start up tracking if needed
*/
static void
pgsp_ExecutorStart(QueryDesc *queryDesc, int eflags)
{
#if PG_VERSION_NUM >= 90500
ExplainState *es = NewExplainState();
#else
ExplainState es;
#endif
在PostgreSQL源码里面，ExecutorStart就是一个壳
void
ExecutorStart(QueryDesc *queryDesc, int eflags)
{
if (ExecutorStart_hook) //如果定义了这个HOOK，那么代码就会跳转到这里执行
(*ExecutorStart_hook) (queryDesc, eflags);
else
standard_ExecutorStart(queryDesc, eflags);
}
pg_show_plans利用的HOOK
ExecutorStart_hook = pgsp_ExecutorStart
PostgreSQL还提供了许多其他的HOOK，所以利用好这些HOOK，我们可以很容易"侵入"，让PostgreSQL按照我们的逻辑跑。
ExecutorStart_hook
ExecutorRun_hook
ExecutorFinish_hook
ExecutorEnd_hook
ProcessUtility_hook
set_rel_pathlist_hook
set_join_pathlist_hook
create_upper_paths_hook
post_parse_analyze_hook
....
小结
至此，明了了，逻辑还算简单，能分析个七七八八所以然来。
又可以开心地使用pg_show_plans了！
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
