时间戳

首页 > 解决方案 > postgres中级联删除的顺序

sql - postgres中级联删除的顺序

问题描述

我遇到了一个问题，这让我怀疑 Postgres 仅在删除原始行之后才从依赖表中删除行（ON DELETE CASCADE）。

我有这些表：

CREATE TABLE IF NOT EXISTS function (
    id UUID PRIMARY KEY,
    level VARCHAR(4) NOT NULL CHECK (level IN ('ORG', 'DEP', 'GRP', 'SESS')),
    name VARCHAR(64) NOT NULL UNIQUE,
    type VARCHAR(15) NOT NULL CHECK (type IN ('SYSTEM', 'SYS-AUTO-ASSIGN', 'CUSTOM'))
);

CREATE TABLE IF NOT EXISTS function_inclusion (
    super_function UUID REFERENCES function (id) ON DELETE CASCADE,
    sub_function UUID REFERENCES function (id) ON DELETE CASCADE,
    UNIQUE (super_function, sub_function)
);

我在 function_inclusion 表上创建了一个触发器（在删除之前）：

CREATE OR REPLACE FUNCTION trg_function_inclusion_del_bef()
RETURNS trigger AS
$func$
DECLARE
    function_type VARCHAR(15);
BEGIN
    SELECT type INTO function_type FROM function WHERE id = OLD.super_function;
    RAISE NOTICE 'function_type: %', function_type;
    -- do stuff based on the function_type of the super_function
    CASE
        WHEN function_type = 'SYSTEM' OR function_type = 'SYS-AUTO-ASSIGN' THEN
            -- (do stuff)
        WHEN function_type = 'CUSTOM' THEN
            -- (do stuff)
        ELSE RAISE EXCEPTION 'The function % doesn''t have a correct type', OLD.super_function;
    END CASE;
    RETURN OLD;
END
$func$  
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS function_inclusion_delete_before ON function_inclusion CASCADE;

CREATE TRIGGER function_inclusion_delete_before
BEFORE DELETE ON function_inclusion
FOR EACH ROW 
EXECUTE PROCEDURE trg_function_inclusion_del_bef();

假设我有 2 个函数和一个 function_inclusion：

INSERT INTO function (id, level, name, type)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', 'DEP', 'custom-function-1', 'CUSTOM');

INSERT INTO function (id, level, name, type)
VALUES ('360bde13-7953-49ed-a923-793b2d828d7e', 'DEP', 'custom-function-2', 'CUSTOM');

INSERT INTO function_inclusion (super_function, sub_function)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', '360bde13-7953-49ed-a923-793b2d828d7e');

当我删除 super_function 时：

DELETE FROM function WHERE id = 'abcf3dbc-9433-4b73-b9c1-f00745dc1175';

我收到此错误：

NOTICE:  function_type: <NULL>
Query 1 ERROR: ERROR:  The function abcf3dbc-9433-4b73-b9c1-f00745dc1175 doesn't have a correct type
CONTEXT:  PL/pgSQL function trg_function_inclusion_del_bef() line 13 at RAISE
SQL statement "DELETE FROM ONLY "public"."function_inclusion" WHERE $1 OPERATOR(pg_catalog.=) "super_function""

因此，该功能似乎已被删除，我无法再通过 function_inclusion 上的触发器访问它。

我试图找到有关“ON DELETE CASCADE”的更多信息，但在我读到的所有地方都只说“引用行被自动删除”，没有提到首先删除的是引用行还是被引用行。

postgres 在删除相关（引用）表中的行之前是否首先删除原始（引用）行？如果是这样，我怎样才能实现同样的事情，而不必在我的 function_inclusion 表中存储冗余数据？

标签： sqlpostgresqltriggersplpgsqlcascade

解决方案

以防万一其他人遇到同样的问题，我发布了我找到的解决方案。

问题是 - 正如我所怀疑的那样 - postgres 在从依赖表中删除行之前首先删除请求的行本身（这些表设置了“ON DELETE CASCADE”）。

我找到的解决方案有点复杂，在“功能”表上实现软删除（请参阅级联软删除）

  1. 首先，我在“函数”表中添加了一个额外的字段 deleted_at：
CREATE TABLE IF NOT EXISTS function (
    id UUID PRIMARY KEY,
    level VARCHAR(4) NOT NULL CHECK (level IN ('ORG', 'DEP', 'GRP', 'SESS')),
    name VARCHAR(64) NOT NULL UNIQUE,
    type VARCHAR(15) NOT NULL CHECK (type IN ('SYSTEM', 'SYS-AUTO-ASSIGN', 'CUSTOM')),
    deleted_at TIMESTAMPTZ DEFAULT NULL
);

该表包含所有“功能”行，包括已经（软）删除的行。从现在开始，我们将需要使用“SELECT FROM ONLY 函数”来选择未删除的行。

  1. 然后我创建了一个继承表“function_deleted”：
CREATE TABLE IF NOT EXISTS function_deleted () INHERITS(function);

插入此表的行也将在表“函数”中找到。要查找已删除的行，我们需要使用“SELECT FROM function_deleted”。

  1. 然后我创建了一个通用触发器函数，用于从任何表中软删除一行：
CREATE OR REPLACE FUNCTION tr_soft_delete_row()
RETURNS TRIGGER AS 
$$
BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL) THEN
        EXECUTE format('DELETE FROM %I.%I WHERE id = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.id;
        RETURN OLD;
    END IF;
    IF (TG_OP = 'DELETE') THEN
        IF (OLD.deleted_at IS NULL) THEN
            OLD.deleted_at := timenow();
        END IF;
        EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_deleted') USING OLD;
    END IF;
    RETURN OLD;
END;
$$ 
LANGUAGE plpgsql;

当在删除行时从触发器调用时，该函数设置 deleted_at 字段并将该行插入“(table)_deleted”表中。

当在 delete_at 字段更新时从触发器调用时，此函数删除行（将自动变为软删除）

  1. 然后我创建了一个触发器来调用这个软删除函数：
CREATE TRIGGER _soft_delete_function
AFTER
    UPDATE OF deleted_at 
    OR DELETE
    ON function
FOR EACH ROW
EXECUTE PROCEDURE tr_soft_delete_row();

（触发器名称前面的下划线确保触发器将在任何其他触发器之前被调用）

  1. 现在我像以前一样创建“function_inclusion”表：
CREATE TABLE IF NOT EXISTS function_inclusion (
    super_function UUID REFERENCES function (id) ON DELETE CASCADE,
    sub_function UUID REFERENCES function (id) ON DELETE CASCADE,
    UNIQUE (super_function, sub_function)
);
  1. 以及该表的触发函数和触发器：
CREATE OR REPLACE FUNCTION trg_function_inclusion_del_aft()
RETURNS trigger AS
$func$
DECLARE
    function_type VARCHAR(15);
BEGIN
    SELECT type INTO function_type FROM ONLY function WHERE id = OLD.super_function;
    -- if the function doesn't exist then it's because it was just deleted
    --> find it in table "function_deleted"
    IF NOT FOUND THEN
        SELECT type INTO function_type FROM function_deleted WHERE id = OLD.super_function 
        ORDER BY deleted_at DESC NULLS LAST LIMIT 1;
    END IF;
    RAISE NOTICE 'function_type: %', function_type;
    -- do stuff based on the function_type of the super_function
    CASE
        WHEN function_type = 'SYSTEM' OR function_type = 'SYS-AUTO-ASSIGN' THEN
            -- (do stuff)
        WHEN function_type = 'CUSTOM' THEN
            -- (do stuff)
        ELSE RAISE EXCEPTION 'The function % doesn''t have a correct type', OLD.super_function;
    END CASE;
    RETURN OLD;
END
$func$  
LANGUAGE plpgsql;

CREATE TRIGGER function_inclusion_delete_after
AFTER DELETE ON function_inclusion
FOR EACH ROW 
EXECUTE PROCEDURE trg_function_inclusion_del_aft();
  1. 现在，当我创建函数和函数包含时：
INSERT INTO function (id, level, name, type)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', 'DEP', 'custom-function-1', 'CUSTOM');

INSERT INTO function (id, level, name, type)
VALUES ('360bde13-7953-49ed-a923-793b2d828d7e', 'DEP', 'custom-function-2', 'CUSTOM');

INSERT INTO function_inclusion (super_function, sub_function)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', '360bde13-7953-49ed-a923-793b2d828d7e');

然后删除其中一项功能：

DELETE FROM ONLY function WHERE id = 'abcf3dbc-9433-4b73-b9c1-f00745dc1175';

我现在没有收到错误，并且在引发的通知中得到了正确的信息（来自 function_inclusion 上的 AFTER DELETE 触发器）：

NOTICE:  function_type: CUSTOM
Query 1 OK: DELETE 1, 1 row affected

这种方法的一个缺点是，现在在我的 SQL 代码中，我需要记住使用“SELECT * FROM ONLY 函数”而不是“SELECT * FROM 函数”。

推荐阅读