Waiting for 8.5 – conditional triggers

On 20th of November Tom Lane committed patch by Takahiro Itagaki which adds interesting functionality:

ADD a WHEN clause TO CREATE TRIGGER, allowing a BOOLEAN expression TO be
checked TO determine whether the TRIGGER should be fired.
 
FOR BEFORE triggers this IS mostly a matter OF spec compliance; but FOR AFTER
triggers it can provide a noticeable performance improvement, since queuing OF
a DEFERRED TRIGGER event AND re-fetching OF the ROW(s) at END OF statement can
be short-circuited IF the TRIGGER does NOT need TO be fired.
 
Takahiro Itagaki, reviewed BY KaiGai Kohei.

Description is pretty self explanatory, but let's see how it looks. Since it is said that the best way to see the difference is to use it for DEFERRED AFTER triggers, let's make such test.

To test it, let's imagine a simple table, which stores information about when any given room in hotel is reserved, with checks that it will not allow multiple reservations on the same room at the same time.

So, we have this table:

CREATE TABLE reservations (
    id serial PRIMARY KEY,
    room_number INT4 NOT NULL,
    reserved_from timestamptz NOT NULL,
    reserved_to timestamptz NOT NULL,
    reserved_by TEXT
);

To make the checks work we can use this function:

CREATE OR REPLACE FUNCTION check_conflicts() RETURNS TRIGGER AS
$BODY$
DECLARE
    conflicting_row record;
BEGIN
    RAISE NOTICE 'Checking conflicts for id: %', NEW.id;
    SELECT * INTO conflicting_row
        FROM reservations
        WHERE id <> NEW.id AND room_number = NEW.room_number AND ( NEW.reserved_from, NEW.reserved_to) OVERLAPS ( reserved_from, reserved_to )
        LIMIT 1;
    IF NOT FOUND THEN
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'Check failed - conflict with %', conflicting_row;
END;
$BODY$
LANGUAGE 'plpgsql';

This RAISE NOTICE is only to show when trigger function will be actually called.

Now, whenever I insert row, it should be checked:

CREATE CONSTRAINT TRIGGER check_conflicts_i
    AFTER INSERT ON reservations
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE check_conflicts()
;

And when update happens, that modifies room number or time period – it should be checked again:

CREATE CONSTRAINT TRIGGER check_conflicts_u
    AFTER UPDATE ON reservations
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN ( ( OLD.room_number <> NEW.room_number ) OR ( OLD.reserved_from <> NEW.reserved_from ) OR ( OLD.reserved_to <> NEW.reserved_to ) )
    EXECUTE PROCEDURE check_conflicts()
;

So, how it works?

First, let's insert 2 rows, in single transaction, both ok:

# BEGIN;
BEGIN
 
*# INSERT INTO reservations (room_number, reserved_from, reserved_to, reserved_by) VALUES (1, '2009-10-01', '2009-10-07', 'depesz');
INSERT 0 1
 
*# INSERT INTO reservations (room_number, reserved_from, reserved_to, reserved_by) VALUES (2, '2009-10-14', '2009-10-16', 'marvin');
INSERT 0 1
 
*# commit;
NOTICE:  Checking conflicts FOR id: 1
NOTICE:  Checking conflicts FOR id: 2
COMMIT

Nice. Trigger was called on commit time, so it works well.

Now, let's see if it will fail well for conflict. Still on insert time:

# BEGIN;
BEGIN
 
*# INSERT INTO reservations (room_number, reserved_from, reserved_to, reserved_by) VALUES (1, '2009-10-06', '2009-10-20', 'zaphod');
INSERT 0 1
 
*# INSERT INTO reservations (room_number, reserved_from, reserved_to, reserved_by) VALUES (3, '2009-10-14', '2009-10-16', 'ford');
INSERT 0 1
 
*# commit;
NOTICE:  Checking conflicts FOR id: 3
ERROR:  CHECK failed - conflict WITH (1,1,"2009-10-01 00:00:00+02","2009-10-07 00:00:00+02",depesz)

Nice. And now let's play with updates. First, let's see the data:

# SELECT * FROM reservations;
 id | room_number |     reserved_from      |      reserved_to       | reserved_by
----+-------------+------------------------+------------------------+-------------
  1 |           1 | 2009-10-01 00:00:00+02 | 2009-10-07 00:00:00+02 | depesz
  2 |           2 | 2009-10-14 00:00:00+02 | 2009-10-16 00:00:00+02 | marvin
(2 ROWS)

OK. And now:

# BEGIN;
BEGIN
 
*#     UPDATE reservations SET reserved_by = 'arthur' WHERE id = 2;
UPDATE 1
 
*# COMMIT;
COMMIT

It worked well – trigger was not called – there is no NOTICE. It wasn't called simply because we didn't modify any rows that would make the reservation change important data.

Now, let's test some update that will call trigger and fail:

# BEGIN;
BEGIN
 
*#     UPDATE reservations SET reserved_from = '2009-10-02', room_number = 1 WHERE id = 2;
UPDATE 1
 
*# COMMIT;
NOTICE:  Checking conflicts FOR id: 2
ERROR:  CHECK failed - conflict WITH (1,1,"2009-10-01 00:00:00+02","2009-10-07 00:00:00+02",depesz)

Nice. everything worked well.

It would be cool to see/know what is the time difference between skipping trigger call in new way, and in old way.

So, let's check, but with a bit simpler structure:

CREATE TABLE test (i INT4);
INSERT INTO test (i) SELECT generate_series(1,100000) x;

Now, on this table I will create simple trigger:

CREATE OR REPLACE FUNCTION test_u() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    IF NEW.i > 0 THEN
        RETURN NEW;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
 
CREATE CONSTRAINT TRIGGER test_u
    AFTER UPDATE ON test
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE test_u()
;

It does nothing, but the important part is that it checks NEW.i as first command, and skips rest of function in case it is > 0 (which is always in our case).

This will be more or less the same situation as with “WHEN" in trigger/constraint definition.

So. How fast it is? Let's update all those rows, and check time.

So, I wrote an SQL script:

DROP TABLE test;
CREATE TABLE test (i INT4 );
INSERT INTO test (i) SELECT generate_series(1,100000) x;
 
CREATE OR REPLACE FUNCTION test_u() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    IF NEW.i > 0 THEN
        RETURN NEW;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
 
CREATE CONSTRAINT TRIGGER test_u
    AFTER UPDATE ON test
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE test_u()
;
\timing
UPDATE test SET i = i + 1;

And ran it 10 times. Got times:

  • 500.248 ms
  • 519.726 ms
  • 523.155 ms
  • 517.660 ms
  • 484.637 ms
  • 514.459 ms
  • 529.793 ms
  • 509.310 ms
  • 488.942 ms
  • 505.672 ms

Average time: 509.36ms.

And now, let's change the trigger definition, so that it will be skipped not in PL/pgSQL function, but it will simply not be called at all:

CREATE CONSTRAINT TRIGGER test_u
    AFTER UPDATE ON test
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN ( NEW.i <= 0 )
    EXECUTE PROCEDURE test_u()
;

Same test, 10 executions. Times:

  • 207.712 ms
  • 235.050 ms
  • 303.190 ms
  • 207.071 ms
  • 224.936 ms
  • 240.334 ms
  • 202.744 ms
  • 205.062 ms
  • 238.946 ms
  • 276.333 ms

Average time: 234.14ms.

So – new approach is clearly faster – as promised in commit log.

Since authors said that there will be no speedup in before, or after, but not deferred, triggers (which is pretty obvious) – theoretically there is no point in using conditional triggers in these cases, but I think I will still use it – if only for clarity. If there is a trigger that does something only sometimes, I think that specifying it explicitly in trigger (not function) definition will make future maintenance easier.

And besides – You will see the “WHEN" in standard \d table.