From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: On login trigger: take three
Date: Fri, 4 Sep 2020 18:59:17 +0300
Message-ID: <3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru> (raw)
In-Reply-To: <TYAPR01MB299057DD3A4AA8CF2B896EEEFE2D0@TYAPR01MB2990.jpnprd01.prod.outlook.com>
References: <0d46d29f-4558-3af9-9c85-7774e14a7709@postgrespro.ru>
<TYAPR01MB299057DD3A4AA8CF2B896EEEFE2D0@TYAPR01MB2990.jpnprd01.prod.outlook.com>
Sorry, attached version of the patch is missing changes in one file.
Here is it correct patch.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:on_login_trigger-2.patch (text/x-patch, 35.0K) downloaddiff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgmlindex 289dd1d..b3c1fc2 100644--- a/doc/src/sgml/ref/create_trigger.sgml+++ b/doc/src/sgml/ref/create_trigger.sgml@@ -27,7 +27,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] }
- ON <replaceable class="parameter">table_name</replaceable>+ ON { <replaceable class="parameter">table_name</replaceable> | <replaceable class="parameter">database_name</replaceable> }
[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ]
@@ -41,6 +41,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable>
UPDATE [ OF <replaceable class="parameter">column_name</replaceable> [, ... ] ]
DELETE
TRUNCATE
+ CONNECTION
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgmlindex 4a0e746..22caf4c 100644--- a/doc/src/sgml/trigger.sgml+++ b/doc/src/sgml/trigger.sgml@@ -71,6 +71,23 @@
</para>
<para>
+ You can also define on connection trigger:+ <programlisting>+ CREATE EVENT TRIGGER mytrigger+ AFTER CONNECTION ON mydatabase+ EXECUTE {PROCEDURE | FUNCTION} myproc();+ </programlisting>+ On connection triggers can only be defined for self database.+ It can be only <literal>AFTER</literal> trigger,+ and may not contain <literal>WHERE</literal> clause or list of columns.+ Any number of triggers with unique names can be defined for the database.+ On connection triggers can be dropped by specifying name of the database:+ <programlisting>+ DROP TRIGGER mytrigger ON mydatabase;+ </programlisting>+ </para>++ <para>
The trigger function must be defined before the trigger itself can be
created. The trigger function must be declared as a
function taking no arguments and returning type <literal>trigger</literal>.
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.cindex 6dfe1be..da57951 100644--- a/src/backend/catalog/objectaddress.c+++ b/src/backend/catalog/objectaddress.c@@ -1433,11 +1433,23 @@ get_object_address_relobject(ObjectType objtype, List *object,
/* Extract relation name and open relation. */
relname = list_truncate(list_copy(object), nnames - 1);
- relation = table_openrv_extended(makeRangeVarFromNameList(relname),- AccessShareLock,- missing_ok);- reloid = relation ? RelationGetRelid(relation) : InvalidOid;+ address.objectId = InvalidOid;+ if (objtype == OBJECT_TRIGGER && list_length(relname) == 1)+ {+ reloid = get_database_oid(strVal(linitial(relname)), true);+ if (OidIsValid(reloid))+ address.objectId = get_trigger_oid(reloid, depname, true);+ }++ if (!OidIsValid(address.objectId))+ {+ relation = table_openrv_extended(makeRangeVarFromNameList(relname),+ AccessShareLock,+ missing_ok);++ reloid = relation ? RelationGetRelid(relation) : InvalidOid;+ }
switch (objtype)
{
@@ -1449,8 +1461,11 @@ get_object_address_relobject(ObjectType objtype, List *object,
break;
case OBJECT_TRIGGER:
address.classId = TriggerRelationId;
- address.objectId = relation ?- get_trigger_oid(reloid, depname, missing_ok) : InvalidOid;+ if (!OidIsValid(address.objectId))+ {+ address.objectId = relation ?+ get_trigger_oid(reloid, depname, missing_ok) : InvalidOid;+ }
address.objectSubId = 0;
break;
case OBJECT_TABCONSTRAINT:
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.cindex 81f0380..f4f4825 100644--- a/src/backend/commands/dropcmds.c+++ b/src/backend/commands/dropcmds.c@@ -104,10 +104,13 @@ RemoveObjects(DropStmt *stmt)
/* Check permissions. */
namespaceId = get_object_namespace(&address);
- if (!OidIsValid(namespaceId) ||- !pg_namespace_ownercheck(namespaceId, GetUserId()))+ if ((relation != NULL || stmt->removeType != OBJECT_TRIGGER) &&+ (!OidIsValid(namespaceId) ||+ !pg_namespace_ownercheck(namespaceId, GetUserId())))+ {
check_object_ownership(GetUserId(), stmt->removeType, address,
object, relation);
+ }
/*
* Make note if a temporary namespace has been accessed in this
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 672fccf..25bc132 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -167,7 +167,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
char *qual;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
- Relation rel;+ Relation rel = NULL;
AclResult aclresult;
Relation tgrel;
SysScanDesc tgscan;
@@ -179,6 +179,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
char internaltrigname[NAMEDATALEN];
char *trigname;
Oid constrrelid = InvalidOid;
+ Oid targetid = InvalidOid;
ObjectAddress myself,
referenced;
char *oldtablename = NULL;
@@ -188,119 +189,141 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
if (OidIsValid(relOid))
rel = table_open(relOid, ShareRowExclusiveLock);
else
- rel = table_openrv(stmt->relation, ShareRowExclusiveLock);-- /*- * Triggers must be on tables or views, and there are additional- * relation-type-specific restrictions.- */- if (rel->rd_rel->relkind == RELKIND_RELATION)
{
- /* Tables can't have INSTEAD OF triggers */- if (stmt->timing != TRIGGER_TYPE_BEFORE &&- stmt->timing != TRIGGER_TYPE_AFTER)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a table",- RelationGetRelationName(rel)),- errdetail("Tables cannot have INSTEAD OF triggers.")));+ if (TRIGGER_FOR_CONNECT(stmt->events))+ {+ if (stmt->row)+ elog(ERROR, "ON CONNECTION trigger can not have FOR EACH ROW clause");+ if (stmt->transitionRels != NIL)+ elog(ERROR, "ON CONNECTION trigger can not have FROM clause");+ if (stmt->whenClause)+ elog(ERROR, "ON CONNECTION trigger can not have WHEN clause");+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ elog(ERROR, "ON CONNECTION trigger can be only AFTER");+ targetid = get_database_oid(stmt->relation->relname, false);+ if (targetid != MyDatabaseId)+ elog(ERROR, "ON CONNECTION trigger can be created only for self database");+ }+ else+ rel = table_openrv(stmt->relation, ShareRowExclusiveLock);
}
- else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)- {- /* Partitioned tables can't have INSTEAD OF triggers */- if (stmt->timing != TRIGGER_TYPE_BEFORE &&- stmt->timing != TRIGGER_TYPE_AFTER)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a table",- RelationGetRelationName(rel)),- errdetail("Tables cannot have INSTEAD OF triggers.")));+ if (rel != NULL)+ {+ if (TRIGGER_FOR_CONNECT(stmt->events))+ elog(ERROR, "ON CONNECTION trigger should be declared for database, not for relations");+ targetid = RelationGetRelid(rel);
/*
- * FOR EACH ROW triggers have further restrictions+ * Triggers must be on tables or views, and there are additional+ * relation-type-specific restrictions.
*/
- if (stmt->row)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
+ /* Tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a table",+ RelationGetRelationName(rel)),+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a table",+ RelationGetRelationName(rel)),+ errdetail("Tables cannot have INSTEAD OF triggers.")));+
/*
- * Disallow use of transition tables.- *- * Note that we have another restriction about transition tables- * in partitions; search for 'has_superclass' below for an- * explanation. The check here is just to protect from the fact- * that if we allowed it here, the creation would succeed for a- * partitioned table with no partitions, but would be blocked by- * the other restriction when the first partition was created,- * which is very unfriendly behavior.+ * FOR EACH ROW triggers have further restrictions
*/
- if (stmt->transitionRels != NIL)+ if (stmt->row)+ {+ /*+ * Disallow use of transition tables.+ *+ * Note that we have another restriction about transition tables+ * in partitions; search for 'has_superclass' below for an+ * explanation. The check here is just to protect from the fact+ * that if we allowed it here, the creation would succeed for a+ * partitioned table with no partitions, but would be blocked by+ * the other restriction when the first partition was created,+ * which is very unfriendly behavior.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }+ }+ else if (rel->rd_rel->relkind == RELKIND_VIEW)+ {+ /*+ * Views can have INSTEAD OF triggers (which we check below are+ * row-level), or statement-level BEFORE/AFTER triggers.+ */+ if (stmt->timing != TRIGGER_TYPE_INSTEAD && stmt->row)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("\"%s\" is a partitioned table",+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a view",
RelationGetRelationName(rel)),
- errdetail("Triggers on partitioned tables cannot have transition tables.")));+ errdetail("Views cannot have row-level BEFORE or AFTER triggers.")));+ /* Disallow TRUNCATE triggers on VIEWs */+ if (TRIGGER_FOR_TRUNCATE(stmt->events))+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a view",+ RelationGetRelationName(rel)),+ errdetail("Views cannot have TRUNCATE triggers.")));
}
- }- else if (rel->rd_rel->relkind == RELKIND_VIEW)- {- /*- * Views can have INSTEAD OF triggers (which we check below are- * row-level), or statement-level BEFORE/AFTER triggers.- */- if (stmt->timing != TRIGGER_TYPE_INSTEAD && stmt->row)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a view",- RelationGetRelationName(rel)),- errdetail("Views cannot have row-level BEFORE or AFTER triggers.")));- /* Disallow TRUNCATE triggers on VIEWs */- if (TRIGGER_FOR_TRUNCATE(stmt->events))- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a view",- RelationGetRelationName(rel)),- errdetail("Views cannot have TRUNCATE triggers.")));- }- else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)- {- if (stmt->timing != TRIGGER_TYPE_BEFORE &&- stmt->timing != TRIGGER_TYPE_AFTER)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a foreign table",- RelationGetRelationName(rel)),- errdetail("Foreign tables cannot have INSTEAD OF triggers.")));+ else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)+ {+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a foreign table",+ RelationGetRelationName(rel)),+ errdetail("Foreign tables cannot have INSTEAD OF triggers.")));- if (TRIGGER_FOR_TRUNCATE(stmt->events))+ if (TRIGGER_FOR_TRUNCATE(stmt->events))+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a foreign table",+ RelationGetRelationName(rel)),+ errdetail("Foreign tables cannot have TRUNCATE triggers.")));++ /*+ * We disallow constraint triggers to protect the assumption that+ * triggers on FKs can't be deferred. See notes with AfterTriggers+ * data structures, below.+ */+ if (stmt->isconstraint)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a foreign table",+ RelationGetRelationName(rel)),+ errdetail("Foreign tables cannot have constraint triggers.")));+ }+ else
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a foreign table",- RelationGetRelationName(rel)),- errdetail("Foreign tables cannot have TRUNCATE triggers.")));+ errmsg("\"%s\" is not a table or view",+ RelationGetRelationName(rel))));- /*- * We disallow constraint triggers to protect the assumption that- * triggers on FKs can't be deferred. See notes with AfterTriggers- * data structures, below.- */- if (stmt->isconstraint)+ if (!allowSystemTableMods && IsSystemRelation(rel))
ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is a foreign table",- RelationGetRelationName(rel)),- errdetail("Foreign tables cannot have constraint triggers.")));+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),+ errmsg("permission denied: \"%s\" is a system catalog",+ RelationGetRelationName(rel))));
}
- else- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is not a table or view",- RelationGetRelationName(rel))));-- if (!allowSystemTableMods && IsSystemRelation(rel))- ereport(ERROR,- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),- errmsg("permission denied: \"%s\" is a system catalog",- RelationGetRelationName(rel))));-
if (stmt->isconstraint)
{
/*
@@ -321,9 +344,9 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/* permission checks */
- if (!isInternal)+ if (!isInternal && rel != NULL)
{
- aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(),+ aclresult = pg_class_aclcheck(targetid, GetUserId(),
ACL_TRIGGER);
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, get_relkind_objtype(rel->rd_rel->relkind),
@@ -348,7 +371,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
partition_recurse = !isInternal && stmt->row &&
rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE;
if (partition_recurse)
- list_free(find_all_inheritors(RelationGetRelid(rel),+ list_free(find_all_inheritors(targetid,
ShareRowExclusiveLock, NULL));
/* Compute tgtype */
@@ -696,13 +719,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/* Internal callers should have made their own constraints */
Assert(!isInternal);
constraintOid = CreateConstraintEntry(stmt->trigname,
- RelationGetNamespace(rel),+ rel != NULL ? RelationGetNamespace(rel) : InvalidOid,
CONSTRAINT_TRIGGER,
stmt->deferrable,
stmt->initdeferred,
true,
InvalidOid, /* no parent */
- RelationGetRelid(rel),+ targetid,
NULL, /* no conkey */
0,
0,
@@ -767,7 +790,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
ScanKeyInit(&key,
Anum_pg_trigger_tgrelid,
BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationGetRelid(rel)));+ ObjectIdGetDatum(targetid));
tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
NULL, 1, &key);
while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
@@ -775,10 +798,22 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple);
if (namestrcmp(&(pg_trigger->tgname), trigname) == 0)
- ereport(ERROR,- (errcode(ERRCODE_DUPLICATE_OBJECT),- errmsg("trigger \"%s\" for relation \"%s\" already exists",- trigname, RelationGetRelationName(rel))));+ {+ if (rel != NULL)+ {+ ereport(ERROR,+ (errcode(ERRCODE_DUPLICATE_OBJECT),+ errmsg("trigger \"%s\" for relation \"%s\" already exists",+ trigname, RelationGetRelationName(rel))));+ }+ else+ {+ ereport(ERROR,+ (errcode(ERRCODE_DUPLICATE_OBJECT),+ errmsg("trigger \"%s\" for database \"%s\" already exists",+ trigname, stmt->relation->relname)));+ }+ }
}
systable_endscan(tgscan);
}
@@ -794,7 +829,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
memset(nulls, false, sizeof(nulls));
values[Anum_pg_trigger_oid - 1] = ObjectIdGetDatum(trigoid);
- values[Anum_pg_trigger_tgrelid - 1] = ObjectIdGetDatum(RelationGetRelid(rel));+ values[Anum_pg_trigger_tgrelid - 1] = ObjectIdGetDatum(targetid);
values[Anum_pg_trigger_tgparentid - 1] = ObjectIdGetDatum(parentTriggerOid);
values[Anum_pg_trigger_tgname - 1] = DirectFunctionCall1(namein,
CStringGetDatum(trigname));
@@ -927,30 +962,31 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
if (newtablename)
pfree(DatumGetPointer(values[Anum_pg_trigger_tgnewtable - 1]));
- /*- * Update relation's pg_class entry; if necessary; and if not, send an SI- * message to make other backends (and this one) rebuild relcache entries.- */- pgrel = table_open(RelationRelationId, RowExclusiveLock);- tuple = SearchSysCacheCopy1(RELOID,- ObjectIdGetDatum(RelationGetRelid(rel)));- if (!HeapTupleIsValid(tuple))- elog(ERROR, "cache lookup failed for relation %u",- RelationGetRelid(rel));- if (!((Form_pg_class) GETSTRUCT(tuple))->relhastriggers)+ if (rel != NULL)
{
- ((Form_pg_class) GETSTRUCT(tuple))->relhastriggers = true;-- CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);+ /*+ * Update relation's pg_class entry; if necessary; and if not, send an SI+ * message to make other backends (and this one) rebuild relcache entries.+ */+ pgrel = table_open(RelationRelationId, RowExclusiveLock);+ tuple = SearchSysCacheCopy1(RELOID,+ ObjectIdGetDatum(targetid));+ if (!HeapTupleIsValid(tuple))+ elog(ERROR, "cache lookup failed for relation %u", targetid);+ if (!((Form_pg_class) GETSTRUCT(tuple))->relhastriggers)+ {+ ((Form_pg_class) GETSTRUCT(tuple))->relhastriggers = true;- CommandCounterIncrement();- }- else- CacheInvalidateRelcacheByTuple(tuple);+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);- heap_freetuple(tuple);- table_close(pgrel, RowExclusiveLock);+ CommandCounterIncrement();+ }+ else+ CacheInvalidateRelcacheByTuple(tuple);+ heap_freetuple(tuple);+ table_close(pgrel, RowExclusiveLock);+ }
/*
* Record dependencies for trigger. Always place a normal dependency on
* the function.
@@ -977,7 +1013,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
}
- else+ else if (rel != NULL)
{
/*
* User CREATE TRIGGER, so place dependencies. We make trigger be
@@ -985,7 +1021,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* dropped. (Auto drop is compatible with our pre-7.3 behavior.)
*/
referenced.classId = RelationRelationId;
- referenced.objectId = RelationGetRelid(rel);+ referenced.objectId = targetid;
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
@@ -1018,7 +1054,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
{
ObjectAddressSet(referenced, TriggerRelationId, parentTriggerOid);
recordDependencyOn(&myself, &referenced, DEPENDENCY_PARTITION_PRI);
- ObjectAddressSet(referenced, RelationRelationId, RelationGetRelid(rel));+ ObjectAddressSet(referenced, RelationRelationId, targetid);
recordDependencyOn(&myself, &referenced, DEPENDENCY_PARTITION_SEC);
}
}
@@ -1029,7 +1065,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
int i;
referenced.classId = RelationRelationId;
- referenced.objectId = RelationGetRelid(rel);+ referenced.objectId = targetid;
for (i = 0; i < ncolumns; i++)
{
referenced.objectSubId = columns[i];
@@ -1148,7 +1184,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/* Keep lock on target rel until end of xact */
- table_close(rel, NoLock);+ if (rel != NULL)+ table_close(rel, NoLock);
return myself;
}
@@ -1165,7 +1202,8 @@ RemoveTriggerById(Oid trigOid)
ScanKeyData skey[1];
HeapTuple tup;
Oid relid;
- Relation rel;+ Relation rel = NULL;+ Form_pg_trigger pg_trigger;
tgrel = table_open(TriggerRelationId, RowExclusiveLock);
@@ -1184,28 +1222,32 @@ RemoveTriggerById(Oid trigOid)
if (!HeapTupleIsValid(tup))
elog(ERROR, "could not find tuple for trigger %u", trigOid);
- /*- * Open and exclusive-lock the relation the trigger belongs to.- */- relid = ((Form_pg_trigger) GETSTRUCT(tup))->tgrelid;+ pg_trigger = (Form_pg_trigger) GETSTRUCT(tup);- rel = table_open(relid, AccessExclusiveLock);+ if (!(pg_trigger->tgtype & TRIGGER_TYPE_CONNECT))+ {+ /*+ * Open and exclusive-lock the relation the trigger belongs to.+ */+ relid = ((Form_pg_trigger) GETSTRUCT(tup))->tgrelid;- if (rel->rd_rel->relkind != RELKIND_RELATION &&- rel->rd_rel->relkind != RELKIND_VIEW &&- rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&- rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("\"%s\" is not a table, view, or foreign table",- RelationGetRelationName(rel))));+ rel = table_open(relid, AccessExclusiveLock);- if (!allowSystemTableMods && IsSystemRelation(rel))- ereport(ERROR,- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),- errmsg("permission denied: \"%s\" is a system catalog",- RelationGetRelationName(rel))));+ if (rel->rd_rel->relkind != RELKIND_RELATION &&+ rel->rd_rel->relkind != RELKIND_VIEW &&+ rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&+ rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is not a table, view, or foreign table",+ RelationGetRelationName(rel))));+ if (!allowSystemTableMods && IsSystemRelation(rel))+ ereport(ERROR,+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),+ errmsg("permission denied: \"%s\" is a system catalog",+ RelationGetRelationName(rel))));+ }
/*
* Delete the pg_trigger tuple.
*/
@@ -1214,19 +1256,22 @@ RemoveTriggerById(Oid trigOid)
systable_endscan(tgscan);
table_close(tgrel, RowExclusiveLock);
- /*- * We do not bother to try to determine whether any other triggers remain,- * which would be needed in order to decide whether it's safe to clear the- * relation's relhastriggers. (In any case, there might be a concurrent- * process adding new triggers.) Instead, just force a relcache inval to- * make other backends (and this one too!) rebuild their relcache entries.- * There's no great harm in leaving relhastriggers true even if there are- * no triggers left.- */- CacheInvalidateRelcache(rel);+ if (rel != NULL)+ {+ /*+ * We do not bother to try to determine whether any other triggers remain,+ * which would be needed in order to decide whether it's safe to clear the+ * relation's relhastriggers. (In any case, there might be a concurrent+ * process adding new triggers.) Instead, just force a relcache inval to+ * make other backends (and this one too!) rebuild their relcache entries.+ * There's no great harm in leaving relhastriggers true even if there are+ * no triggers left.+ */+ CacheInvalidateRelcache(rel);- /* Keep lock on trigger's rel until end of xact */- table_close(rel, NoLock);+ /* Keep lock on trigger's rel until end of xact */+ table_close(rel, NoLock);+ }
}
/*
@@ -5812,3 +5857,102 @@ pg_trigger_depth(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyTriggerDepth);
}
++void+standard_session_start_hook(void)+{+ TriggerData LocTriggerData;+ SysScanDesc tgscan;+ ScanKeyData skey;+ Relation tgrel;+ Trigger trigger;+ FmgrInfo finfo;+ HeapTuple htup;++ StartTransactionCommand();+ PushActiveSnapshot(GetTransactionSnapshot());++ tgrel = table_open(TriggerRelationId, AccessShareLock);++ ScanKeyInit(&skey,+ Anum_pg_trigger_tgrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(MyDatabaseId));++ memset(&LocTriggerData, 0, sizeof(LocTriggerData));+ LocTriggerData.type = T_TriggerData;+ LocTriggerData.tg_event = TRIGGER_EVENT_AFTER;+ LocTriggerData.tg_trigger = &trigger;++ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,+ NULL, 1, &skey);+ while (HeapTupleIsValid(htup = systable_getnext(tgscan)))+ {+ Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);+ Assert(pg_trigger->tgtype == (TRIGGER_TYPE_AFTER|TRIGGER_TYPE_CONNECT));+ trigger.tgoid = pg_trigger->oid; /* OID of trigger (pg_trigger row) */+ /* Remaining fields are copied from pg_trigger, see pg_trigger.h */+ trigger.tgname = pg_trigger->tgname.data;+ trigger.tgfoid = pg_trigger->tgfoid;+ trigger.tgtype = pg_trigger->tgtype;+ trigger.tgenabled = pg_trigger->tgenabled;+ trigger.tgisinternal = pg_trigger->tgisinternal;+ trigger.tgconstrrelid = pg_trigger->tgconstrrelid;+ trigger.tgconstrindid = pg_trigger->tgconstrindid;+ trigger.tgconstraint = pg_trigger->tgconstraint;+ trigger.tgdeferrable = pg_trigger->tgdeferrable;+ trigger.tginitdeferred = pg_trigger->tginitdeferred;+ trigger.tgnargs = pg_trigger->tgnargs;+ trigger.tgnattr = pg_trigger->tgattr.dim1;+ if (trigger.tgnattr > 0)+ {+ trigger.tgattr = (int16 *) palloc(trigger.tgnattr * sizeof(int16));+ memcpy(trigger.tgattr, &(pg_trigger->tgattr.values),+ trigger.tgnattr * sizeof(int16));+ }+ else+ trigger.tgattr = NULL;++ if (trigger.tgnargs > 0)+ {+ bytea *val;+ char *p;+ bool isnull;++ val = DatumGetByteaPP(fastgetattr(htup,+ Anum_pg_trigger_tgargs,+ tgrel->rd_att, &isnull));+ if (isnull)+ elog(ERROR, "tgargs is null in trigger \"%s\"", trigger.tgname);+ p = (char *) VARDATA_ANY(val);+ trigger.tgargs = (char **) palloc(trigger.tgnargs * sizeof(char *));+ for (int i = 0; i < trigger.tgnargs; i++)+ {+ trigger.tgargs[i] = pstrdup(p);+ p += strlen(p) + 1;+ }+ }+ else+ trigger.tgargs = NULL;++ trigger.tgqual = NULL;+ trigger.tgoldtable = NULL;+ trigger.tgnewtable = NULL;++ memset(&finfo, 0, sizeof(finfo));++ /*+ * Call the trigger and throw away any possibly returned updated tuple.+ * (Don't let ExecCallTriggerFunc measure EXPLAIN time.)+ */+ ExecCallTriggerFunc(&LocTriggerData,+ 0,+ &finfo,+ NULL,+ CurrentMemoryContext);+ }+ systable_endscan(tgscan);+ table_close(tgrel, AccessShareLock);+ PopActiveSnapshot();+ CommitTransactionCommand();+}diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex dbb47d4..bf7a639 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -5301,6 +5301,8 @@ TriggerOneEvent:
{ $$ = list_make2(makeInteger(TRIGGER_TYPE_UPDATE), $3); }
| TRUNCATE
{ $$ = list_make2(makeInteger(TRIGGER_TYPE_TRUNCATE), NIL); }
+ | CONNECTION+ { $$ = list_make2(makeInteger(TRIGGER_TYPE_CONNECT), NIL); }
;
TriggerReferencing:
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.cindex c9424f1..af38640 100644--- a/src/backend/tcop/postgres.c+++ b/src/backend/tcop/postgres.c@@ -101,8 +101,6 @@ int max_stack_depth = 100;
/* wait N seconds to allow attach from a debugger */
int PostAuthDelay = 0;
--
/* ----------------
* private variables
* ----------------
@@ -167,6 +165,9 @@ static ProcSignalReason RecoveryConflictReason;
static MemoryContext row_description_context = NULL;
static StringInfoData row_description_buf;
+/* Hook for plugins to get control at start of session */+session_start_hook_type session_start_hook = standard_session_start_hook;+
/* ----------------------------------------------------------------
* decls for routines only used in this file
* ----------------------------------------------------------------
@@ -4017,6 +4018,11 @@ PostgresMain(int argc, char *argv[],
if (!IsUnderPostmaster)
PgStartTime = GetCurrentTimestamp();
+ if (session_start_hook)+ {+ (*session_start_hook) ();+ }+
/*
* POSTGRES main processing loop begins here
*
@@ -4779,3 +4785,4 @@ disable_statement_timeout(void)
if (get_timeout_active(STATEMENT_TIMEOUT))
disable_timeout(STATEMENT_TIMEOUT, false);
}
+diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.hindex fa5761b..2dce3a7 100644--- a/src/include/catalog/pg_trigger.h+++ b/src/include/catalog/pg_trigger.h@@ -82,6 +82,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
#define TRIGGER_TYPE_UPDATE (1 << 4)
#define TRIGGER_TYPE_TRUNCATE (1 << 5)
#define TRIGGER_TYPE_INSTEAD (1 << 6)
+#define TRIGGER_TYPE_CONNECT (1 << 7)
#define TRIGGER_TYPE_LEVEL_MASK (TRIGGER_TYPE_ROW)
#define TRIGGER_TYPE_STATEMENT 0
@@ -115,6 +116,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
#define TRIGGER_FOR_DELETE(type) ((type) & TRIGGER_TYPE_DELETE)
#define TRIGGER_FOR_UPDATE(type) ((type) & TRIGGER_TYPE_UPDATE)
#define TRIGGER_FOR_TRUNCATE(type) ((type) & TRIGGER_TYPE_TRUNCATE)
+#define TRIGGER_FOR_CONNECT(type) ((type) & TRIGGER_TYPE_CONNECT)
/*
* Efficient macro for checking if tgtype matches a particular level
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.hindex bd30607..c76fdff 100644--- a/src/include/tcop/tcopprot.h+++ b/src/include/tcop/tcopprot.h@@ -30,6 +30,12 @@ extern PGDLLIMPORT const char *debug_query_string;
extern int max_stack_depth;
extern int PostAuthDelay;
+/* Hook for plugins to get control at start and end of session */+typedef void (*session_start_hook_type) (void);++extern PGDLLIMPORT session_start_hook_type session_start_hook;+extern void standard_session_start_hook(void);+
/* GUC-configurable parameters */
typedef enum
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.cindex d4a3d58..6e97028 100644--- a/src/pl/plpgsql/src/pl_exec.c+++ b/src/pl/plpgsql/src/pl_exec.c@@ -904,9 +904,9 @@ plpgsql_exec_trigger(PLpgSQL_function *func,
PLpgSQL_execstate estate;
ErrorContextCallback plerrcontext;
int rc;
- TupleDesc tupdesc;- PLpgSQL_rec *rec_new,- *rec_old;+ TupleDesc tupdesc = NULL;+ PLpgSQL_rec *rec_new = NULL,+ *rec_old = NULL;
HeapTuple rettup;
/*
@@ -929,26 +929,28 @@ plpgsql_exec_trigger(PLpgSQL_function *func,
estate.err_text = gettext_noop("during initialization of execution state");
copy_plpgsql_datums(&estate, func);
- /*- * Put the OLD and NEW tuples into record variables- *- * We set up expanded records for both variables even though only one may- * have a value. This allows record references to succeed in functions- * that are used for multiple trigger types. For example, we might have a- * test like "if (TG_OP = 'INSERT' and NEW.foo = 'xyz')", which should- * work regardless of the current trigger type. If a value is actually- * fetched from an unsupplied tuple, it will read as NULL.- */- tupdesc = RelationGetDescr(trigdata->tg_relation);-- rec_new = (PLpgSQL_rec *) (estate.datums[func->new_varno]);- rec_old = (PLpgSQL_rec *) (estate.datums[func->old_varno]);+ if (trigdata->tg_relation)+ {+ /*+ * Put the OLD and NEW tuples into record variables+ *+ * We set up expanded records for both variables even though only one may+ * have a value. This allows record references to succeed in functions+ * that are used for multiple trigger types. For example, we might have a+ * test like "if (TG_OP = 'INSERT' and NEW.foo = 'xyz')", which should+ * work regardless of the current trigger type. If a value is actually+ * fetched from an unsupplied tuple, it will read as NULL.+ */+ tupdesc = RelationGetDescr(trigdata->tg_relation);- rec_new->erh = make_expanded_record_from_tupdesc(tupdesc,- estate.datum_context);- rec_old->erh = make_expanded_record_from_exprecord(rec_new->erh,- estate.datum_context);+ rec_new = (PLpgSQL_rec *) (estate.datums[func->new_varno]);+ rec_old = (PLpgSQL_rec *) (estate.datums[func->old_varno]);+ rec_new->erh = make_expanded_record_from_tupdesc(tupdesc,+ estate.datum_context);+ rec_old->erh = make_expanded_record_from_exprecord(rec_new->erh,+ estate.datum_context);+ }
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
{
/*
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex 5e76b3a..ae47a85 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -3043,6 +3043,36 @@ drop table self_ref;
drop function dump_insert();
drop function dump_update();
drop function dump_delete();
+-- on connect trigger+create table connects(id serial, who text);+create function on_login_proc() returns trigger as $$+begin+ insert into connects (who) values (current_user);+ raise notice 'You are welcome!';+ return null;+end;+$$ language plpgsql;+create trigger on_login_trigger after connection on regression execute procedure on_login_proc();+\c+NOTICE: You are welcome!+select * from connects;+ id | who +----+----------+ 1 | knizhnik+(1 row)++\c+NOTICE: You are welcome!+select * from connects;+ id | who +----+----------+ 1 | knizhnik+ 2 | knizhnik+(2 rows)++drop trigger on_login_trigger on regression;+drop function on_login_proc();+drop table connects;
-- Leave around some objects for other tests
create table trigger_parted (a int primary key) partition by list (a);
create function trigger_parted_trigfunc() returns trigger language plpgsql as
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex e228d0a..551e592 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -2286,6 +2286,24 @@ drop function dump_insert();
drop function dump_update();
drop function dump_delete();
+-- on connect trigger+create table connects(id serial, who text);+create function on_login_proc() returns trigger as $$+begin+ insert into connects (who) values (current_user);+ raise notice 'You are welcome!';+ return null;+end;+$$ language plpgsql;+create trigger on_login_trigger after connection on regression execute procedure on_login_proc();+\c+select * from connects;+\c+select * from connects;+drop trigger on_login_trigger on regression;+drop function on_login_proc();+drop table connects;+
-- Leave around some objects for other tests
create table trigger_parted (a int primary key) partition by list (a);
create function trigger_parted_trigfunc() returns trigger language plpgsql as
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru
Subject: Re: On login trigger: take three
In-Reply-To: <3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru>
References: <0d46d29f-4558-3af9-9c85-7774e14a7709@postgrespro.ru> <TYAPR01MB299057DD3A4AA8CF2B896EEEFE2D0@TYAPR01MB2990.jpnprd01.prod.outlook.com> <3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox