 
PostgreSQL Training, Consulting & Support |
By A. Elein Mustain |
 12-Mar-2005 Issue: 101 |
| Archives | | General Tidbits | | Google General Bits | | Docs | | Castellano | | Português | | Subscriptions | | Notifications |
 |
| Prev | | Next |
|
General Bits is a column loosely based on the PostgreSQL mailing list
pgsql-general. To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org. |
| ||||
This is a special rollup issue covering many different articles from the archives about Triggers. I hope you enjoy this collection.
|
A bad trigger was created to update an id with the next value of a sequence.
CREATE OR REPLACE FUNCTION "f_products"() RETURNS "opaque" AS '
BEGIN
NEW.ID = NEXTVAL(''products_id_seq'');
RETURN NEW;
END' LANGUAGE 'plpgsql'
CREATE TRIGGER "t_classes" AFTER INSERT ON "products"
FOR EACH ROW EXECUTE PROCEDURE f_products();
This does not work because to change a value in the row, you must use a BEFORE trigger rather than an AFTER trigger.
It was also pointed out that it is much easier to use a SERIAL data type or to have a DEFAULT set to the value of nextval() to achieve the same effect. You can find more informatino in the documentation.
|
The following is a trigger was created to validate the tag_number column for uniqueness on update. However, when columns other than tag_number are updated, they are rejected. When, say, column item_name is updated, both the OLD and the NEW records will have the same value for tag_number and therefore the exception will be raised.
CREATE OR REPLACE FUNCTION validate_tag_number() RETURNS OPAQUE AS ' BEGIN IF new.tag_number=old.tag_number THEN RAISE EXCEPTION ''Number is already present ''; END IF; RETURN NEW; END; 'LANGUAGE 'plpgsql'; CREATE TRIGGER validate_tag_number BEFORE UPDATE ON tag_vendor FOR EACH ROW EXECUTE PROCEDURE validate_tag_number();
What you really want to do to ensure uniqueness is to define the column with a unique index by declaring it to be UNIQUE.
|
Another issue with bulk loading is triggers firing with each row inserted. If you are sure your data is trustworthy and already meets your referential integrity requirements, you can turn off triggers for the bulk load and turn them back on immediately afterward. You should not use this option when your data is not completely clean.
The reltriggers field in the pg_class table contains the number of triggers active for each table. It can be set to 0 the disable the triggers, but will need to be reset to the proper number of triggers to have them re-enabled.
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'tablename'; UPDATE pg_class SET reltriggers = ( SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'table name';
|
The decision factor for rules vs. triggers is that rules are for rewriting the query and triggers are for changes executed on rows. The timing is different. Rules are invoked before a query gets executed and may affect the statement to be executed. The rule is only invoked at the start, but the ultimate statement may affect many rows.
A trigger, on the other hand, is a reaction to a change of data. During the statement execution, if there is a trigger PER ROW, each row affected will fire off the trigger. The triggers PER STATEMENT are not yet implemented so they are not considered here.
It is a common mistake to try to use a rule to track input and delete counts, for example. A rule which adds or subtracts a counter in some table will fire once, regardless of whether the statement affects one or many rows. In the example in the item above, if rules were used instead of triggers then when several rows were deleted by a single SQL statement the count would only have been decremented by one. Likewise, when we inserted multiple rows in one statement, the row count would have been incremented by one.
A good use of rules is to enable updates, inserts and deletes for views. These rules can redirect the data into logical statements operating on the underlying tables.
|
Both plpgsql and plpython functions can be trigger functions. The return values are a different for the plpython functions in keeping with the plpython language. This item describes plpgsql behavior only.
As of 7.3 trigger functions needed to be created to return type trigger. The previously defined return value, opaque, has been deprecated.
The information value returned from plpgsql triggers is row data and an indication of the disposition of the row. Even if NULL is returned, as is legal in certain situations, it indicates (non-)data and a disposition.
If the function is not returning NULL, it is usually returning the row structure that was made available for the function via variables NEW and OLD. INSERTS only have a NEW row. UPDATES have both NEW and OLD rows. DELETES only have an OLD row. The value returned from the function is usually NULL or NEW, depending on what the function is supposed to do.
A BEFORE trigger is fired BEFORE the row is acted upon-- before it is inserted, updated or deleted. But only the BEFORE UPDATE or BEFORE INSERT trigger can modify the row being inserted or updated. The UPDATE or INSERT can be ignored by returning NULL instead of NEW. This does not abort the transaction.
An AFTER trigger occurs after the insertion, update or deletion occurs. It cannot change the data or void the action on the particular row.
All triggers, however, can raise an error to abort the transaction.
| Trigger Type | Statement | Return Value | Meaning |
|---|---|---|---|
| BEFORE | INSERT | NULL | Forget this row insert, but don't abort transaction |
| NEW | Use this, possibly updated row. | ||
| UPDATE | NULL | Forget this row update, but don't abort transaction | |
| NEW | Use this, possibly updated row. | ||
| OLD | Same as returning NULL, but otherwise useless | ||
| DELETE | NULL | Forget this row deletion, but don't abort transaction | |
| OLD | Delete this row. | ||
| AFTER | INSERT | NULL | Return value ignored |
| UPDATE | NULL | Return value ignored | |
| DELETE | NULL | Return value ignored. |
|
In plpgsql trigger functions the row triggering the function is defined in SQL variables so that the values are available to the trigger functions. NEW is the row about to be inserted or updated. OLD is the row as it was before the update. This table shows which statement types have what variables available.
These column values of these variables are accessed using the "dot" syntax like this:
Statement Variable INSERT NEW UPDATE NEW, OLD DELETE OLD
NEW.id
BEFORE triggers on INSERT and UPDATE statements allow you to update or change column values based on your trigger's criteria. In UPDATE triggers, this criteria can be based on what the row looked like before the insert started.
If the task at hand is to change or assign column values in the row in question, you simply make the assignment to the NEW variable. No separate update is required.
This is an example of a plpgsql trigger function for BEFORE UPDATE of a salary table. If salary is the field being updated, the previous salary is saved in a separate column, oldsalary and the approved by field is set to NULL so the raise can be approved by some other mechanism.
CREATE OR REPLACE FUNCTION newsal() returns TRIGGER as ' BEGIN IF NEW.salary <> OLD.salary,0 THEN NEW.oldsalary = OLD.salary; NEW.approvedby = NULL; END IF; RETURN NEW; END; ' language 'plpgsql'; CREATE TRIGGER newsal BEFORE UPDATE on salary FOR EACH ROW EXECUTE PROCEDURE newsal();
|
A transaction in postgreSQL encompasses all of the functions and triggers in the transaction. If you are not in a block transaction (BEGIN ... COMMIT) the single primary statement initiates the transaction.
In this (nonsense) SQL, the primary statement is INSERT. Let us also suppose there is a BEFORE INSERT trigger on thattable which calls yet another function checkme().
INSERT INTO thattable SELECT f(id), x(sdate), y(stuff) FROM mytable WHERE id IN (SELECT id FROM anothertable a, badaccts b WHERE a.id = b.id);The execution of this statement invokes all of the triggers, functions and sub-selects in the context of the single transaction begun with INSERT. This includes the functions f(), x(), y(), the trigger function checkme() and the subselect in the last WHERE clause. Any cast functions invoked implicitly are also included, as well as any input or output functions for the data types. If there is an error in any of the SQL or auxiliary functions the transaction as a whole will be rolled back.
|
Suppose we had a table representing processes tasks like this:
=# \d procs Table "procs" Column | Type | Modifiers -----------+-----------------------+----------- pid | integer | not null task | character varying(10) | not null owner | character varying(10) | not null IsActive | boolean |The tasks are executed in single-threaded mode, one after the other. No two processes can be active at the same moment. What is wanted is the exclusive OR behavior like a radio button set.
The problem is how to keep the table updated with one and only one record in the table with IsActive set as TRUE. When a process is made active active, any other active process must be set to inactive.
The implementation is a trigger that fires when a processes updated to active which updates the row that was previously set to active as inactive. Here is the function and trigger.
CREATE TRIGGER proc_current AFTER UPDATE OR INSERT ON procs FOR EACH ROW EXECUTE PROCEDURE proc_current_trigger(); CREATE FUNCTION proc_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.IsActive THEN UPDATE procs SET IsActive = ''f'' WHERE pid != NEW.pid AND IsActive = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql';
Both INSERT and UPDATE cases are being handled by the trigger to ensure that integrity remains. Because there is no DELETE trigger and no action when a process is set to inactive, it is possible to have no active processes.
The function checks to see if the new or updated row is set to active, and if so, unset all (should only be one!) other rows to inactive. The other is accomplished by excluding the new row by pid in the where clause.
|
Constraints are restrictions on columns in a table definition. There are several kinds of constraints available for both the table and the columns and they are implemented in several ways. Some constraints are implemented as triggers, some as indexes, and some are attributes of a column definition.
There are some hints when you create a table what triggers and indexes are created for references and primary keys. These are those notices:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't1_pkey' for table 't1' NOTICE: CREATE TABLE / UNIQUE will create implicit index 't1_unq_key' for table 't1' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)But when those messages long gone, it is nice to know exactly how the constraints defined are being implemented. This is a list of possible constraints and how they are implemented. The letter in parentheses indicates the constraint type:
| Constraint Clause | Constraint Name | Uses | Index/Trigger Name | Trigger Function |
|---|---|---|---|---|
| PRIMARY KEY | table_pkey (p) | index | table_pkey | |
| UNIQUE | table_column_key (u) | index | table_column_key | |
| FOREIGN KEY | $n (f) | trigger | RI_ConstraintTrigger_n | RI_FKey_check_ins |
| referred table | $n (f) | trigger | RI_ConstraintTrigger_n | RI_FKey_noaction_del |
| referred table | $n (f) | trigger | RI_ConstraintTrigger_n | RI_FKey_noaction_upd |
| CHECK | table_column (c) | check | none |
References and Foreign Keys
For the purposes of these descriptions, REFERENCES and FOREIGN KEYS are interchangeable
as they have the same implementation. References and foreign keys all use the same set of
built-in functions prefixed with RI_FKey_ to do the reference checking.
A table which defines a FOREIGN KEY reference also implicitly installs update and delete triggers on the table being referenced. In the case above, the trigger implemented the default NO ACTION behaviour on the referenced tables. If the foreign key were defined with different directives, one of the other functions would be installed as the trigger instead. These are the internal functions which are used for referential integrity constraints.
| RI_FKey_cascade_del | RI_FKey_check_ins | RI_FKey_noaction_del |
| RI_FKey_cascade_upd | RI_FKey_check_upd | RI_FKey_noaction_upd |
| RI_FKey_restrict_del | RI_FKey_setdefault_del | RI_FKey_setnull_del |
| RI_FKey_restrict_upd | RI_FKey_setdefault_upd | RI_FKey_setnull_upd |
Foreign key constraints names have the format $1, $2, etc. The trigger names are constructed using oid based numbers.
Checks and other Constraints
In the case of the CHECK constraint the check is stored as an attribute
specific constraint--it is neither a trigger function nor an index.
The compiled and source of the CHECK expression is stored in the pg_constraint
table.
DEFAULT values and NOT NULL are conceptually constraints, but are not implemented like other constraints. These limitations are attributes of columns and are stored in the pg_attributes and pg_attrdef tables.
Named Constraints
The default names of FOREIGN KEY, UNIQUE and CHECK constraints can be overridden.
To name a constraint, prefix its definition with
CONSTRAINT name like this.
create table showconstraints ( id integer PRIMARY KEY, unq integer CONSTRAINT sc_unq UNIQUE, checkme integer CONSTRAINT posval CHECK ( checkme > 0 ) );
Informational Queries
Here are some queries to show you the constraints, triggers and
indexes for your specific table or tables. To use these queries,
substitute the table name in question for each of the where clauses, or wrap
the query into a function passing in the table name.
-- What indexes are on my table? select * from pg_indexes where tablename = 'tablename'; -- What triggers are on my table? select c.relname as "Table", t.tgname as "Trigger Name", t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled", t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table", p.proname as "Function Name" from pg_trigger t, pg_class c, pg_class cc, pg_proc p where t.tgfoid = p.oid and t.tgrelid = c.oid and t.tgconstrrelid = cc.oid and c.relname = 'tablename'; -- What constraints are on my table? select r.relname as "Table", c.conname as "Constraint Name", contype as "Constraint Type", conkey as "Key Columns", confkey as "Foreign Columns", consrc as "Source" from pg_class r, pg_constraint c where r.oid = c.conrelid and relname = 'tablename';
|
A trigger to update the row count is needed. The actual row count is important for the application and count(*) by nature is too slow.
The way to implement a proper row counter is to create a trigger on the table which needs to be counted. This trigger will increment or decrement the count on insert or delete.
There are a couple of interesting issues with the row counting implementation. The first is that the counter trigger function can work for both insert and delete and with any table by using trigger information in plpgsql. The second is the issue of initializing the rowcount table.
Suppose you had the table mystuff and set up the table rowcount to hold the row count of many tables.
CREATE TABLE mystuff ( name text NOT NULL, description text, PRIMARY KEY (name)); CREATE TABLE rowcount ( table_name text NOT NULL, total_rows bigint, PRIMARY KEY (table_name));
Most people are familiar with the use of OLD and NEW variables in trigger functions, but there are several other informational variables available.
| Name | Meaning |
|---|---|
| TG_NAME | Name of the trigger |
| TG_WHEN | BEFORE or AFTER |
| TG_LEVEL | ROW or STATEMENT |
| TG_OP | INSERT, UPDATE or DELETE |
| TG_RELID | Relation OID of table with trigger |
| TG_RELNAME | Table name of table with trigger |
| TG_NARGS | Number of arguments in row being updated |
| TG_ARGV[] | Text array of datatypes in row being updated |
The count_rows() trigger function uses TG_OP and TG_RELNAME. TG_OP indicates whether it is a DELETE or INSERT and TG_RELNAME is used to store the count in the countrows table by table name. This is the function:
CREATE OR REPLACE FUNCTION count_rows() RETURNS TRIGGER AS ' BEGIN IF TG_OP = ''INSERT'' THEN UPDATE rowcount SET total_rows = total_rows + 1 WHERE table_name = TG_RELNAME; ELSIF TG_OP = ''DELETE'' THEN UPDATE rowcount SET total_rows = total_rows - 1 WHERE table_name = TG_RELNAME; END IF; RETURN NULL; END; ' LANGUAGE plpgsql;
There are many ways the various TG variables can be used to write generic triggers. Different courses of action can be taken based on the exact definition of the trigger. The data in the row can be accessed via NEW and OLD and information about their datatypes is available if decisions or actions need to be done based on the data type.
Now we will look at what is necessary to initialize the trigger. If the trigger function and the tables are all defined in the same transaction, then initialization is not necessary, except for the creation of the row in rowcount for the table in question. However, most of the time if row counting functionality is being added to an existing table, the base row count must be ascertained.
For an existing table, the initialization must be done in a single transaction. The target table is locked to prevent updates during this initialization. Then the trigger is created and the rowcount is inserted with the current row count. Once the initialization transaction is committed, then your counter is operational. Don't forget to test it!
BEGIN;
-- Make sure no rows can be added to mystuff until we have finished
LOCK TABLE mystuff IN SHARE ROW EXCLUSIVE MODE;
create TRIGGER countrows
AFTER INSERT OR DELETE on mystuff
FOR EACH ROW EXECUTE PROCEDURE count_rows();
-- Initialise the row count record
DELETE FROM rowcount WHERE table_name = 'mystuff';
INSERT INTO rowcount (table_name, total_rows)
VALUES ('mystuff', (SELECT COUNT(*) FROM mystuff));
COMMIT;
--
-- Testing
--
insert into mystuff values ('abacus','mathmatics');
insert into mystuff values ('bee','insect');
select * from rowcount;
insert into mystuff values ('dog','pet');
insert into mystuff values ('cathedral','building');
select * from rowcount;
select * from mystuff;
delete from mystuff where name='abacus';
select * from rowcount;
select * from mystuff;
|
The topic of using a trigger to count rows was discussed in Article #49-1. A subsequent question was raised regarding the effect of multiple triggers on the logic of the counter. The question was whether it was possible for one trigger to increment the counter and then have the subsequent trigger abort the original statement.
When a statement is executed one or more triggers are also executed to implement foreign key integrity, additional functionality such as the row counter and also CHECK clauses. All of the triggers are executed in the context of the transaction of the original statement. They are executed (arbitrarily) in alphabetical order. If any one of the triggers cause the original statement to be aborted, then all of the triggers' actions will also be rolled back. Everything is in the same transaction.
If the trigger for counting succeeded and a subsequent trigger rolled back the transaction, then the effects of the counting trigger would also be rolled back.
|
This is a nice little view which will list triggers in your database. This is a very helpful addition to your pg_catalog utilities because there is no psql utility to show triggers individually. Triggers are usually shown in relation to the tables they are attached to.
create view showtriggers as select trg.tgname as trigger_name , tbl.relname as table_name, p.proname as function_name, case trg.tgtype & cast(2 as int2) when 0 then 'AFTER' else 'BEFORE' end as trigger_type, case trg.tgtype & cast(28 as int2) when 16 then 'UPDATE' when 8 then 'DELETE' when 4 then 'INSERT' when 20 then 'INSERT, UPDATE' when 28 then 'INSERT, UPDATE, DELETE' when 24 then 'UPDATE, DELETE' when 12 then 'INSERT, DELETE' end as trigger_event from pg_trigger trg, pg_class tbl, pg_proc p where trg.tgrelid = tbl.oid and trg.tgfoid = p.oid;
|
One technique for tracking updates of a table is to store the old values into a another table as a log of changes. All or most of the columns of the original table are duplicated with in the log table and a timestamp or other key is added.
This technique is best done with a trigger instead of an UPDATE RULE, particularly if the UPDATE RULE was to apply to a view. A possible update rule might be this:
CREATE RULE update_org AS ON UPDATE TO orig_table_view DO INSTEAD ( UPDATE orig_table set col=NEW.col... INSERT INTO log_table ...( old.col... ) );This rule will not work because when the original table is updated, the OLD values have changed and are visible. Therefore the insert into the log table logs the new values rather than the old ones. If you switch the order of the UPDATE and INSERT statements, this RULE should work for logging.
However, with TRIGGERS you do not need to wrestle with the visibility rules. You will still need the rule for updating the view. The trigger will be on the base table and insert the log record only.
To both techniques in action see Logging Updates.
|
It is very simple to add a column representing an modification timestamp to any row in any table. This question is asked fairly often, particularly from MySQL converts. MySQL supports a feature where the first timestamp typed column in a table is automatically updated to the current timestamp any time a row is inserted or updated. To avoid the automatic update in MySQL you must use a datetime type instead of the timestamp type. (Some believe it is sacrilegious for the server to update data without specific requests, but the feature is useful.)
If you have a modification timestamp column with the DEFAULT set to now(), then each INSERT will record the current timestamp. This only works for INSERTS and not UPDATES since the DEFAULT values are only evaluated for INSERTS.
To update the modification timestamp on update, you will need a very simple function and a very simple trigger. If you had several tables with the same column name for the modification timestamp, you can reuse the function, but you will need to define the trigger for each table explicitly.
The trigger function, simply sets the NEW column to now().
create or replace function ts_row() returns TRIGGER as ' BEGIN NEW.mod_date = now(); RETURN NEW; END; ' language 'plpgsql';
For any table which has a timestamp column named mod_date, this function can be invoked as an UPDATE trigger.
create trigger upd_any_table BEFORE UPDATE ON any_table for each row EXECUTE PROCEDURE ts_row(); create trigger upd_other_table BEFORE UPDATE ON other_table for each row EXECUTE PROCEDURE ts_row();
A working example of this function and trigger is available over in General TidBits under Timestamped Rows.
Further discussion on this topic included a discussion on the implementation of the modification timestamp column as a RULE instead of a TRIGGER and a proposal to add an "ON UPDATE" qualification to the DEFAULT specification. RULES require more mechanics since they require an updatable view and the rule needs to be qualified so that it is not recursive. It was also pointed out that adding an ON UPDATE to the DEFAULT specification would not (necessarily) override explicit updates to the modification timestamp field.
Timestamping of rows in conjunction with the system of no deletions used to be a part of the University Postgres and was called "timetravel". Timetravel was the ability (pre-vacuum) to qualify a data set based on a time frame and enabled you to ask what the data values were for a week ago at 2:00pm. Timetravel was removed from PostgreSQL because the overhead of row size and update costs was considered too high. Timetravel is not too hard to reconstruct, however, The logging of a modification timestamp as described above is the key requirements.
|
There are many different models for handling tree structures in SQL.
Google it and see what you can find. Here I am presenting a practical
and relatively simple approach to a trees with an example using
items and topics. The example is based on, say, a google search.
There are many topics under which are items (eg. URLS) as well as other topics.
The topics are the structures of the tree and the items are the
leaves. This example assumes a web interface, but we will not go
into that here. What we will concentrate on is handling the selection
and manipulation of the tree that will facilitate writing a good
user interface.
To create the basic structure of the tree there will be an item table, a topic table and an item_topic table. Each topic in the topic table will reference its parent topic, creating the tree. The item_topic table is an aggregate table which maps items to topics without limiting any item to just one topic.
CREATE TABLE items (
item_id SERIAL PRIMARY KEY,
item_name TEXT);
CREATE TABLE topics (
topic_id SERIAL PRIMARY KEY,
topic_name TEXT,
parent_id INTEGER REFERENCES topics (topic_id) );
CREATE TABLE item_topics (
item_id INTEGER,
topic_id INTEGER,
FOREIGN KEY (item_id) REFERENCES items (item_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (topic_id) REFERENCES topics (topic_id)
ON UPDATE CASCADE
PRIMARY KEY (item_id, topic_id) );
To insert an item, an insert into both the item table and the topic table is required. In the following example, a topic and item are inserted and then the item_topics record is created the hard way. In the user interface one would have a nice interface which allowed you to assign topics to items--a drop down or a list.
INSERT INTO topics VALUES (default, 'History', NULL );
INSERT INTO items VALUES (default, 'Studying History' );
INSERT INTO item_topics VALUES (
(SELECT item_id FROM items
WHERE item_name = 'Studying History'),
(SELECT topic_id FROM topics
WHERE topic_name='History' ));
The aggregate table, item_topics, should be updated
if ever the item table key or the topic table key is updated.
If an item is deleted, it should be removed from the aggregate
table. The CASCADE directives implement this policy.
A view of the item_topics table would probably be helpful to the user interface developer. This would show the pertinent data from joining the three tables together. Here we can also see some "real" data.
CREATE VIEW it AS
SELECT i.item_id, i.item_name, it.topic_id, t.parent_id, t.topic_name
FROM item_topics it JOIN items i USING (item_id)
JOIN topics t USING (topic_id);
elein=# SELECT item_name, topic_name FROM it;
item_name | topic_name
------------------------------------+-----------------------
Studying History | History
Spanish California | California History
Gold Rush | California History
Dot Coms | California History
American Presidents | American History
Civil War | American History
Civil Rights | American History
The Making of the Constitution | American History
The Golden Gate | San Francisco History
Digging up Market Street | San Francisco History
Ada Lovelace: Scientific Computing | History
Ada Lovelace: Scientific Computing | Ada
The Camel Book | Perl
Bad Perl, Good Perl | Perl
Comparing Databases | Databases
General Bits | Postgres
Practicing Postgres | Postgres
UCBerkeley Archives: Ingres | Ingres
Relational Technology, Inc. | Ingres
(19 rows)
But what happens if a topic is deleted? If the topic had any
items and we deleted the topic, the items would be orphans if
they just happened to not be associated with another topic.
If the topics had any children topics, they would lose their
link to their grandparents, possibly disrupting the associations
of the tree.
If the topic to be deleted had both items and a parent, then
the parent would inherit the deleted topic's items.
If a topic to be deleted had child topics and a parent topic,
then those children will have their parent id reassigned to
their "grand"parent.
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009