postgresql - 如何通过访问 postgres 中的数据来正确模拟语句级触发器

Question

我正在使用 PostgreSQL 作为工作项目的数据库。我们在很多地方使用触发器来维护计算列或本质上充当物化 View 的表。

当简单地使用行级触发器来保持所有这些同步时，所有这些都工作得很好。然而，当我们编写脚本定期将客户数据导入数据库时​​，我们遇到了性能问题或单个事务中的锁数问题。

为了缓解这种情况，我想创建一个语句级触发器来访问修改后的行(插入、更新或删除)。然而，由于这是不可能的，我改为创建一个 BEFORE 语句级触发器来创建一个临时表。然后是一个 AFTER 行级触发器，它将更改的数据插入到临时表中。最后一个 AFTER 语句级触发器将读取更改并执行必要的更新，然后删除临时表。

所有这一切都很好，假设在触发器中，没有人会再次重新触发相同的流程(因为临时表已经存在)。

但是我后来了解到，当使用外键约束与 ON DELETE SET NULL 时，它只是通过将列设置为 NULL 的系统触发器来实现的。这当然不是问题，除了当你在一个表上有几个像这样的外键约束时，所有外键约束都引用同一个表(我们就称它为 files)。当从 files 表中删除一行时，所有这些处理 ON DELETE SET NULL 子句的系统级触发器都会同时触发，即并行。这对我来说是一个严重的问题。

我将如何着手实现这样的事情？下面是一个简短的 SQL 脚本来说明问题:

CREATE TABLE files (
    id serial PRIMARY KEY,
    "name" TEXT NOT NULL
);

CREATE TABLE profiles (
    id serial PRIMARY KEY,
    NAME TEXT NOT NULL,
    cv_file_id INT REFERENCES files(id) ON DELETE SET NULL,
    photo_file_id INT REFERENCES files(id) ON DELETE SET NULL
);

CREATE TABLE profile_audit (
    profile_id INT NOT NULL,
    modified_at timestamptz NOT NULL
);

CREATE FUNCTION pre_stmt_create_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    CREATE TEMPORARY TABLE tmp_modified_profiles (
        id INT NOT NULL
    ) ON COMMIT DROP;
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE FUNCTION insert_modified_profile_to_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO tmp_modified_profiles(id) VALUES (NEW.id);
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE FUNCTION post_stmt_insert_rows_and_drop_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO profile_audit (id, modified_at)
    SELECT t.id, CURRENT_TIMESTAMP FROM tmp_modified_profiles t;

    DROP TABLE tmp_modified_profiles;

    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tr_create_working_table BEFORE UPDATE ON profiles FOR EACH STATEMENT EXECUTE PROCEDURE pre_stmt_create_temp_table();
CREATE TRIGGER tr_insert_row_to_working_table AFTER UPDATE ON profiles FOR EACH ROW EXECUTE PROCEDURE insert_modified_profile_to_temp_table();
CREATE TRIGGER tr_insert_modified_rows_and_drop_working_table AFTER UPDATE ON profiles FOR EACH STATEMENT EXECUTE PROCEDURE post_stmt_insert_rows_and_drop_temp_table();

INSERT INTO files ("name") VALUES ('photo.jpg'), ('my_cv.pdf');

INSERT INTO profiles ("name") VALUES ('John Doe');

DELETE FROM files WHERE "name" = 'photo.jpg';

Answer 1

这将是一个严重的 hack，但与此同时，在 PostgreSQL 9.5 发布之前，我会尝试使用 CONSTRAINT 触发器 deferred 到事务结束。我不确定这是否有效，但可能值得一试。