目錄結(jié)構(gòu)

---

注：提前言明 本文借鑒了以下博主、書籍或網(wǎng)站的內(nèi)容，其列表如下：

1、參考書籍：《PostgreSQL數(shù)據(jù)庫內(nèi)核分析》
2、參考書籍：《數(shù)據(jù)庫事務(wù)處理的藝術(shù)：事務(wù)管理與并發(fā)控制》
3、PostgreSQL數(shù)據(jù)庫倉庫鏈接，點(diǎn)擊前往
4、日本著名PostgreSQL數(shù)據(jù)庫專家 鈴木啟修 網(wǎng)站主頁，點(diǎn)擊前往
5、參考書籍：《PostgreSQL中文手冊(cè)》
6、參考書籍：《PostgreSQL指南：內(nèi)幕探索》，點(diǎn)擊前往

---

1、本文內(nèi)容全部來源于開源社區(qū) GitHub和以上博主的貢獻(xiàn)，本文也免費(fèi)開源（可能會(huì)存在問題，評(píng)論區(qū)等待大佬們的指正）
2、本文目的：開源共享 拋磚引玉 一起學(xué)習(xí)
3、本文不提供任何資源 不存在任何交易 與任何組織和機(jī)構(gòu)無關(guān)
4、大家可以根據(jù)需要自行 復(fù)制粘貼以及作為其他個(gè)人用途，但是不允許轉(zhuǎn)載 不允許商用 （寫作不易，還請(qǐng)見諒 💖）
5、本文內(nèi)容基于PostgreSQL master源碼開發(fā)而成

---

---

---

文章快速說明索引

學(xué)習(xí)目標(biāo)：

做數(shù)據(jù)庫內(nèi)核開發(fā)久了就會(huì)有一種 少年得志，年少輕狂 的錯(cuò)覺，然鵝細(xì)細(xì)一品覺得自己其實(shí)不算特別優(yōu)秀 遠(yuǎn)遠(yuǎn)沒有達(dá)到自己想要的。也許光鮮的表面掩蓋了空洞的內(nèi)在，每每想到于此，皆有夜半臨淵如履薄冰之感。為了睡上幾個(gè)踏實(shí)覺，即日起 暫緩其他基于PostgreSQL數(shù)據(jù)庫的兼容功能開發(fā)，近段時(shí)間 將著重于學(xué)習(xí)分享Postgres的基礎(chǔ)知識(shí)和實(shí)踐內(nèi)幕。

---

學(xué)習(xí)內(nèi)容：（詳見目錄）

1、深入理解PostgreSQL數(shù)據(jù)庫之Support event trigger for logoff

---

學(xué)習(xí)時(shí)間：

2024年05月10日 23:32:16

---

學(xué)習(xí)產(chǎn)出：

1、PostgreSQL數(shù)據(jù)庫基礎(chǔ)知識(shí)回顧 1個(gè)
2、CSDN 技術(shù)博客 1篇
3、PostgreSQL數(shù)據(jù)庫內(nèi)核深入學(xué)習(xí)

---

注：下面我們所有的學(xué)習(xí)環(huán)境是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>

---

功能使用背景說明

前段時(shí)間PostgreSQL合入了Add support event triggers on authenticated login功能，可以看一下本人之前的博客：

• PostgreSQL的學(xué)習(xí)心得和知識(shí)總結(jié)（一百三十六）|深入理解PostgreSQL數(shù)據(jù)庫之Add support event triggers on authenticated login，點(diǎn)擊前往

于是我跟建平?jīng)Q定實(shí)現(xiàn)一版 logoff 的事件觸發(fā)器，不過因?yàn)榭紤]的不是那么周全 外加社區(qū)的態(tài)度比較冷淡，該patch屬于放棄項(xiàng)。

接下來本著開源分享的目的，我給大家詳細(xì)介紹一下其使用和實(shí)現(xiàn)原理。對(duì)此感興趣的小伙伴可以自行查看郵件列表：

• Support event trigger for logoff，點(diǎn)擊前往

---

使用案例，如下：

-- 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

---

補(bǔ)丁實(shí)現(xiàn)原理分析

注：此次的 patch 在實(shí)現(xiàn)上和 login 事件觸發(fā)器非常類似，接下來 重點(diǎn)看一下核心邏輯即可。若有想了解更加詳細(xì)的內(nèi)容，請(qǐng)看本人之前的博客！

// 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);...
}

---

因?yàn)槭莑ogoff事件觸發(fā)器，所以這里選擇before_shmem_exit注冊(cè)EventTriggerOnLogoff函數(shù)，其邏輯如下：

// 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.*		*		注冊(cè)早期回調(diào)以執(zhí)行用戶級(jí)清理，例如 在我們開始關(guān)閉低級(jí)子系統(tǒng)之前，事務(wù)中止。* ----------------------------------------------------------------*/
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;}
}

---

上述這些回調(diào)函數(shù)，具體調(diào)用的地方 如下：

// 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.*  * 運(yùn)行所有 on_shmem_exit 例程 --- 但實(shí)際上并不退出* 后端嚴(yán)重死機(jī)后，postmaster 使用它來重新初始化共享內(nèi)存和信號(hào)量* 與 proc_exit() 一樣，我們?cè)谡{(diào)用每個(gè)回調(diào)之前從列表中刪除它，以避免出現(xiàn)錯(cuò)誤時(shí)無限循環(huán)* ------------------*/
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函數(shù)的實(shí)現(xiàn)，如下(該函數(shù)實(shí)現(xiàn)上類似于函數(shù)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);
}