postgresql - 从 pg_stat_activity 查看时不启动触发器

Question

在 postgres 中不是真正在 pg_stat_activity 上创建触发器，因为我基于 pg_stat_activity 创建我的 View 并创建触发器。

DROP FUNCTION IF EXISTS get_sa() CASCADE;
DROP FUNCTION IF EXISTS f_call_count_conn();
DROP FUNCTION IF EXISTS f_update_count_conn();

CREATE OR REPLACE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;


CREATE OR REPLACE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa();

GRANT SELECT ON pg_stat_activity_allusers TO public;

CREATE OR REPLACE FUNCTION f_call_count_conn()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF TG_OP = 'INSERT' THEN
      COPY (SELECT time_change, count FROM count_conn) TO '/tmp/query.csv' (format csv, delimiter ';');
      RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
      COPY (SELECT time_change, count FROM count_conn) TO '/tmp/query.csv' (format csv, delimiter ';');
      RETURN OLD;
  END IF;

  -- PERFORM f_update_count_conn();
  -- RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


CREATE TRIGGER t_check_activity_conn
INSTEAD OF INSERT OR DELETE ON pg_stat_activity_allusers
FOR EACH ROW
EXECUTE PROCEDURE f_call_count_conn();


CREATE FUNCTION f_update_count_conn()
  RETURNS VOID
AS
$BODY$
BEGIN
  insert into count_conn (time_change, count)
    values (NOW(), (select count(*)
    from pg_stat_activity_allusers));
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

此代码无效，但是当我在真实表上替换我的 View (pg_stat_activity_allusers) 并更改此表时，我的触发器工作。为什么？谢谢!

Answer 1

你的代码在 Postgres 9.5 上对我有用，除了我手动调用 f_update_count_conn() 来填充 count_conn，因为你没有(或不再)在任何地方调用它。

# select f_update_count_conn();
 f_update_count_conn 
---------------------

(1 row)

mw=# select * from count_conn;
        time_change         | count 
----------------------------+-------
 2017-02-03 17:22:34.846179 |     1
(1 row)

mw=# insert into pg_stat_activity_allusers(datid) values(123456::oid);
INSERT 0 1
mw=# 
[1]+  Stopped                 '/Applications/Postgres.app/Contents/Versions/9.5/bin'/psql -p5432
$ cat /tmp/query.csv
2017-02-03 17:22:34.846179;1