PostgreSQL triggers in Rails

SQL triggers is something that everybody heard a lot about. Before start we need to talk about benefits and burdens.

###+

It’s very very fast. If you are using any kind of adapter you pay for all this wrappers and connectors. If it’s trigger – all operations are very cheap because they are provided by PostgtreSQL.
It works like a magick. There is no code on your repository, there is no any callbacks on your models and so on.

###-

It works like a magick. You can’t easy determine all operations. All this \df+ stuff and so on.
It’s hard to change. Really if you want change trigger you need to drop it and hang it again. With full trigger code. You can use hair_trigger to simplify it but anyway.
It increases requirements to your developers. Really, thay should now SQL. In perfect world your team is awesome. But it’s business.
It will cause some problems in cause if you are using STI because you can’t define model by it’s tablename.
It can cause problems on backups restoring. On restoring large tables somebody can use --disable-triggers to speed up process and it’s not that you are expecting.

If you are still want it let’s continue. You have MobilePhone, Laptop and Ad. Ad contains info about Product (in this case MobilePhone or Laptop). You want to provide some kind of faceted search for your products. We will use extension pg_trgm for searching products with name similar to provided by user.

What we need to implement it?

1) pg_trgm extension.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2) Table to store all this stuff

CREATE TABLE IF NOT EXISTS product_names (
  product_name  text    NOT NULL,
  product_type  text    NOT NULL,
  product_id    integer NOT NULL
);
CREATE INDEX product_name_gin_index ON product_names
  USING gin(product_name gin_trgm_ops);

3) Function that will process data. Based on this example. It’s pretty simple. We change our table curresponding to actions on product`s tables.

CREATE OR REPLACE FUNCTION collect_product_names() RETURNS TRIGGER AS $body$
BEGIN
  IF (TG_OP = 'INSERT') THEN
      INSERT INTO product_names (product_type, product_id, product_name)
      VALUES (TG_TABLE_NAME::TEXT, NEW.id, NEW.name);

      RETURN NULL;
  ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE product_names
         SET product_name = NEW.name
       WHERE  product_id = OLD.id
         AND product_type = TG_TABLE_NAME::TEXT;

      RETURN NULL;
  ELSIF (TG_OP = 'DELETE') THEN
      DELETE
      FROM   product_names
      WHERE  product_type = TG_TABLE_NAME::TEXT
        AND  product_id = OLD.id;

      RETURN NULL;
  ELSE
      RAISE WARNING '[AUDIT.collect_product_names] - Other action occurred: %, at %',TG_OP,now();
      RETURN NULL;
  END IF;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER;

4) Add our function for product`s table

DROP TRIGGER IF EXISTS collect_product_names ON mobile_phones;
DROP TRIGGER IF EXISTS collect_product_names ON laptops;

CREATE TRIGGER collect_product_names
  AFTER INSERT OR DELETE OR UPDATE ON mobile_phones
  FOR EACH ROW EXECUTE PROCEDURE collect_product_names();
CREATE TRIGGER collect_product_names
  AFTER INSERT OR DELETE OR UPDATE ON laptops
  FOR EACH ROW EXECUTE PROCEDURE collect_product_names();

5) Migrate existing data to our new table

[MobilePhone, Laptop].each do |klass|
  klass.pluck(:name, :id).each do |(name, id)|
    ProductName.create!(product_name: name,
      product_id: id, product_type: klass.table_name)
  end
end

All in one

class AddProductNamesTrigger < ActiveRecord::Migration
  def up
    execute %Q{
      CREATE EXTENSION IF NOT EXISTS pg_trgm;

      CREATE TABLE IF NOT EXISTS product_names (
          product_name  text    NOT NULL,
          product_type  text    NOT NULL,
          product_id    integer NOT NULL
      );
      CREATE INDEX product_name_gin_index ON product_names USING gin(product_name gin_trgm_ops);

      CREATE OR REPLACE FUNCTION collect_product_names() RETURNS TRIGGER AS $body$
        BEGIN
          IF (TG_OP = 'INSERT') THEN
              INSERT INTO product_names (product_type, product_id, product_name)
              VALUES (TG_TABLE_NAME::TEXT, NEW.id, NEW.name);

              RETURN NULL;
          ELSIF (TG_OP = 'UPDATE') THEN
              UPDATE product_names
                 SET product_name = NEW.name
               WHERE  product_id = OLD.id
                 AND product_type = TG_TABLE_NAME::TEXT;

              RETURN NULL;
          ELSIF (TG_OP = 'DELETE') THEN
              DELETE
              FROM   product_names
              WHERE  product_type = TG_TABLE_NAME::TEXT
                AND  product_id = OLD.id;

              RETURN NULL;
          ELSE
              RAISE WARNING '[AUDIT.collect_product_names] - Other action occurred: %, at %',TG_OP,now();
              RETURN NULL;
          END IF;

        END;
      $body$
      LANGUAGE plpgsql
      SECURITY DEFINER;

      DROP TRIGGER IF EXISTS collect_product_names ON mobile_phones;
      DROP TRIGGER IF EXISTS collect_product_names ON laptops;

      CREATE TRIGGER collect_product_names
        AFTER INSERT OR DELETE OR UPDATE ON mobile_phones
        FOR EACH ROW EXECUTE PROCEDURE collect_product_names();
      CREATE TRIGGER collect_product_names
        AFTER INSERT OR DELETE OR UPDATE ON laptops
        FOR EACH ROW EXECUTE PROCEDURE collect_product_names();
    }
    [MobilePhone, Laptop].each do |klass|
      klass.pluck(:name, :id).each do |(name, id)|
        ProductName.create(product_name: name, product_id: id, product_type: klass.table_name)
      end
    end
  end

  def down
    execute %Q{
      DROP TABLE IF EXISTS product_names;
      DROP TRIGGER IF EXISTS collect_product_names ON mobile_phones;
      DROP TRIGGER IF EXISTS collect_product_names ON laptops;
      DROP FUNCTION IF EXISTS collect_product_names();
    }
  end
end

Our databases prepared. Now we need to prepare rails app.

class ProductName < ActiveRecord::Base
  scope :by_name,    -> (pattern) { where("product_name % ?", pattern) }
  scope :statistics, -> { group(:product_type).count }
end

How to use it?

ProductName.by_name("Apple").statistics
#=> {"laptops"=>1, "mobile_phones"=>3}

P.S. Now you can see your function with \df+ on PostgreSQL terminal.