How to do a trigger that modifies a record in a partitioned Postgres table?

Question

I have a table with a trigger that updates a "modified" timestamp whenever the record changes. I did it with a BEFORE trigger:

CREATE OR REPLACE FUNCTION update_modified()
  RETURNS trigger AS
$$
BEGIN
  NEW.modified = now();
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER contact_update_modified
  BEFORE UPDATE
  ON contacts
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

Then I partitioned the table, and when I try to add the trigger, I get:

ERROR:  "contacts" is a partitioned table
DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
SQL state: 42809

If I change it to an AFTER trigger it doesn't update the modified field (which makes sense).

It appears that it does work if I add the trigger to each child partition table manually. I can do this, but it's not ideal. Is there a better way?

Nore

Answer 1

What kind of partitioning are you using? Declarative or using Inheritance?

If you are using Declarative you cannot use BEFORE INSERT/UPDATE ROW triggers. That is not supported as you can read in the documentation:

BEFORE ROW triggers, if necessary, must be defined on individual partitions, not the partitioned table.

You can create this trigger on the partitions themselves.

Mathre

Answer 2

It's can be do using a loop.

First if you using in migrations, need run this above query.

SELECT child.relname
  FROM pg_inherits
           JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
           JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
  WHERE parent.relname='base_table_name';

After take the result and run in each loop, like this. This exemple make in ruby on rails.

ActiveRecord::Base.connection.execute(sql).each do |partion_name|
  sql = <<-SQL
  CREATE TRIGGER trigger_name_#{partion_name['relname']}
    BEFORE UPDATE 
    ON #{partion_name['relname']}
    FOR EACH ROW
  EXECUTE PROCEDURE function_name();
  SQL
  ActiveRecord::Base.connection.execute(sql)
end

But you can run using each partition name. Exemple, your table name is contacts and partition names are: contacts_0, contacts_1, so just make like this.

CREATE TRIGGER contacts_0_update_modified
BEFORE UPDATE
ON contacts_0
FOR EACH ROW
EXECUTE PROCEDURE update_modified();

CREATE TRIGGER contacts_1_update_modified
BEFORE UPDATE
ON contacts_1
FOR EACH ROW
EXECUTE PROCEDURE update_modified();

Caves