pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
On login trigger: take three
4+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2023-10-16 e83d1b0c40  Add support event triggers on authenticated login
  2023-09-25 7750fefdb2  Add GUC for temporarily disabling event triggers
* On login trigger: take three
@ 2020-09-03 13:42  Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
  0 siblings, 2 replies; 4+ messages in thread

From: Konstantin Knizhnik @ 2020-09-03 13:42 UTC (permalink / raw)
  To: pgsql-hackers

Hi hackers,

Recently I have asked once again by one of our customers about login 
trigger in postgres. People are migrating to Postgres from Oracle and  
looking for Postgres analog of this Oracle feature.
This topic is not new:

https://www.postgresql.org/message-id/flat/1570308356720-0.post%40n3.nabble.com#4748bcb0c5fc98cec0a7...
https://www.postgresql.org/message-id/flat/OSAPR01MB507373499CCCEA00EAE79875FE2D0%40OSAPR01MB5073.jp...

end even session connect/disconnect hooks were sometimes committed (but 
then reverted).
As far as I understand most of the concerns were related with disconnect 
hook.
Performing some action on session disconnect is actually much more 
complicated than on login.
But customers are not needed it, unlike actions performed at session start.

I wonder if we are really going to make some steps in this directions?
The discussion above was finished with "We haven't rejected the concept 
altogether, AFAICT"

I have tried to resurrect this patch and implement on-connect trigger on 
top of it.
The syntax is almost the same as proposed by Takayuki:

CREATE EVENT TRIGGER mytrigger
AFTER CONNECTION ON mydatabase
EXECUTE {PROCEDURE | FUNCTION} myproc();

I have replaced CONNECT with CONNECTION because last keyword is already 
recognized by Postgres and
make ON clause mandatory to avoid shift-reduce conflicts.

Actually specifying database name is redundant, because we can define 
on-connect trigger only for self database (just because triggers and 
functions are local to the database).
It may be considered as argument against handling session start using 
trigger. But it seems to be the most natural mechanism for users.

On connect trigger can be dropped almost in the same way as normal (on 
relation) trigger, but with specifying name of the database instead of 
relation name:

DROP TRIGGER mytrigger ON mydatabase;

It is possible to define arbitrary number of on-connect triggers with 
different names.

I attached my prototype implementation of this feature.
I just to be sure first that this feature will be interested to community.
If so, I will continue work in it and prepare new version of the patch 
for the commitfest.

Example

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachments:

on_login_trigger.patch (text/x-patch, 34.1K) 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/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
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: On login trigger: take three
@ 2020-09-03 14:18  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
  1 sibling, 0 replies; 4+ messages in thread

From: Pavel Stehule @ 2020-09-03 14:18 UTC (permalink / raw)
  To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>; +Cc: pgsql-hackers

Hi

čt 3. 9. 2020 v 15:43 odesílatel Konstantin Knizhnik <
k.knizhnik@postgrespro.ru> napsal:

> Hi hackers,
>
> Recently I have asked once again by one of our customers about login
> trigger in postgres. People are migrating to Postgres from Oracle and
> looking for Postgres analog of this Oracle feature.
> This topic is not new:
>
>
> https://www.postgresql.org/message-id/flat/1570308356720-0.post%40n3.nabble.com#4748bcb0c5fc98cec0a7...
>
> https://www.postgresql.org/message-id/flat/OSAPR01MB507373499CCCEA00EAE79875FE2D0%40OSAPR01MB5073.jp...
>
> end even session connect/disconnect hooks were sometimes committed (but
> then reverted).
> As far as I understand most of the concerns were related with disconnect
> hook.
> Performing some action on session disconnect is actually much more
> complicated than on login.
> But customers are not needed it, unlike actions performed at session start.
>
> I wonder if we are really going to make some steps in this directions?
> The discussion above was finished with "We haven't rejected the concept
> altogether, AFAICT"
>
> I have tried to resurrect this patch and implement on-connect trigger on
> top of it.
> The syntax is almost the same as proposed by Takayuki:
>
> CREATE EVENT TRIGGER mytrigger
> AFTER CONNECTION ON mydatabase
> EXECUTE {PROCEDURE | FUNCTION} myproc();
>
> I have replaced CONNECT with CONNECTION because last keyword is already
> recognized by Postgres and
> make ON clause mandatory to avoid shift-reduce conflicts.
>
> Actually specifying database name is redundant, because we can define
> on-connect trigger only for self database (just because triggers and
> functions are local to the database).
> It may be considered as argument against handling session start using
> trigger. But it seems to be the most natural mechanism for users.
>
> On connect trigger can be dropped almost in the same way as normal (on
> relation) trigger, but with specifying name of the database instead of
> relation name:
>
> DROP TRIGGER mytrigger ON mydatabase;
>
> It is possible to define arbitrary number of on-connect triggers with
> different names.
>
> I attached my prototype implementation of this feature.
> I just to be sure first that this feature will be interested to community.
> If so, I will continue work in it and prepare new version of the patch
> for the commitfest.
>
>
I have a customer that requires this feature too. Now it uses a solution
based on dll session autoloading.  Native solution can be great.

+1

Pavel

Example
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: On login trigger: take three
@ 2020-09-04 00:11  tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>
  parent: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
  1 sibling, 1 reply; 4+ messages in thread

From: tsunakawa.takay@fujitsu.com @ 2020-09-04 00:11 UTC (permalink / raw)
  To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>; +Cc: pgsql-hackers

From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
> Recently I have asked once again by one of our customers about login trigger in
> postgres. People are migrating to Postgres from Oracle and looking for Postgres
> analog of this Oracle feature.
> This topic is not new:

> I attached my prototype implementation of this feature.
> I just to be sure first that this feature will be interested to community.
> If so, I will continue work in it and prepare new version of the patch for the
> commitfest.

Thanks a lot for taking on this! +10

> It may be considered as argument against handling session start using trigger.
> But it seems to be the most natural mechanism for users.

Yeah, it's natural, just like the Unix shells run some shell scripts in the home directory.


Regards
Takayuki Tsunakawa



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: On login trigger: take three
@ 2020-09-04 15:59  Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
  parent: tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Konstantin Knizhnik @ 2020-09-04 15:59 UTC (permalink / raw)
  To: pgsql-hackers@lists.postgresql.org

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
^ permalink raw reply [nested|flat] 4+ messages in thread


end of thread, other threads:[~2020-09-04 15:59 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-03 13:42 On login trigger: take three Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
2020-09-03 14:18 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-09-04 00:11 ` tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>
2020-09-04 15:59   ` Konstantin Knizhnik <k.knizhnik@postgrespro.ru>

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