Let's try to mimic the following belongs_to relationship with counter_cache behavior in Postgres!

tldr - jump to the summary below!

class Comment < ActiveRecord::Base  
  belongs_to :post, counter_cache: true
end

class Post < ActiveRecord::Base  
end

First we need to create the relevant tables and columns.

CREATE TABLE posts (  
  id              serial PRIMARY KEY,
  title           text NOT NULL,
  body            text NOT NULL,
  comments_count  integer NOT NULL DEFAULT 0
);

CREATE TABLE comments (  
  id       serial PRIMARY KEY,
  post_id  integer NOT NULL REFERENCES posts(id),
  body     text NOT NULL
);

Then let's INSERT a sample post.

INSERT INTO posts (title, body) VALUES  
  ('My first post!', 'Sample content.');

Now we can SELECT * FROM posts to see what we're working with.

 id |     title      |      body       | comments_count 
----+----------------+-----------------+----------------
  1 | My first post! | Sample content. |              0
(1 row)

Since we don't have any comments yet, the comments_count is already correctly set to 0. We need to define a TRIGGER to increment or decrement the counter cache for a few different cases:

  • when a new comment is added with INSERT
  • when a comment is removed with DELETE
  • when a comment's post_id is changed with UPDATE
CREATE TRIGGER update_post_comments_count  
  AFTER DELETE OR INSERT OR UPDATE ON comments
  FOR EACH ROW EXECUTE PROCEDURE counter_cache('posts', 'comments_count', 'post_id');

Now that our trigger is in place we'll need to define the functions that it relies on. Let's start by creating a function called increment_counter to handle the actual increment and decrement updates.

CREATE FUNCTION increment_counter(table_name text, column_name text, id integer, step integer)  
  RETURNS VOID AS $$
    DECLARE
      table_name text := quote_ident(table_name);
      column_name text := quote_ident(column_name);
      conditions text := ' WHERE id = $1';
      updates text := column_name || '=' || column_name || '+' || step;
    BEGIN
      EXECUTE 'UPDATE ' || table_name || ' SET ' || updates || conditions
      USING id;
    END;
  $$ LANGUAGE plpgsql;

If we call increment_counter('posts', 'comments_count', 99, 1) then it executes the following SQL statement:

UPDATE posts SET comments_count = comments_count + 1 WHERE id = 99;

Now we just need to define the counter_cache function that ties our TRIGGER and increment_counter behavior together.

CREATE FUNCTION counter_cache()  
  RETURNS trigger AS $$
    DECLARE
      table_name text := quote_ident(TG_ARGV[0]);
      counter_name text := quote_ident(TG_ARGV[1]);
      fk_name text := quote_ident(TG_ARGV[2]);
      fk_changed boolean := false;
      fk_value integer;
      record record;
    BEGIN
      IF TG_OP = 'UPDATE' THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name || ' != ' || '($2).' || fk_name
        INTO fk_changed
        USING OLD, NEW;
      END IF;

      IF TG_OP = 'DELETE' OR fk_changed THEN
        record := OLD;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, -1);
      END IF;

      IF TG_OP = 'INSERT' OR fk_changed THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, 1);
      END IF;

      RETURN record;
    END;
  $$ LANGUAGE plpgsql;

There are some interesting things here to note:

  • We're using some special trigger variables!
  • TG_ARGV - this variable contains an array of the arguments that the trigger function was called with. Trigger functions do not support declaring named parameters!
  • TG_OP - contains the name of the operation that fired the trigger e.g. DELETE, INSERT, or UPDATE.
  • NEW and OLD - the NEW object refers to the newly inserted row. The OLD object refers to a deleted row. Since our trigger supports both INSERT and DELETE statements, we need to make sure that we're referring to the correct object.
  • Only triggers fired by UPDATE statements have both NEW and OLD objects present.
  • RETURN record - our trigger needs to return the record being inserted or deleted, either NEW or OLD in this case.

Let's INSERT some comments to see if the comments_count increments correctly for the post.

INSERT INTO comments (post_id, body) VALUES  
  (1, 'This is a comment!'),
  (1, 'And this is another comment!'),
  (1, 'One more comment!');

If we SELECT * FROM posts again we should see an updated comments_count.

 id |     title      |      body       | comments_count 
----+----------------+-----------------+----------------
  1 | My first post! | Sample content. |              3
(1 row)

Success! Now if we delete the first comment then it should decrement the post's comments_count as well!

DELETE FROM comments WHERE id = 1;

SELECT * FROM posts;

 id |     title      |      body       | comments_count 
----+----------------+-----------------+----------------
  1 | My first post! | Sample content. |              2
(1 row)

Let's try changing the a comment's post_id to make sure it handles that case correctly. First we'll need to add a second post.

INSERT INTO posts (title, body) VALUES  
  ('Another post!!', 'Sample content.');

Then let's SELECT * FROM posts and SELECT * FROM comments to review our data.

 id |     title      |      body       | comments_count 
----+----------------+-----------------+----------------
  1 | My first post! | Sample content. |              2
  2 | Another post!! | Sample content. |              0
(2 rows)

 id | post_id |   body    
----+---------+-----------
  2 |       1 | Comment 2
  3 |       1 | Comment 3
(2 rows)

If we UPDATE comments SET post_id = 2 WHERE id = 2 then we should see comments_count change for both posts!

SELECT * FROM posts;

 id |     title      |      body       | comments_count 
----+----------------+-----------------+----------------
  1 | My first post! | Sample content. |              1
  2 | Another post!! | Sample content. |              1
(2 rows)

SELECT * FROM comments;

 id | post_id |   body    
----+---------+-----------
  2 |       2 | Comment 2
  3 |       1 | Comment 3
(2 rows)

Awesome! It's pretty cool that we can define "macro" like triggers that are reusable across tables!

tldr

Here's a summary of what we ended up with:

CREATE TABLE posts (  
  id              serial PRIMARY KEY,
  title           text NOT NULL,
  body            text NOT NULL,
  comments_count  integer NOT NULL DEFAULT 0
);

CREATE TABLE comments (  
  id       serial PRIMARY KEY,
  post_id  integer NOT NULL REFERENCES posts(id),
  body     text NOT NULL
);

CREATE FUNCTION increment_counter(table_name text, column_name text, id integer, step integer)  
  RETURNS VOID AS $$
    DECLARE
      table_name text := quote_ident(table_name);
      column_name text := quote_ident(column_name);
      conditions text := ' WHERE id = $1';
      updates text := column_name || '=' || column_name || '+' || step;
    BEGIN
      EXECUTE 'UPDATE ' || table_name || ' SET ' || updates || conditions
      USING id;
    END;
  $$ LANGUAGE plpgsql;

CREATE FUNCTION counter_cache()  
  RETURNS trigger AS $$
    DECLARE
      table_name text := quote_ident(TG_ARGV[0]);
      counter_name text := quote_ident(TG_ARGV[1]);
      fk_name text := quote_ident(TG_ARGV[2]);
      fk_changed boolean := false;
      fk_value integer;
      record record;
    BEGIN
      IF TG_OP = 'UPDATE' THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name || ' != ' || '($2).' || fk_name
        INTO fk_changed
        USING OLD, NEW;
      END IF;

      IF TG_OP = 'DELETE' OR fk_changed THEN
        record := OLD;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, -1);
      END IF;

      IF TG_OP = 'INSERT' OR fk_changed THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, 1);
      END IF;

      RETURN record;
    END;
  $$ LANGUAGE plpgsql;

CREATE TRIGGER update_post_comments_count  
  AFTER INSERT OR UPDATE OR DELETE ON comments
  FOR EACH ROW EXECUTE PROCEDURE counter_cache('posts', 'comments_count', 'post_id');

If you enjoyed this, check out how to port ActiveRecord validations and soft delete behavior to Postgres as well!