pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 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.sgml index 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.c index 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.c index 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.c index 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.y index 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.c index 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.h index 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.h index 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.c index 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.out index 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.sql index 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

view thread (4+ messages)

Message-ID: <3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru>
Permalink:  ../3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru/
Also on:    postgresql.org/message-id/3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru

 ·  · 

reply

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