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

Question

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

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

为了缓解这种情况，我想创建一个语句级触发器，可以访问修改后的行（插入、更新或删除）。但是，由于这是不可能的，我创建了一个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

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

Answer 2

您可以使用状态列来跟踪语句级触发器的插入和更新。

在BEFORE INSERT OR UPDATE行级触发器中：

    SET NEW.status = TG_OP;

现在，您可以使用语句级AFTER触发器：

    BEGIN
        DO FUNNY THINGS
         WHERE status = 'INSERT';
        -- reset the status
        UPDATE mytable
           SET status =  NULL
         WHERE status = 'INSERT';
    END;

但是，如果您还想处理删除，则需要在行级触发器中使用类似以下内容：

    INSERT INTO status_table (table_name, op, id) VALUES (TG_TABLE_NAME, TG_OP, OLD.id);

然后，在语句级AFTER触发器中，您可以执行以下操作：

    BEGIN
        DO FUNNY THINGS
         WHERE id IN (SELECT id FROM status_table 
                       WHERE table_name = TG_TABLE_NAME AND op = TG_OP); -- just an example
        -- reset the status
        DELETE FROM status_table
         WHERE table_name = TG_TABLE_NAME AND op = TG_OP;
    END;