Triggers and rule systems

PostgreSQL provides triggers and rules systems to automatically perform a certain function when an event like INSERT, UPDATE, or DELETE is performed.

Tip

Triggers and rules cannot be defined on the SELECT statements except for the _RETURN, which is used in the internal implementation of PostgreSQL views.

From the functionality point of view, the trigger system is more generic; it can be used to implement complex actions more easily than rules. However, both trigger and rule systems can be used to implement the same functionality in several cases.

From the performance point of view, rules tend to be faster than triggers; but triggers tend to be simpler and more compatible with other RDBMs, since the rule system is a PostgreSQL extension.

The PostgreSQL rule system

Creating a rule will either rewire the default rule, or create a new rule for a specific action on a specific table or view. In other words, a rule on an insert action can change the insert action behavior, or can create a new action for the insert. When using the rule system, one needs to note that it is based on the C macro system. This means one can get strange results when it is used with volatile functions such as random(), and sequence functions such as nextval(). The following example shows how tricky the rule system can be. Let us assume that we would like to audit the table for car. For this reason, a new table called car_log will be created to keep a track of all the actions on the car table, such as UPDATE, DELETE, and INSERT. One can achieve this by using the trigger system, as follows:

CREATE TABLE car_log (LIKE car);

ALTER TABLE car_log
ADD COLUMN car_log_action varchar (1) NOT NULL,
ADD COLUMN car_log_time TIMESTAMP WITH TIME ZONE NOT NULL;
CREATE RULE car_log AS
    ON INSERT TO car
    DO ALSO
        INSERT INTO car_log (car_id, number_of_owners, regestration_number, number_of_doors, car_log_action, car_log_time)
        VALUES (new.car_id, new.number_of_owners, new.regestration_number, new.number_of_doors, 'I', now());

The preceding code creates the car_log table, which has a structure similar to the car table. The car_log table also has two additional attributes to log the actions: such as insert (indicated in the example as I), and the action time. The preceding code also creates a rule on the car table to cascade the insert on the car table to the car_log table. To test the code, let us insert a record, and examine the contents of the car_log table, as follows:

car_portal=# INSERT INTO car (car_id, number_of_owners, regestration_number, number_of_doors) VALUES (1, 2, '2015xyz', 3);
INSERT 0 1
car_portal=# SELECT * FROM car;
-[ RECORD 1 ]-------+--------
car_id              | 1
number_of_owners    | 2
regestration_number | 2015xyz
number_of_doors     | 3

car_portal=# SELECT * FROM car_log;
-[ RECORD 1 ]-------+---------------------------
car_id              | 1
number_of_owners    | 2
regestration_number | 2015xyz
number_of_doors     | 3
car_log_action      | I
car_log_time        | 2015-02-04 10:48:56.779-08

As the preceding example shows, everything goes as expected. One record is inserted in the car_log table, and that record is identical to the one inserted in the car table. However, as said earlier, the rules system is built on macros, which cause some issues, as shown in the following example:

car_portal=# TRUNCATE car cascade;
NOTICE:  truncate cascades to table "advertisement"
...
TRUNCATE TABLE
car_portal=# ALTER sequence car_car_id_seq RESTART;
ALTER SEQUENCE
car_portal=# INSERT INTO car (car_id, number_of_owners, regestration_number, number_of_doors) VALUES (DEFAULT, 2, '2015xyz', 3);
INSERT 0 1
car_portal=# SELECT * FROM ONLY car;
-[ RECORD 1 ]-------+--------
car_id              | 1
number_of_owners    | 2
regestration_number | 2015xyz
number_of_doors     | 3

car_portal=# SELECT * FROM car_log;
-[ RECORD 1 ]-------+---------------------------
car_id              | 2
number_of_owners    | 2
regestration_number | 2015xyz
number_of_doors     | 3
car_log_action      | I
car_log_time        | 2015-02-04 10:56:48.553-08

In the preceding example, the car table is truncated, and car_car_id_seq is restarted to compare the results easily. Note that the records in the car table and the car_log tables are not identical. The car_id in the car table is 1 while in the car_log table it is 2. One can notice that the DEFAULT keyword is used to indicate the car_id which is nextval('car_car_id_seq'::regclass), shown as follows:

car_portal=# d car
                              Table "car_portal_app.car"
       Column        |  Type   |                      Modifiers
---------------------+---------+------------------------------------------------------
 car_id              | integer | not null default nextval('car_car_id_seq'::regclass)
...

The car_log rule caused the sequence to be executed another time, because it did not substitute NEW.car_id with 1, but substituted it with nextval('car_car_id_seq'::regclass) instead.

Writing rules is often quicker than writing triggers, and the code is crisper and shorter. A common case of using rules is to ignore the CRUD operation on the table in order to protect the table against data changes. This scenario can be used to protect lookup tables or to ignore the CRUD operations, for example. In the car web portal database, let us assume that we would like to disable the logging of the user's search. A very simple technique to do so without changing the client codes is as follows:

CREATE RULE account_search_log_insert AS
    ON INSERT TO account_search_history
    DO INSTEAD NOTHING;

When creating a rule, one can have a conditional rule, that is, one can rewrite the action if a certain condition is met, as shown in the following rule synopsis. However, one cannot have conditional rules for INSERT, UPDATE, and DELETE on views without having unconditional rules. To solve this problem, one can create an unconditional dummy rule. Rules on views are one way of implementing updatable views.

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

where event can be one of:

    SELECT | INSERT | UPDATE | DELETE

Finally, when defining rules, one should take care of infinite recursion, similar to triggers. Wrong rule definitions can cause the error: ERROR: infinite recursion detected in rules for relation ….

The PostgreSQL trigger system

PostgreSQL triggers a function when a certain event occurs on a table, view, or foreign table. Triggers are executed when a user tries to modify the data through any of the data manipulation language (DML) events, including INSERT, UPDATE, DELETE, or TRUNCATE. The trigger synopsis is as follows:

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

The trigger time context is one of the following:

  • BEFORE: This is applied on tables only, and is fired before the constraints are checked and the operation is performed. It is useful for checking data constraints on several tables. It is not possible to model using the referential integrity constraints.
  • AFTER: This too is applied on tables only, and is fired after the operation is performed. It is useful for cascading the changes to other tables. An example use case is data auditing.
  • INSTEAD OF: This is applied on views, and is used to make views updatable.

When the trigger is marked for each row, then the trigger function will be executed for each row that has been affected by the CRUD operation. A statement trigger is only executed once per operation. When the WHEN condition is supplied, then only the rows that fulfill the condition will be handled by the trigger. Finally, triggers can be marked as CONSTRAINT to control when they can be executed; the trigger can be executed after the end of the statement or at the end of the transaction. The constraint trigger must be AFTER and FOR EACH ROW, and the firing time constraint triggers controlled by the following options:

  • DEFERRABLE: This marks the trigger as deferrable, which will cause the trigger firing to be postponed till the end of the transaction.
  • INITIALLY DEFERRED: This specifies the time when the trigger is to be executed. This means that the trigger will be executed at the end of the transaction.
  • NOT DEFERRABLE: This is the default behavior of the trigger, which will cause the trigger to be fired after each statement in the transaction.
  • INITIALLY IMMEDIATE: This specifies the time when the trigger is to be executed. This means that the trigger will be executed after each statement.

    Tip

    Trigger names define the execution order of the triggers, which have the same firing time context alphabetically.

    The firing time options: DEFERRABLE, INITIALLY DEFERRED, NOT DEFERRABLE, and INITIALLY IMMEDIATE, can also be applied to constraint. These options are useful when PostgreSQL interacts with external systems such as memcached. For example, let us assume that we have a trigger on a table that is cached; whenever the table is updated, the cache is also updated. Since the caching system is not transactional, we can postpone the update until the end of the transaction to guarantee data consistency.

To explain the trigger system, let us redo the car_log table example using triggers. First of all, notice that the trigger type is AFTER trigger, since data should first be checked against the car table constraint before inserting it in the new table. To create a trigger, one needs to create a function, as follows:

CREATE OR REPLACE FUNCTION car_log_trg () RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO car_log SELECT NEW.*, 'I', NOW();
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO car_log SELECT NEW.*, 'U', NOW();
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO car_log SELECT OLD.*, 'D', NOW();
  END IF;
  RETURN NULL; --ignored since this is after trigger
END;
$$
LANGUAGE plpgsql;

To create the trigger, one needs to execute the following statement:

CREATE TRIGGER car_log AFTER INSERT OR UPDATE OR DELETE ON car FOR EACH ROW EXECUTE PROCEDURE car_log_trg ();

The TRIGGER function should fulfill the following requirements:

  • Return type: The TRIGGER function should return the trigger pseudo type.
  • Return value: The TRIGGER function must return a value. The value is often NULL for AFTER ... EACH ROW and for statement-level triggers or record/row with the exact structure of the table that fired the trigger.
  • No arguments: The trigger function must be declared without an argument, even if one needs to pass an argument to it. The passing of an argument is achieved via the TG_ARG variable.

When the trigger function is created, several variables, such as TG_ARG and NEW, are created automatically. Other variables that are created are listed in the following table:

Trigger variable

Data type

Description

NEW

RECORD

It holds the row that is inserted or updated. In the case of statement level trigger, it is NULL.

OLD

RECORD

It holds the old row that is updated or deleted. In the case of statement level trigger, it is NULL.

TG_NAME

NAME

The trigger name.

TG_OP

NAME

The trigger operation, which can have one of the following values:

INSERT

UPDATE

DELETE

TRUNCATE

TG_WHEN

NAME

The time when the trigger is fired. It can have one of the following values:

AFTER

BEFORE

TG_RELID

OID

The relation OID. One can get the relation name by casting it to text using regclass::text.

TG_TABLE_NAME

NAME

The trigger table name.

TG_TABLE_SCHEMA

NAME

The trigger table schema name.

TG_NARG

INTEGER

Number of arguments passed to the trigger.

TG_ARG[]

TEXT array

The trigger argument. The indexing starts from zero and a wrong index returns NULL.

For a row-level trigger, which is fired BEFORE the actual operation, returning null values will cancel the operation. This means that the next trigger will not be fired, and the affected row will not be deleted, updated, or inserted. For the trigger that is fired AFTER the operation or a statement-level trigger, the return value will be ignored; however, the operation will be aborted if the trigger function raises an exception or an error due to the relational database's transactional behavior.

Tip

In the preceding auditing example, if one changes the car table definition such as adding or dropping a column, the trigger function on the car table will fail, leading to the ignoring of the newly inserted or updated row. One could solve this by using exception trapping in the trigger definition.

Triggers with arguments

In the following example, another general auditing technique will be presented which can be applied to several tables, while some table columns can be excluded from auditing.

The new editing techniques use the hstore extension. hstore defines a hash map data type, and provides a set of functions and operators to handle this data type. In the new auditing technique, the table rows will be stored as a hash map. The first step is to create the hstore extension and a table where the audited data will be stored, as follows:

SET search_path to car_portal_app;
CREATE extension hstore;
CREATE TABLE car_portal_app.log
(
  schema_name text NOT NULL,
  table_name text NOT NULL,
  old_row hstore,
  new_row hstore,
  action TEXT check (action IN ('I','U','D')) NOT NULL,
  created_by text NOT NULL,
  created_on timestamp without time zone NOT NULL
);

The second step is to define the trigger function, as follows:

CREATE OR REPLACE FUNCTION car_portal_app.log_audit() RETURNS trigger  AS $$
DECLARE
  log_row log;
  excluded_columns text[] = NULL;
BEGIN
  log_row = ROW (
    TG_TABLE_SCHEMA::text,                       
    TG_TABLE_NAME::text,                          
    NULL,
    NULL,
    NULL,
    current_user::TEXT,
    current_timestamp
    );
      
  IF TG_ARGV[0] IS NOT NULL THEN
    excluded_columns = TG_ARGV[0]::text[];
  END IF;

  IF (TG_OP = 'INSERT') THEN
    log_row.new_row = hstore(NEW.*) - excluded_columns;
    log_row.action ='I';
  ELSIF (TG_OP = 'UPDATE' AND (hstore(OLD.*) - excluded_columns!= hstore(NEW.*)-excluded_columns)) THEN
      log_row.old_row = hstor(OLD.*) - excluded_columns;
    log_row.new_row = hstore(NEW.* )- excluded_columns;
    log_row.action ='U';
  ELSIF (TG_OP = 'DELETE') THEN
    log_row.old_row = hstore (OLD.*) - excluded_columns;
    log_row.action ='D';
  ELSE
    RETURN NULL; -- update on excluded columns
  END IF;

  INSERT INTO log SELECT log_row.*;
     
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The preceding function defines a variable log_row of type log, and populates this variable with the trigger table name, trigger table schema, current user, and the current timestamp using the row construct. Moreover, the preceding trigger function parses TG_ARGV to determine if some columns need to be excluded from auditing. Note that the excluded columns are passed as a text array. Finally, the trigger function populates log.action, log.old_row, and log.new_row based on the TG_OP variable.

To apply the preceding trigger on the car table, assuming that the number_of_doors attribute should be excluded from tracking, one can create the trigger as follows:

CREATE TRIGGER car_log_trg  AFTER INSERT OR UPDATE OR DELETE ON car_portal_app.car FOR EACH ROW EXECUTE PROCEDURE log_audit('{number_of_doors}');

The array lateral {number_of_doors} is passed to the function log_audit and accessed via the TG_ARG variable. Finally, the expression hstore(NEW.*) - excluded_columns is used to convert the NEW variable to the hstore type, and then delete the keys specified in the excluded_columns array from the converted hstore. The following example shows the trigger behavior for the insert statement:

car_portal=# INSERT INTO car_portal_app.car (car_id, number_of_owners, registration_number, number_of_doors) VALUES (DEFAULT, 1, '2015abcde', 5);
INSERT 0 1

car_portal=# TABLE car_portal_app.log;
-[ RECORD 1 ]----------------------------------------------------------------------------
schema_name | car_portal_app
table_name  | car
old_row     |
new_row     | "car_id"=>"10", "number_of_owners"=>"1", "registration_number"=>"2015abcde"
action      | I
created_by  | postgres
created_on  | 2015-02-14 11:56:47.483

Using triggers to make views updatable

For views that are not automatically updatable, the trigger system can be used to make them updatable. The view seller_account_information, which shows the information about the seller account, is not automatically updatable, as shown next:

CREATE OR REPLACE VIEW seller_account_info AS
SELECT
  account.account_id,
  first_name,
  last_name,
  email,
  password,
  seller_account_id,
  total_rank,
  number_of_advertisement,
  street_name,
  street_number,
  zip_code ,
  city
FROM
  account INNER JOIN
  seller_account ON (account.account_id = seller_account.account_id);

car_portal=# SELECT is_insertable_into FROM information_schema.tables WHERE table_name = 'seller_account_info';
-[ RECORD 1 ]------+---
is_insertable_into | NO

The following trigger function assumes that account_id and seller_account_id are always generated using the default values, which are the sequences generated automatically when creating a serial data type. This is often a good approach and relieves the developer from checking the table for a unique constraint before inserting new rows, and in keeps the primary key values without big gaps. Furthermore, the trigger function assumes that the primary keys cannot be changed for the same reason. Changing the primary keys might also cause problems when the default foreign keys options, cascade delete and cascade update, are not used. Finally, note that the trigger functions return NEW for the INSERT and UPDATE operations, OLD for the DELETE operation, and NULL in the case of an exception.

Returning the proper value is important to detect the number of rows that are affected by the operation. It is also very important to return the proper value when using the RETURNING keyword, as shown in the following TRIGGER function code:

CREATE OR REPLACE FUNCTION seller_account_info_update () RETURNS TRIGGER AS
$$
DECLARE
  acc_id INT;
  seller_acc_id INT;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    WITH inserted_account AS (
      INSERT INTO car_portal_app.account (account_id, first_name, last_name, password, email)
      VALUES (DEFAULT, NEW.first_name, NEW.last_name, NEW.password, NEW.email) RETURNING account_id
      ), inserted_seller_account AS (INSERT INTO car_portal_app.seller_account(seller_account_id, account_id, total_rank, number_of_advertisement, street_name, street_number, zip_code, city)
      SELECT nextval('car_portal_app.seller_account_seller_account_id_seq'::regclass), account_id, NEW.total_rank, NEW.number_of_advertisement, NEW.street_name, NEW.street_number, NEW.zip_code, NEW.city FROM inserted_account RETURNING account_id, seller_account_id
      ) SELECT account_id, seller_account_id INTO acc_id, seller_acc_id FROM inserted_seller_account;
      NEW.account_id = acc_id;
      NEW.seller_account_id = seller_acc_id;
      RETURN NEW;
    ELSIF (TG_OP = 'UPDATE' AND OLD.account_id = NEW.account_id AND OLD.seller_account_id = NEW.seller_account_id) THEN
      UPDATE car_portal_app.account
      SET first_name = new.first_name, last_name = new.last_name, password= new.password, email = new.email
      WHERE account_id = new.account_id;
      UPDATE car_portal_app.seller_account
      SET total_rank = NEW.total_rank, number_of_advertisement = NEW.number_of_advertisement, street_name = NEW.street_name, street_number = NEW.street_number, zip_code = NEW.zip_code, city = NEW.city
      WHERE seller_account_id = NEW.seller_account_id;
      RETURN NEW;
      
    ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM car_portal_app.seller_account WHERE seller_account_id = OLD.seller_account_id;
      DELETE FROM car_portal_app.account WHERE account_id = OLD.account_id;
      RETURN OLD;
    ELSE
      RAISE EXCEPTION 'An error occurred for % operation', TG_OP;
      RETURN NULL;
  END IF;
END;
$$
LANGUAGE plpgsql;

To run and test the trigger function, let us execute the following SQL statements:

CREATE  TRIGGER seller_account_info_trg INSTEAD OF INSERT OR UPDATE OR DELETE ON car_portal_app.seller_account_info FOR EACH ROW  EXECUTE PROCEDURE seller_account_info_update ();

car_portal=# INSERT INTO car_portal_app.seller_account_info (first_name, last_name, password, email, total_rank, number_of_advertisement, street_name, street_number, zip_code, city) VALUES
('test_first_name', 'test_last_name', 'test_password', '[email protected]', NULL, 0, 'test_street_name', 'test_street_number', 'test_zip_code', 'test_city') RETURNING account_id, seller_account_id;
 account_id | seller_account_id
------------+-------------------
         14 |                 8
(1 row)

INSERT 0 1
car_portal=# UPDATE car_portal_app.seller_account_info set email = '[email protected]' RETURNING seller_account_id;
 seller_account_id
-------------------
                 8
(1 row)

UPDATE 1
car_portal=# DELETE FROM car_portal_app.seller_account_info;
DELETE 1
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.163.144