Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEE7Z-0004vA-7G for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Sep 2020 15:59:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEE7Y-0000O4-5C for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Sep 2020 15:59:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEE7X-0000Nx-I2 for pgsql-hackers@lists.postgresql.org; Fri, 04 Sep 2020 15:59:23 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEE7T-0004so-6g for pgsql-hackers@lists.postgresql.org; Fri, 04 Sep 2020 15:59:22 +0000 Received: from [192.168.27.200] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 9F48121C2EDB for ; Fri, 4 Sep 2020 18:59:17 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1599235157; bh=hv7iGvV1jBreEe7+3sYB5db+/4HePqny/ZjbvkBt3Ac=; h=Subject:To:References:From:Date:In-Reply-To; b=lB72B4OU8mIlsvJHySLM5eYM6qzXNL+T5+oyIEqyl9dqQqVrcoU/rRT5J8NQR8Rdp 3fckE8tvJcWKGNzQmyDqTOb0TKPHlMRUA5KgkbsqC3+FXCUlTEq3p4IjEVeWoOq1v4 +sFPEq66lphxPw7MVBD9Cqpn2fKqtVCHWXwNrx1U= Subject: Re: On login trigger: take three To: pgsql-hackers@lists.postgresql.org References: <0d46d29f-4558-3af9-9c85-7774e14a7709@postgrespro.ru> From: Konstantin Knizhnik Message-ID: <3356df03-d73c-d02f-00ba-093766a13ca0@postgrespro.ru> Date: Fri, 4 Sep 2020 18:59:17 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------0FA816FFA91FFF098EE96B39" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------0FA816FFA91FFF098EE96B39 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------0FA816FFA91FFF098EE96B39 Content-Type: text/x-patch; name="on_login_trigger-2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="on_login_trigger-2.patch" 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 CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } - ON table_name + ON { table_name | database_name } [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ] @@ -41,6 +41,7 @@ CREATE [ CONSTRAINT ] TRIGGER name UPDATE [ OF column_name [, ... ] ] DELETE TRUNCATE + CONNECTION 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 @@ + You can also define on connection trigger: + + CREATE EVENT TRIGGER mytrigger + AFTER CONNECTION ON mydatabase + EXECUTE {PROCEDURE | FUNCTION} myproc(); + + On connection triggers can only be defined for self database. + It can be only AFTER trigger, + and may not contain WHERE 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: + + DROP TRIGGER mytrigger ON mydatabase; + + + + 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 trigger. 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 --------------0FA816FFA91FFF098EE96B39--