Callbacks in the database (Postgres)

4 min readFeb 11, 2016

WARNING : This is more about Postgres than Ecto.

In a previous article I wrote about how you could use prepare_changes to implement a Railsy counter_cache behavior into an Ecto Model/Schema.

Maximiliano Guzenski pointed out that he would rather put this logic into the database, which makes sense. Let’s see how to achieve this using Postgres functions and triggers.

SECOND WARNING : I’m neither a db admin nor a full-time developer. Feel free to correct me when I’m wrong.

Functions and triggers

In Postgres you can add callbacks to table operations (INSERT / UPDATE / DELETE / TRUNCATE).

It basically goes like this :

You create a function / procedure that tells WHAT has to be done (updating a column in another table, logging something, changing a field before a row is inserted, you name it).
You create a trigger that tells WHEN the function is called (BEFORE INSERT, AFTER UPDATE, etc).

Note : according to this stack overflow answer, triggers are run within the same transaction that the statement that triggered them.

The pitch

I have a table posts and a table comments. A Comment has a post_id and I want to add a column comments_count to Post that will be automatically updated whenever a Comment is created or deleted.

The function

First let’s write the function that will increment or decrement the comments_count column.

CREATE OR REPLACE FUNCTION update_comments_count()
RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    UPDATE posts SET comments_count = comments_count + 1
      WHERE id = NEW.post_id;
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    UPDATE posts SET comments_count = comments_count - 1
      WHERE id = NEW.post_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

When this function is called by a trigger, some variables are set. TG_OP, for instance, holds the operation type.

In an INSERT operation, NEW holds the row that was (or will be) created.

In a DELETE operation, OLD holds the row that was (or will be) deleted.

At the end of each if-branch, we return the row. I don’t know if it is necessary with AFTER triggers but maybe if you have multiple triggers the return value of one trigger will be fed into the next.

The last line says that the function is written in PL/pgSQL. You can actually write this logic in other languages (Tcl, Perl, Python). See here for more information.

The trigger

Second let’s write the trigger that will execute our function.

DROP TRIGGER IF EXISTS update_comments_count_trg ON comments;CREATE TRIGGER update_comments_count_trg
AFTER INSERT OR DELETE
ON comments
FOR EACH ROW
EXECUTE PROCEDURE update_comments_count();

The _trg suffix is a convention, you can actually call your trigger anything you want.

AFTER INSERT OR DELETE is self-explanatory. It says that the function should be triggered after a succesful insertion or a succesful deletion.

ON comments is needed because, unlike functions which are declared db wide, triggers (like indices) are declared on tables.

FOR EACH ROW specifies that the function should be triggered for each row modified by the operation. You can write triggers that will call the function for the full operation (statement). See the trigger documentation.

EXECUTE PROCEDURE update_comments_count() is also self-explanatory. It tells which function to call (possibly passing arguments to it).

The Ecto part

In order to not make this post completely unrelated to Ecto, let’s see how to write the migration for this.

First create the migration :

mix ecto.gen.migration add_comments_count_to_posts

Then edit it :

defmodule Posts.Repo.Migrations.AddCommentsCountToPosts do
  use Ecto.Migration  def up do
    alter table(:posts) do
      add :comments_count, :integer, null: false, default: 0
    end    execute """
    CREATE OR REPLACE FUNCTION update_comments_count()
    RETURNS trigger AS $$
    BEGIN
      IF (TG_OP = 'INSERT') THEN
        UPDATE posts SET comments_count = comments_count + 1
          WHERE id = NEW.post_id;
        RETURN NEW;
      ELSIF (TG_OP = 'DELETE') THEN
        UPDATE posts SET comments_count = comments_count - 1
          WHERE id = OLD.post_id;
        RETURN OLD;
      END IF;
      RETURN NULL;
    END;
    $$ LANGUAGE plpgsql;
    """    execute "DROP TRIGGER IF EXISTS update_comments_count_trg ON comments;"
    execute """
    CREATE TRIGGER update_comments_count_trg
    AFTER INSERT OR DELETE
    ON comments
    FOR EACH ROW
    EXECUTE PROCEDURE update_comments_count();
    """
  end  def down do
    alter table(:posts) do
      remove :comments_count
    end    execute "DROP FUNCTION update_comments_count() CASCADE;"
  end
end

Ecto.Migration.execute/1 allows to execute SQL statement within the migration. I use heredoc notation to make the statement more readable.

You have to use as many execute calls as statements you want to run. You cannot drop a trigger and create it within the same execute call.

Let’s migrate :

mix ecto.migrate

Done !

Some considerations

Let’s talk about the pros and cons of this approach.

Pros

Speed : the database handles this problem itself, you save roundtrips between you app server and your db server.
Consistency : should the database be modified by other applications, you don’t have to rewrite this behavior in all your apps.

Cons

Portability : this solution is Postgres specific. Other DBMS might have triggers but the snippets I wrote will only work with Postgres.
It’s SQL : this one is very subjective. I now that using ORMs is not a reason for not knowing SQL, but I prefer writing Elixir code way more than writing SQL code.

Conclusion

Triggers are powerful mechanisms that you can use to ensure data consistency. Have fun using them within (or outside) of your Ecto applications !