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#4748bcb0c5fc98cec0a735dbdffb0c68
https://www.postgresql.org/message-id/flat/OSAPR01MB507373499CCCEA00EAE79875FE2D0%40OSAPR01MB5073.jpnprd01.prod.outlook.com#ed50c248be32be6955c385ca67d6cdc1
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
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
