PostgreSQL的学习心得和知识总结（一百四十三）|深入理解PostgreSQL数据库之Support event trigger for logoff

目录结构

---

注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往

---

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
5、本文内容基于PostgreSQL master源码开发而成

---

---

---

文章快速说明索引

学习目标：

做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。

---

学习内容：（详见目录）

1、深入理解PostgreSQL数据库之Support event trigger for logoff

---

学习时间：

2024年05月10日 23:32:16

---

学习产出：

1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习

---

注：下面我们所有的学习环境是Centos8+PostgreSQL master+Oracle19C+MySQL8.0

postgres=# select version();version                                                   
------------------------------------------------------------------------------------------------------------PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)postgres=##-----------------------------------------------------------------------------#SQL> select * from v$version;          BANNER        Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
BANNER_FULL	  Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0	
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
CON_ID 0#-----------------------------------------------------------------------------#mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.06 sec)mysql>

---

功能使用背景说明

前段时间PostgreSQL合入了Add support event triggers on authenticated login功能，可以看一下本人之前的博客：

• PostgreSQL的学习心得和知识总结（一百三十六）|深入理解PostgreSQL数据库之Add support event triggers on authenticated login，点击前往

于是我跟建平决定实现一版 logoff 的事件触发器，不过因为考虑的不是那么周全 外加社区的态度比较冷淡，该patch属于放弃项。

接下来本着开源分享的目的，我给大家详细介绍一下其使用和实现原理。对此感兴趣的小伙伴可以自行查看邮件列表：

• Support event trigger for logoff，点击前往

---

使用案例，如下：

-- src/test/regress/expected/event_trigger_logoff.out-- Logoff event triggers
CREATE TABLE user_logoffs(id serial, who text);
GRANT SELECT ON user_logoffs TO public;
CREATE FUNCTION on_logoff_proc() RETURNS event_trigger AS $$
BEGININSERT INTO user_logoffs (who) VALUES (SESSION_USER);
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER on_logoff_trigger ON logoff EXECUTE FUNCTION on_logoff_proc();
ALTER EVENT TRIGGER on_logoff_trigger ENABLE ALWAYS;
\c
-- Is it enough to wait 100ms to let the logoff event trigger execute?
SELECT pg_sleep(0.1);pg_sleep 
----------(1 row)SELECT COUNT(*) FROM user_logoffs;count 
-------1
(1 row)\c
SELECT pg_sleep(0.1);pg_sleep 
----------(1 row)SELECT COUNT(*) FROM user_logoffs;count 
-------2
(1 row)-- Check dathaslogoffevt in system catalog
SELECT dathaslogoffevt FROM pg_database WHERE datname = :'DBNAME';dathaslogoffevt 
-----------------t
(1 row)-- Cleanup
DROP TABLE user_logoffs;
DROP EVENT TRIGGER on_logoff_trigger;
DROP FUNCTION on_logoff_proc();
\c

---

补丁实现原理分析

注：此次的 patch 在实现上和 login 事件触发器非常类似，接下来 重点看一下核心逻辑即可。若有想了解更加详细的内容，请看本人之前的博客！

// src/backend/tcop/postgres.c/* ----------------------------------------------------------------* PostgresMain*	   postgres main loop -- all backends, interactive or otherwise loop here** dbname is the name of the database to connect to, username is the* PostgreSQL user name to be used for the session.** NB: Single user mode specific setup should go to PostgresSingleUserMain()* if reasonably possible.* ----------------------------------------------------------------*/
void
PostgresMain(const char *dbname, const char *username)
{.../* Fire any defined login event triggers, if appropriate */EventTriggerOnLogin();/** Register a callback to fire any defined logoff event triggers, if* appropriate.*/if (IsUnderPostmaster)before_shmem_exit(EventTriggerOnLogoff, 0);...
}

---

因为是logoff事件触发器，所以这里选择before_shmem_exit注册EventTriggerOnLogoff函数，其逻辑如下：

// src/backend/storage/ipc/ipc.c/* ----------------------------------------------------------------*		before_shmem_exit**		Register early callback to perform user-level cleanup,*		e.g. transaction abort, before we begin shutting down*		low-level subsystems.*		*		注册早期回调以执行用户级清理，例如 在我们开始关闭低级子系统之前，事务中止。* ----------------------------------------------------------------*/
void
before_shmem_exit(pg_on_exit_callback function, Datum arg)
{if (before_shmem_exit_index >= MAX_ON_EXITS)ereport(FATAL,(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),errmsg_internal("out of before_shmem_exit slots")));before_shmem_exit_list[before_shmem_exit_index].function = function;before_shmem_exit_list[before_shmem_exit_index].arg = arg;++before_shmem_exit_index;if (!atexit_callback_setup){atexit(atexit_callback);atexit_callback_setup = true;}
}

---

上述这些回调函数，具体调用的地方 如下：

// src/backend/storage/ipc/ipc.c/* ------------------* Run all of the on_shmem_exit routines --- but don't actually exit.* This is used by the postmaster to re-initialize shared memory and* semaphores after a backend dies horribly.  As with proc_exit(), we* remove each callback from the list before calling it, to avoid* infinite loop in case of error.*  * 运行所有 on_shmem_exit 例程 --- 但实际上并不退出* 后端严重死机后，postmaster 使用它来重新初始化共享内存和信号量* 与 proc_exit() 一样，我们在调用每个回调之前从列表中删除它，以避免出现错误时无限循环* ------------------*/
void
shmem_exit(int code)
{shmem_exit_inprogress = true;/** Call before_shmem_exit callbacks.** These should be things that need most of the system to still be up and* working, such as cleanup of temp relations, which requires catalog* access; or things that need to be completed because later cleanup steps* depend on them, such as releasing lwlocks.*/elog(DEBUG3, "shmem_exit(%d): %d before_shmem_exit callbacks to make",code, before_shmem_exit_index);while (--before_shmem_exit_index >= 0)before_shmem_exit_list[before_shmem_exit_index].function(code,before_shmem_exit_list[before_shmem_exit_index].arg);before_shmem_exit_index = 0;...
}

---

最后再看一下EventTriggerOnLogoff函数的实现，如下(该函数实现上类似于函数EventTriggerOnLogin)：

// src/backend/commands/event_trigger.c/** Fire logoff event triggers if any are present.  The dathaslogoffevt* pg_database flag is left unchanged when an event trigger is dropped to avoid* complicating the codepath in the case of multiple event triggers.  This* function will instead unset the flag if no trigger is defined.*/
void
EventTriggerOnLogoff(int code, Datum arg)
{List	   *runlist;EventTriggerData trigdata;/** See EventTriggerDDLCommandStart for a discussion about why event* triggers are disabled in single user mode or via a GUC.  We also need a* database connection (some background workers don't have it).*/if (!IsUnderPostmaster || !event_triggers ||!OidIsValid(MyDatabaseId) || !MyDatabaseHasLogoffEventTriggers)return;StartTransactionCommand();runlist = EventTriggerCommonSetup(NULL,EVT_Logoff, "logoff",&trigdata, false);if (runlist != NIL){/** Event trigger execution may require an active snapshot.*/PushActiveSnapshot(GetTransactionSnapshot());/* Run the triggers. */EventTriggerInvoke(runlist, &trigdata);/* Cleanup. */list_free(runlist);PopActiveSnapshot();}/** There is no active logoff event trigger, but our* pg_database.dathaslogoffevt is set. Try to unset this flag.  We use the* lock to prevent concurrent SetDatabaseHasLogoffEventTriggers(), but we* don't want to hang the connection waiting on the lock.  Thus, we are* just trying to acquire the lock conditionally.*/else if (ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId,0, AccessExclusiveLock)){/** The lock is held.  Now we need to recheck that logoff event triggers* list is still empty.  Once the list is empty, we know that even if* there is a backend which concurrently inserts/enables a logoff event* trigger, it will update pg_database.dathaslogoffevt *afterwards*.*/runlist = EventTriggerCommonSetup(NULL,EVT_Logoff, "logoff",&trigdata, true);if (runlist == NIL){Relation	pg_db = table_open(DatabaseRelationId, RowExclusiveLock);HeapTuple	tuple;Form_pg_database db;ScanKeyData key[1];SysScanDesc scan;/** Get the pg_database tuple to scribble on.  Note that this does* not directly rely on the syscache to avoid issues with* flattened toast values for the in-place update.*/ScanKeyInit(&key[0],Anum_pg_database_oid,BTEqualStrategyNumber, F_OIDEQ,ObjectIdGetDatum(MyDatabaseId));scan = systable_beginscan(pg_db, DatabaseOidIndexId, true,NULL, 1, key);tuple = systable_getnext(scan);tuple = heap_copytuple(tuple);systable_endscan(scan);if (!HeapTupleIsValid(tuple))elog(ERROR, "could not find tuple for database %u", MyDatabaseId);db = (Form_pg_database) GETSTRUCT(tuple);if (db->dathaslogoffevt){db->dathaslogoffevt = false;/** Do an "in place" update of the pg_database tuple.  Doing* this instead of regular updates serves two purposes. First,* that avoids possible waiting on the row-level lock. Second,* that avoids dealing with TOAST.** It's known that changes made by heap_inplace_update() may* be lost due to concurrent normal updates.  However, we are* OK with that.  The subsequent connections will still have a* chance to set "dathaslogoffevt" to false.*/heap_inplace_update(pg_db, tuple);}table_close(pg_db, RowExclusiveLock);heap_freetuple(tuple);}else{list_free(runlist);}}CommitTransactionCommand();
}

---

注：SetDatabaseHasLogoffEventTriggers有一处不同于SetDatabaseHasLoginEventTriggers，如下：

/** Set pg_database.dathaslogoffevt flag for current database indicating that* current database has on logoff event triggers.*/
void
SetDatabaseHasLogoffEventTriggers(void)
{/* Set dathaslogoffevt flag in pg_database */Form_pg_database db;Relation	pg_db = table_open(DatabaseRelationId, RowExclusiveLock);HeapTuple	tuple;/** Use shared lock to prevent a conflict with EventTriggerOnLogoff() trying* to reset pg_database.dathaslogoffevt flag.  Note, this lock doesn't* effectively blocks database or other objection.  It's just custom lock* tag used to prevent multiple backends changing* pg_database.dathaslogoffevt flag.*/LockSharedObject(DatabaseRelationId, MyDatabaseId, 0, AccessExclusiveLock);tuple = SearchSysCacheCopy1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));if (!HeapTupleIsValid(tuple))elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);db = (Form_pg_database) GETSTRUCT(tuple);if (!db->dathaslogoffevt){db->dathaslogoffevt = true;CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);CommandCounterIncrement();/* take effect for the current session */MyDatabaseHasLogoffEventTriggers = true; /* ----- here ----- */}table_close(pg_db, RowExclusiveLock);heap_freetuple(tuple);
}