PostgreSQL：如果只更新了一个特定列，则不触发语句触发器

Question

我在PostgreSQL中的person表上有一个语句触发器，每当person表更新时刷新物化视图。其中一列是一个字节，用于存储从PHP上传的图像。该列不在物化视图中。person表上有一个行触发器，用于在图像列更新时更新图像上传日期列。如果行触发器触发，如何防止语句触发器触发?

create or replace function person_photo_date_func() returns trigger as $$
    begin
        new.photo_utc := current_timestamp;
        return new;
    end;
$$ language plpgsql;
create trigger person_photo_date_tg
    after update of photo on person
    for each row execute procedure person_photo_date_func();
create or replace function refresh_mv() returns trigger as $$
    begin
        refresh materialized view vm_csr;
        return null;
    end
$$ language plpgsql security definer;
create trigger refresh_mat_views_person
    after insert or update or delete or truncate
    on person for each statement
    execute procedure refresh_mv();

Answer 1

看这个，有人尝试了同样的方法，从存储过程中禁用触发器https://dba.stackexchange.com/questions/48402/disabling-triggers-in-stored-procedures

我会重新设计这个。你可以使用一些"状态"表，其中你的person_photo_date_func()将把"事务"由refresh_mv()跳过。

create table skip_refresh_mv (id serial primary key, xid integer);
create or replace function person_photo_date_func() returns trigger as $$
    begin
        new.photo_utc := current_timestamp;
        INSERT INTO skip_refresh_mv (xid) SELECT txid_current();
        return new;
    end;
$$ language plpgsql;
create or replace function refresh_mv() returns trigger as $$
    begin
      IF NOT EXISTS (SELECT 1 FROM skip_refresh_mv WHERE xid = txid_current()) THEN
        refresh materialized view vm_csr;
      END IF;        
      return null;
    end
$$ language plpgsql security definer;

但是它假设每个事务只有一条语句

您还需要清理skip_refresh_mv中的旧记录，因此向skip_refresh_mv表添加一些时间戳列，并对旧记录进行例行清理。

如果每个事务最多有一条这样的语句，那么您也可以在检查存在性之后从refresh_mv()内部清除记录。

注:如果您需要对每个语句执行此操作，我会使用pg_stat_activity。您可以使用以下命令获取当前事务活动查询:

select * from pg_stat_activity where state = 'active' and backend_xid = txid_current()::integer;