Hi, hackers
I see [1] has already implemented on login event trigger, why not implement
the logoff event trigger?
My friend Song Jinzhou and I try to implement the logoff event trigger, so
attach it.
Here is a problem with the regression test when using \c to create a new
session, because it might be running concurrently, which may lead to the
checking being unstable.
Any thoughts?
[1]
https://www.postgresql.org/message-id/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru
--
Regards,
Japin Li
>From ef7c6aa408d0a6a97d7b0d2e093b71e279b1b0dc Mon Sep 17 00:00:00 2001
From: Japin Li <japi...@hotmail.com>
Date: Mon, 15 Apr 2024 09:11:41 +0800
Subject: [PATCH v1 1/1] Add support event triggers for logoff
---
doc/src/sgml/catalogs.sgml | 13 ++
doc/src/sgml/ecpg.sgml | 2 +
doc/src/sgml/event-trigger.sgml | 5 +
src/backend/commands/dbcommands.c | 17 +-
src/backend/commands/event_trigger.c | 181 +++++++++++++++++-
src/backend/tcop/postgres.c | 8 +
src/backend/utils/cache/evtcache.c | 2 +
src/backend/utils/init/globals.c | 1 +
src/backend/utils/init/postinit.c | 1 +
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_database.dat | 2 +-
src/include/catalog/pg_database.h | 3 +
src/include/commands/event_trigger.h | 1 +
src/include/miscadmin.h | 1 +
src/include/tcop/cmdtaglist.h | 1 +
src/include/utils/evtcache.h | 1 +
.../regress/expected/event_trigger_logoff.out | 49 +++++
src/test/regress/parallel_schedule | 2 +
src/test/regress/sql/event_trigger_logoff.sql | 26 +++
19 files changed, 309 insertions(+), 9 deletions(-)
create mode 100644 src/test/regress/expected/event_trigger_logoff.out
create mode 100644 src/test/regress/sql/event_trigger_logoff.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2907079e2a..621fbfde98 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3076,6 +3076,19 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dathaslogoffevt</structfield> <type>bool</type>
+ </para>
+ <para>
+ Indicates that there are logoff event triggers defined for this database.
+ This flag is used to avoid extra lookups on the
+ <structname>pg_event_trigger</structname> table during each backend
+ startup. This flag is used internally by <productname>PostgreSQL</productname>
+ and should not be manually altered or read for monitoring purposes.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>datconnlimit</structfield> <type>int4</type>
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index e7a53f3c9d..d223843157 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4765,6 +4765,7 @@ encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
dathasloginevt = f (type: 1)
+dathaslogoffevt = f (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
@@ -4790,6 +4791,7 @@ encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
dathasloginevt = f (type: 1)
+dathaslogoffevt = f (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 8e009cca05..ed996a456e 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -29,6 +29,7 @@
occurs in the database in which it is defined. Currently, the only
supported events are
<literal>login</literal>,
+ <literal>logoff</literal>,
<literal>ddl_command_start</literal>,
<literal>ddl_command_end</literal>,
<literal>table_rewrite</literal>
@@ -54,6 +55,10 @@
the in-progress <literal>login</literal> trigger.
</para>
+ <para>
+ The <literal>logoff</literal> event occurs when a user logs off the system.
+ </para>
+
<para>
The <literal>ddl_command_start</literal> event occurs just before the
execution of a <literal>CREATE</literal>, <literal>ALTER</literal>, <literal>DROP</literal>,
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 8229dfa1f2..a6784a0ea6 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -116,7 +116,7 @@ static void movedb_failure_callback(int code, Datum arg);
static bool get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
- TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
+ bool *dbHasLogoffEvtP, TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbLocale,
char **dbIcurules,
char *dbLocProvider,
@@ -680,6 +680,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
char *src_collversion = NULL;
bool src_istemplate;
bool src_hasloginevt = false;
+ bool src_haslogoffevt = false;
bool src_allowconn;
TransactionId src_frozenxid = InvalidTransactionId;
MultiXactId src_minmxid = InvalidMultiXactId;
@@ -981,7 +982,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
if (!get_db_info(dbtemplate, ShareLock,
&src_dboid, &src_owner, &src_encoding,
&src_istemplate, &src_allowconn, &src_hasloginevt,
- &src_frozenxid, &src_minmxid, &src_deftablespace,
+ &src_haslogoffevt, &src_frozenxid, &src_minmxid, &src_deftablespace,
&src_collate, &src_ctype, &src_locale, &src_icurules, &src_locprovider,
&src_collversion))
ereport(ERROR,
@@ -1425,6 +1426,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(dbistemplate);
new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(dballowconnections);
new_record[Anum_pg_database_dathasloginevt - 1] = BoolGetDatum(src_hasloginevt);
+ new_record[Anum_pg_database_dathaslogoffevt - 1] = BoolGetDatum(src_haslogoffevt);
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
new_record[Anum_pg_database_datminmxid - 1] = TransactionIdGetDatum(src_minmxid);
@@ -1653,7 +1655,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
- &db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
+ &db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
{
if (!missing_ok)
{
@@ -1868,7 +1870,7 @@ RenameDatabase(const char *oldname, const char *newname)
rel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
+ NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist", oldname)));
@@ -1978,7 +1980,7 @@ movedb(const char *dbname, const char *tblspcname)
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
- NULL, NULL, NULL, NULL, &src_tblspcoid, NULL, NULL, NULL, NULL, NULL, NULL))
+ NULL, NULL, NULL, NULL, NULL, &src_tblspcoid, NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist", dbname)));
@@ -2760,7 +2762,7 @@ static bool
get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
- TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
+ bool *dbHasLogoffEvtP, TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbLocale,
char **dbIcurules,
char *dbLocProvider,
@@ -2847,6 +2849,9 @@ get_db_info(const char *name, LOCKMODE lockmode,
/* Has on login event trigger? */
if (dbHasLoginEvtP)
*dbHasLoginEvtP = dbform->dathasloginevt;
+ /* Has on logoff event trigger? */
+ if (dbHasLogoffEvtP)
+ *dbHasLogoffEvtP = dbform->dathaslogoffevt;
/* allowing connections? */
if (dbAllowConnP)
*dbAllowConnP = dbform->datallowconn;
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 7a5ed6b985..1adbd4ea12 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -112,6 +112,7 @@ static void EventTriggerInvoke(List *fn_oid_list, EventTriggerData *trigdata);
static const char *stringify_grant_objtype(ObjectType objtype);
static const char *stringify_adefprivs_objtype(ObjectType objtype);
static void SetDatabaseHasLoginEventTriggers(void);
+static void SetDatabaseHasLogoffEventTriggers(void);
/*
* Create an event trigger.
@@ -143,6 +144,7 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
strcmp(stmt->eventname, "ddl_command_end") != 0 &&
strcmp(stmt->eventname, "sql_drop") != 0 &&
strcmp(stmt->eventname, "login") != 0 &&
+ strcmp(stmt->eventname, "logoff") != 0 &&
strcmp(stmt->eventname, "table_rewrite") != 0)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -179,6 +181,10 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("tag filtering is not supported for login event triggers")));
+ else if (strcmp(stmt->eventname, "logoff") == 0 && tags != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("tag filtering is not supported for logoff event triggers")));
/*
* Give user a nice error message if an event trigger of the same name
@@ -317,6 +323,13 @@ insert_event_trigger_tuple(const char *trigname, const char *eventname, Oid evtO
if (strcmp(eventname, "login") == 0)
SetDatabaseHasLoginEventTriggers();
+ /*
+ * Logoff event triggers have an additional flag in pg_database to enable
+ * faster lookups in hot codepaths. Set the flag unless already True.
+ */
+ if (strcmp(eventname, "logoff") == 0)
+ SetDatabaseHasLogoffEventTriggers();
+
/* Depend on owner. */
recordDependencyOnOwner(EventTriggerRelationId, trigoid, evtOwner);
@@ -413,6 +426,44 @@ SetDatabaseHasLoginEventTriggers(void)
heap_freetuple(tuple);
}
+/*
+ * 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;
+ }
+ table_close(pg_db, RowExclusiveLock);
+ heap_freetuple(tuple);
+}
+
/*
* ALTER EVENT TRIGGER foo ENABLE|DISABLE|ENABLE ALWAYS|REPLICA
*/
@@ -455,6 +506,14 @@ AlterEventTrigger(AlterEventTrigStmt *stmt)
tgenabled != TRIGGER_DISABLED)
SetDatabaseHasLoginEventTriggers();
+ /*
+ * Logoff event triggers have an additional flag in pg_database to enable
+ * faster lookups in hot codepaths. Set the flag unless already True.
+ */
+ if (namestrcmp(&evtForm->evtevent, "logoff") == 0 &&
+ tgenabled != TRIGGER_DISABLED)
+ SetDatabaseHasLogoffEventTriggers();
+
InvokeObjectPostAlterHook(EventTriggerRelationId,
trigoid, 0);
@@ -618,6 +677,8 @@ EventTriggerGetTag(Node *parsetree, EventTriggerEvent event)
{
if (event == EVT_Login)
return CMDTAG_LOGIN;
+ else if (event == EVT_Logoff)
+ return CMDTAG_LOGOFF;
else
return CreateCommandTag(parsetree);
}
@@ -660,7 +721,8 @@ EventTriggerCommonSetup(Node *parsetree,
if (event == EVT_DDLCommandStart ||
event == EVT_DDLCommandEnd ||
event == EVT_SQLDrop ||
- event == EVT_Login)
+ event == EVT_Login ||
+ event == EVT_Logoff)
{
if (!command_tag_event_trigger_ok(dbgtag))
elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(dbgtag));
@@ -998,6 +1060,123 @@ EventTriggerOnLogin(void)
CommitTransactionCommand();
}
+/*
+ * 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();
+}
/*
* Fire table_rewrite triggers.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 76f48b13d2..0b12a1da4b 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4314,6 +4314,14 @@ 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);
+
+
/*
* POSTGRES main processing loop begins here
*
diff --git a/src/backend/utils/cache/evtcache.c b/src/backend/utils/cache/evtcache.c
index 185b52e669..803175bd03 100644
--- a/src/backend/utils/cache/evtcache.c
+++ b/src/backend/utils/cache/evtcache.c
@@ -168,6 +168,8 @@ BuildEventTriggerCache(void)
event = EVT_TableRewrite;
else if (strcmp(evtevent, "login") == 0)
event = EVT_Login;
+ else if (strcmp(evtevent, "logoff") == 0)
+ event = EVT_Logoff;
else
continue;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index cc61937eef..79f45b6bbf 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -93,6 +93,7 @@ Oid MyDatabaseId = InvalidOid;
Oid MyDatabaseTableSpace = InvalidOid;
bool MyDatabaseHasLoginEventTriggers = false;
+bool MyDatabaseHasLogoffEventTriggers = false;
/*
* DatabasePath is the path (relative to DataDir) of my database's
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 0805398e24..fc2944e027 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1114,6 +1114,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
MyDatabaseTableSpace = datform->dattablespace;
MyDatabaseHasLoginEventTriggers = datform->dathasloginevt;
+ MyDatabaseHasLogoffEventTriggers = datform->dathaslogoffevt;
/* pass the database name back to the caller */
if (out_dbname)
strcpy(out_dbname, dbname);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6fee3160f0..03a114dce4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3623,7 +3623,7 @@ psql_completion(const char *text, int start, int end)
/* Complete CREATE EVENT TRIGGER <name> ON with event_type */
else if (Matches("CREATE", "EVENT", "TRIGGER", MatchAny, "ON"))
COMPLETE_WITH("ddl_command_start", "ddl_command_end", "login",
- "sql_drop", "table_rewrite");
+ "logoff", "sql_drop", "table_rewrite");
/*
* Complete CREATE EVENT TRIGGER <name> ON <event_type>. EXECUTE FUNCTION
diff --git a/src/include/catalog/pg_database.dat b/src/include/catalog/pg_database.dat
index c2ba636f8d..3402b35ddd 100644
--- a/src/include/catalog/pg_database.dat
+++ b/src/include/catalog/pg_database.dat
@@ -16,7 +16,7 @@
descr => 'default template for new databases',
datname => 'template1', encoding => 'ENCODING',
datlocprovider => 'LOCALE_PROVIDER', datistemplate => 't',
- datallowconn => 't', dathasloginevt => 'f', datconnlimit => '-1', datfrozenxid => '0',
+ datallowconn => 't', dathasloginevt => 'f', dathaslogoffevt => 'f', datconnlimit => '-1', datfrozenxid => '0',
datminmxid => '1', dattablespace => 'pg_default', datcollate => 'LC_COLLATE',
datctype => 'LC_CTYPE', datlocale => 'DATLOCALE',
daticurules => 'ICU_RULES', datacl => '_null_' },
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index dbd4379ffa..98bfdb6efa 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -52,6 +52,9 @@ CATALOG(pg_database,1262,DatabaseRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID
/* database has login event triggers? */
bool dathasloginevt;
+ /* database has logoff event triggers? */
+ bool dathaslogoffevt;
+
/*
* Max connections allowed. Negative values have special meaning, see
* DATCONNLIMIT_* defines below.
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index 90fc1af5f6..ff153347c8 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -57,6 +57,7 @@ extern void EventTriggerDDLCommandEnd(Node *parsetree);
extern void EventTriggerSQLDrop(Node *parsetree);
extern void EventTriggerTableRewrite(Node *parsetree, Oid tableOid, int reason);
extern void EventTriggerOnLogin(void);
+extern void EventTriggerOnLogoff(int code, Datum arg);
extern bool EventTriggerBeginCompleteQuery(void);
extern void EventTriggerEndCompleteQuery(void);
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 90f9b21b25..62b190211f 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -207,6 +207,7 @@ extern PGDLLIMPORT Oid MyDatabaseId;
extern PGDLLIMPORT Oid MyDatabaseTableSpace;
extern PGDLLIMPORT bool MyDatabaseHasLoginEventTriggers;
+extern PGDLLIMPORT bool MyDatabaseHasLogoffEventTriggers;
/*
* Date/Time Configuration
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 7fdcec6dd9..0908c549cd 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -187,6 +187,7 @@ PG_CMDTAG(CMDTAG_LISTEN, "LISTEN", false, false, false)
PG_CMDTAG(CMDTAG_LOAD, "LOAD", false, false, false)
PG_CMDTAG(CMDTAG_LOCK_TABLE, "LOCK TABLE", false, false, false)
PG_CMDTAG(CMDTAG_LOGIN, "LOGIN", true, false, false)
+PG_CMDTAG(CMDTAG_LOGOFF, "LOGOFF", true, false, false)
PG_CMDTAG(CMDTAG_MERGE, "MERGE", false, false, true)
PG_CMDTAG(CMDTAG_MOVE, "MOVE", false, false, true)
PG_CMDTAG(CMDTAG_NOTIFY, "NOTIFY", false, false, false)
diff --git a/src/include/utils/evtcache.h b/src/include/utils/evtcache.h
index 573e1de406..af297d3a93 100644
--- a/src/include/utils/evtcache.h
+++ b/src/include/utils/evtcache.h
@@ -24,6 +24,7 @@ typedef enum
EVT_SQLDrop,
EVT_TableRewrite,
EVT_Login,
+ EVT_Logoff,
} EventTriggerEvent;
typedef struct
diff --git a/src/test/regress/expected/event_trigger_logoff.out b/src/test/regress/expected/event_trigger_logoff.out
new file mode 100644
index 0000000000..9e0928f964
--- /dev/null
+++ b/src/test/regress/expected/event_trigger_logoff.out
@@ -0,0 +1,49 @@
+-- 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
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 675c567617..e9c3d62f6d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,6 +130,8 @@ test: oidjoins event_trigger
# on-login event handling could catch connection of a concurrent test.
test: event_trigger_login
+test: event_trigger_logoff
+
# this test also uses event triggers, so likewise run it by itself
test: fast_default
diff --git a/src/test/regress/sql/event_trigger_logoff.sql b/src/test/regress/sql/event_trigger_logoff.sql
new file mode 100644
index 0000000000..3762cea31e
--- /dev/null
+++ b/src/test/regress/sql/event_trigger_logoff.sql
@@ -0,0 +1,26 @@
+-- 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);
+SELECT COUNT(*) FROM user_logoffs;
+\c
+SELECT pg_sleep(0.1);
+SELECT COUNT(*) FROM user_logoffs;
+
+-- Check dathaslogoffevt in system catalog
+SELECT dathaslogoffevt FROM pg_database WHERE datname = :'DBNAME';
+
+-- Cleanup
+DROP TABLE user_logoffs;
+DROP EVENT TRIGGER on_logoff_trigger;
+DROP FUNCTION on_logoff_proc();
+\c
base-commit: 84db9a0eb10dd1dbee6db509c0e427fa237177dc
--
2.34.1
