青岛有没有做网站的,监利县建设局网站,金耀网站建设,html5手机网站模板 下载目录结构 注#xff1a;提前言明 本文借鉴了以下博主、书籍或网站的内容#xff0c;其列表如下#xff1a; 1、参考书籍#xff1a;《PostgreSQL数据库内核分析》 2、参考书籍#xff1a;《数据库事务处理的艺术#xff1a;事务管理与并发控制》 3、PostgreSQL数据库仓库… 目录结构 注提前言明 本文借鉴了以下博主、书籍或网站的内容其列表如下 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数据库内核深入学习 注下面我们所有的学习环境是Centos8PostgreSQL masterOracle19CMySQL8.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 dont 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 dont 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* dont 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.** Its 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 doesnt* effectively blocks database or other objection. Its 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);
}
