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数据库之Support event trigger for logoff

文章快速说明索引
功能使用背景说明
补丁实现原理分析

文章快速说明索引

学习目标：

做数据库内核开发久了就会有一种少年得志，年少轻狂的错觉，然鹅细细一品觉得自己其实不算特别优秀远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起暂缓其他基于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 $$
BEGIN
  INSERT 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);
}