Postgresql – Call multiple functions from trigger

functionsplpgsqlpostgresqltrigger

I run PostgreSQL-9.2.4

Is it possible to call 2 functions from a trigger?

Let's say I have two functions for two different tables to be executed if following trigger fires:

Trigger:

CREATE TRIGGER start ON system_status FOR EACH ROW
WHEN ((new.event = start_task))
EXECUTE PROCEDURE ...()

Function 1:
(When task starts => remove any previously assigned next task for this system)

CREATE FUNCTION void_next_task() RETURNS trigger AS $$

BEGIN
  DELETE FROM tasks_status ts
  WHERE ts.system = NEW.system
  AND ts.event = 'next_task';
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

Function 2:
(If inserted combination of task and system already presented in the table => mark any earlier records with this combination as deleted)

CREATE FUNCTION void_dup_task() RETURNS trigger AS $$

BEGIN
  UPDATE system_status ss
  SET deleted = 'TRUE'
  WHERE ss.system = NEW.system
  AND ss.task = NEW.task
  AND ss.deleted IS FALSE;
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

So I ended up with following ways to resolve it:

To have a trigger which calls two functions;
To have a function which performs update on one table and delete on another one;
To have two exactly same triggers and two different functions;

Before I will go ahead and implement solution 3 could you advice me if solution 1 or 2 are possible at all?

Best Answer

A trigger can only ever call one tigger function, so no to item 1.

The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want.

Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain in two separate functions. Just more code and overhead and two function invocations, which is more expensive.

2. is the undisputed victor.

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Postgresql – Scaling PostgreSQL TRIGGER(s)

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

That's a pretty standard use for a trigger.

Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation?

If you keep creating them, eventually you'll run out of disk space.

There's no specific limit for triggers.

PostgreSQL limits are documented on the about page.

Does they affect query performance?

It depends on the trigger type, trigger language, and what the trigger does.

A simple PL/PgSQL BEFORE ... FOR EACH STATEMENT trigger that doesn't do anything has near-zero overhead.

FOR EACH ROW triggers have higher overhead than FOR EACH STATEMENT triggers. Scaling, obviously, with the affected row counts.

AFTER triggers are more expensive than BEFORE triggers because they must be queued up until the statement finishes doing its work, then executed. They aren't spilled to disk if the queue gets big (at least in 9.4 and below, may change in future) so huge AFTER trigger queues can cause available memory to overrun, resulting in the statement aborting.

A trigger that modifies the NEW row before insert/update is cheaper than a trigger that does DML.

The specific use case you want would perform better with an in-progress enhancement that might make it into PostgreSQL 9.5 (if we're lucky), where FOR EACH STATEMENT triggers can see virtual OLD and NEW tables. This isn't possible in current PostgreSQL versions, so you must use FOR EACH ROW triggers instead.

Did anyone before tried this ?

Of course. It's a pretty standard use for triggers, along with auditing, sanity checking, etc.

You'll want to look into LISTEN and NOTIFY for a good way to wake up your worker when changes to the task table happen.

You're already doing the most important thing by avoiding talking to external systems directly from triggers. That tends to be problematic for performance and reliability. People often try to do things like send mail directly from a trigger, and that's bad news.

Best Answer

Related Solutions

MySQL using trigger instead of check

Postgresql – Scaling PostgreSQL TRIGGER(s)

Related Question