From 201737168c4ed5b14313d111d8d746c7f072f24e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 19 Apr 2002 16:36:08 +0000 Subject: pg_trigger's index on tgrelid is replaced by a unique index on (tgrelid, tgname). This provides an additional check on trigger name uniqueness per-table (which was already enforced by the code anyway). With this change, RelationBuildTriggers will read the triggers in order by tgname, since it's scanning using this index. Since a predictable trigger ordering has been requested for some time, document this behavior as a feature. Also document that rules fire in name order, since yesterday's changes to pg_rewrite indexing cause that too. --- doc/src/sgml/ref/create_rule.sgml | 40 ++++++++------ doc/src/sgml/ref/create_trigger.sgml | 40 +++++++++++--- doc/src/sgml/trigger.sgml | 101 ++++++++++++++++++++--------------- 3 files changed, 115 insertions(+), 66 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index c9cc209843a..1c5786a0c91 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ @@ -22,7 +22,7 @@ PostgreSQL documentation CREATE RULE name AS ON event - TO object [ WHERE condition ] + TO table [ WHERE condition ] DO [ INSTEAD ] action where action can be: @@ -48,7 +48,8 @@ NOTHING name - The name of a rule to create. + The name of a rule to create. This must be distinct from the name + of any other rule for the same table. @@ -63,14 +64,11 @@ NOTHING - object + table - Object is either table - or table.column. (Currently, only the - table form is - actually implemented.) + The name (optionally schema-qualified) of the table or view the rule + applies to. @@ -103,8 +101,7 @@ NOTHING Within the condition and action, the special table names new and old may be - used to refer to values in the referenced table (the - object). + used to refer to values in the referenced table. new is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. old is valid in ON UPDATE and ON DELETE @@ -159,7 +156,7 @@ CREATE accessed, inserted, updated, or deleted, there is an old instance (for selects, updates and deletes) and a new instance (for inserts and updates). All the rules for the given event type and the given target - object (table) are examined, in an unspecified order. If the + table are examined successively (in order by name). If the condition specified in the WHERE clause (if any) is true, the action part of the rule is @@ -178,8 +175,7 @@ CREATE The action part of the rule can consist of one or more queries. To write multiple queries, surround them with parentheses. Such queries will be performed in the - specified order (whereas there are no guarantees about the execution - order of multiple rules for an object). The action can also be NOTHING indicating no action. Thus, a DO INSTEAD NOTHING rule suppresses the original query from executing (when its condition is true); a DO NOTHING rule @@ -191,6 +187,20 @@ CREATE executes with the same command and transaction identifier as the user command that caused activation. + + + It is important to realize that a rule is really a query transformation + mechanism, or query macro. The entire query is processed to convert it + into a series of queries that include the rule actions. This occurs + before evaluation of the query starts. So, conditional rules are + handled by adding the rule condition to the WHERE clause of the action(s) + derived from the rule. The above description of a rule as an operation + that executes for each row is thus somewhat misleading. If you actually + want an operation that fires independently for each physical row, you + probably want to use a trigger not a rule. Rules are most useful for + situations that call for transforming entire queries independently of + the specific data being handled. + @@ -202,7 +212,7 @@ CREATE Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT query. Thus, an ON SELECT - rule effectively turns the object table into a view, whose visible + rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT query rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index f3e82766380..1d24be9a2b4 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -44,23 +44,24 @@ CREATE TRIGGER name { BEFORE | AFTE name - The name to give the new trigger. + The name to give the new trigger. This must be distinct from the name + of any other trigger for the same table. - table + event - The name of an existing table. + One of INSERT, DELETE or UPDATE. - event + table - One of INSERT, DELETE or UPDATE. + The name (optionally schema-qualified) of the table the trigger is for. @@ -68,7 +69,20 @@ CREATE TRIGGER name { BEFORE | AFTE func - A user-supplied function. + A user-supplied function that is declared as taking no arguments + and returning type opaque. + + + + + arguments + + + An optional comma-separated list of arguments to be provided to the + function when the trigger is executed, along with the standard trigger + data such as old and new tuple contents. The arguments are literal + string constants. Simple names and numeric constants may be written + here too, but they will all be converted to strings. @@ -130,6 +144,12 @@ CREATE after the event, all changes, including the last insertion, update, or deletion, are visible to the trigger. + + + If multiple triggers of the same kind are defined for the same event, + they will be fired in alphabetical order by name. + + SELECT does not modify any rows so you can not create SELECT triggers. Rules and views are more @@ -262,6 +282,12 @@ CREATE TABLE distributors ( + + + SQL99 specifies that multiple triggers should be fired in + time-of-creation order. PostgreSQL + uses name order, which was judged more convenient to work with. + diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 8dd9815e0c6..5456f4d0cdb 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,5 +1,5 @@ @@ -14,8 +14,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl AFTER on INSERT, DELETE or UPDATE of a tuple as a trigger event. - - Trigger Creation + + Trigger Definition If a trigger event occurs, the trigger manager (called by the Executor) @@ -24,13 +24,17 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl - The trigger function must be defined before the trigger is created as a - function taking no arguments and returning opaque. If the function is - written in C, it must use the version 1 function manager interface. + 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 opaque. + (The trigger function receives its input through a TriggerData + structure, not in the form of ordinary function arguments.) + If the function is written in C, it must use the version 1 + function manager interface. - The syntax for creating triggers is as follows: + The syntax for creating triggers is: CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ] @@ -48,9 +52,9 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - The name of the trigger is - used if you ever have to delete the trigger. - It is used as an argument to the DROP TRIGGER command. + The trigger must have a name distinct from all other triggers on + the same table. The name is needed + if you ever have to delete the trigger. @@ -72,7 +76,7 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | UPDATE - The next element of the command determines on what event(s) will trigger + The next element of the command determines what event(s) will trigger the function. Multiple events can be specified separated by OR. @@ -82,7 +86,7 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | relation - The relation name determines which table the event applies to. + The relation name indicates which table the event applies to. @@ -94,6 +98,7 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | The FOR EACH clause determines whether the trigger is fired for each affected row or before (or after) the entire statement has completed. + Currently only the ROW case is supported. @@ -102,7 +107,7 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | procedure - The procedure name is the function called. + The procedure name is the function to be called. @@ -112,23 +117,23 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | The arguments passed to the function in the TriggerData structure. - The purpose of passing arguments to the function is to allow different - triggers with similar requirements to call the same function. + This is either empty or a list of one or more simple literal + constants (which will be passed to the function as strings). - Also, procedure - may be used for triggering different relations (these - functions are named as general trigger functions). - - - - As example of using both features above, there could be a general - function that takes as its arguments two field names and puts the current - user in one and the current timestamp in the other. This allows triggers to - be written on INSERT events to automatically track creation of records in a - transaction table for example. It could also be used as a last updated - function if used in an UPDATE event. + The purpose of including arguments in the trigger definition + is to allow different + triggers with similar requirements to call the same function. + As an example, there could be a generalized trigger + function that takes as its arguments two field names and puts the + current user in one and the current timestamp in the other. + Properly written, this trigger function would be independent of + the specific table it is triggering on. So the same function + could be used for INSERT events on any table with suitable fields, + to automatically track creation of records in a transaction table for + example. It could also be used to track last-update events if + defined as an UPDATE trigger. @@ -136,8 +141,8 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - Trigger functions return HeapTuple to the calling Executor. This - is ignored for triggers fired after an INSERT, DELETE or UPDATE operation + Trigger functions return a HeapTuple to the calling Executor. The return + value is ignored for triggers fired AFTER an operation, but it allows BEFORE triggers to: @@ -150,33 +155,41 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - Return a pointer to another tuple (INSERT and UPDATE only) which will - be inserted (as the new version of the updated tuple if UPDATE) instead - of original tuple. + For INSERT and UPDATE triggers only, the returned tuple becomes the + tuple which will be inserted or will replace the tuple being updated. + This allows the trigger function to modify the row being inserted or + updated. + + A BEFORE trigger that does not intend to cause either of these behaviors + must be careful to return the same NEW tuple it is passed. Note that there is no initialization performed by the CREATE TRIGGER - handler. This will be changed in the future. Also, if more than one trigger - is defined for the same event on the same relation, the order of trigger - firing is unpredictable. This may be changed in the future. + handler. This may be changed in the future. - If a trigger function executes SQL-queries (using SPI) then these queries - may fire triggers again. This is known as cascading triggers. There is no - explicit limitation on the number of cascade levels. + If more than one trigger + is defined for the same event on the same relation, the triggers will + be fired in alphabetical order by name. In the case of BEFORE triggers, + the possibly-modified tuple returned by each trigger becomes the input + to the next trigger. If any BEFORE trigger returns NULL, the operation + is abandoned and subsequent triggers are not fired. - If a trigger is fired by INSERT and inserts a new tuple in the same - relation then this trigger will be fired again. Currently, there is nothing - provided for synchronization (etc) of these cases but this may change. At - the moment, there is function funny_dup17() in the regress tests which uses - some techniques to stop recursion (cascading) on itself... + If a trigger function executes SQL-queries (using SPI) then these queries + may fire triggers again. This is known as cascading triggers. There is no + direct limitation on the number of cascade levels. It is possible for + cascades to cause recursive invocation of the same trigger --- for + example, an INSERT trigger might execute a query that inserts an + additional tuple into the same table, causing the INSERT trigger to be + fired again. It is the trigger programmer's + responsibility to avoid infinite recursion in such scenarios. @@ -326,7 +339,7 @@ typedef struct TriggerData is a pointer to structure describing the triggered relation. Look at src/include/utils/rel.h for details about this structure. The most - interest things are tg_relation->rd_att (descriptor of the relation + interesting things are tg_relation->rd_att (descriptor of the relation tuples) and tg_relation->rd_rel->relname (relation's name. This is not char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if you need a copy of name). -- cgit v1.2.3