如何创建一个触发器来修改分区Postgres表中的记录？

Question

我有一个表格，并使用触发器在记录更改时更新“修改时间戳”。我是通过 BEFORE 触发器来实现的:

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();

然后我对表进行了分区，当我尝试添加触发器时，出现以下提示：

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

如果我将它更改为触发器(AFTER)，它不会更新修改字段(这是有道理的)。

如果我手动将触发器添加到每个子分区表中，它似乎可以工作。我可以这样做，但这并不理想。有更好的方法吗？

- ccleve

Answer 1

你正在使用哪种分区方式？是声明式的还是使用继承的？

如果你正在使用声明式的方式，那么你就不能使用BEFORE INSERT/UPDATE ROW触发器。正如你在文档中所读到的那样：

“如有必要，BEFORE ROW触发器必须在各个分区上定义，而不是在分区表上定义。”

你可以在分区本身上创建这个触发器。

- Natashz

Answer 2

可以使用循环来实现。

如果您在迁移中使用，则需要运行上述查询。

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';

在每个循环中运行结果，就像这样。这个例子是用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

但是您可以使用每个分区的名称来运行。例如，您的表名为contacts，分区名称为：contacts_0，contacts_1，只需像这样操作。

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();

- Rafael Ferreira Alves