What Is The Real PostgreSQL Triggers Ordering?

by Szymon Lipiński

During the last database training that I led, I was talking about selecting rows from database and sorting them. Including some problems with collations. Later I was talking about triggers. For this post I will assume that there are only row level triggers.

When you perform e.g. INSERT in the PostgreSQL database, there are fired triggers (if they exists, of course). At the very beginning there are fired all BEFORE INSERT triggers. Then INSERT is made, and later all triggers AFTER INSERT are fired.

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name.

trigger-definition

And then suddenly I was asked:

- OK, that’s clear, but which collation is used for ordering the triggers?

Good point, I really had no idea. So let’s check that.

First of all create the function which then will be used as a trigger:

    CREATE FUNCTION trigtest() RETURNS TRIGGER AS $$
    BEGIN
      RAISE NOTICE '%', TG_ARGV[0];
      RETURN NEW;
    END;
    $$ LANGUAGE PLPGSQL;

This function has to be used with an argument. It just prints the argument that was passed.

Now let’s create a bunch of triggers, with different names. In the PostgreSQL I can use any chars I want, even crazy ones, what usually is stupid, but I can.

So let’s check them:

    CREATE TABLE tab (i int);

    CREATE TRIGGER "ąą" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('ąą');
    CREATE TRIGGER "$$" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('$$');
    CREATE TRIGGER "[]" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('[]');
    CREATE TRIGGER "AA" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('AA');
    CREATE TRIGGER "aa" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('aa');
    CREATE TRIGGER "aA" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('aA');
    CREATE TRIGGER "Aa" BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('Aa');
    CREATE TRIGGER "b"  BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('b');
    CREATE TRIGGER "B"  BEFORE INSERT ON tab FOR EACH ROW EXECUTE PROCEDURE trigtest('B');

The best way to check the ordering of the triggers is to make a simple insert:

    # INSERT INTO tab(i) VALUES(0);
    NOTICE:  $$
    NOTICE:  AA
    NOTICE:  Aa
    NOTICE:  B
    NOTICE:  []
    NOTICE:  aA
    NOTICE:  aa
    NOTICE:  b
    NOTICE:  ąą
    INSERT 0 1

The collation used for ordering the triggers is the normal POSIX locale, as described here.

So beware when the definition says “triggers will be fired in alphabetical order by trigger name”. The order is the same all the time, the collation you use doesn’t really matter.