postgres中级联删除的sql顺序

dfty9e19  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(500)

我遇到了一个问题,这让我怀疑postgres只有在删除了原始行之后才从依赖表中删除行(在delete cascade上)。
我有这些table:

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)
);

我在函数包含表上创建了一个触发器(在删除之前):

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();

假设我有两个函数和一个包含函数:

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\u函数时:

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""

所以看起来函数已经被删除了,我不能再从函数包含的触发器访问它了。
我试图找到更多关于“on delete cascade”的信息,但是我读到的每一个地方都只说“引用行被自动删除”,没有提到先删除哪个,引用行还是被引用行。
postgres在删除依赖(引用)表中的行之前是否先删除原始(引用)行?如果是这样的话,我如何实现同样的事情而不必在我的函数包含表中存储冗余数据?

eagi6jfj

eagi6jfj1#

以防其他人遇到同样的问题,我张贴的解决办法,我发现。
问题是,正如我所怀疑的那样,postgres首先删除请求的行本身,然后再从依赖表(设置了“on delete cascade”)中删除行。
我发现的解决方案有点复杂,在“function”表上实现软删除(参见级联软删除)
首先,我在“function”表中添加了一个额外的字段:

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 function”来选择未删除的行。
然后我创建了一个继承表“function_deleted”:

CREATE TABLE IF NOT EXISTS function_deleted () INHERITS(function);

插入到此表中的行也将在表“function”中找到。要查找已删除的行,我们需要使用“select from function\u deleted”。
然后我创建了一个通用触发器函数,用于从任何表中软删除行:

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\u at字段并将行插入“(table)\u deleted”表。
当在更新已删除的\u at字段时从触发器调用时,此函数将删除行(这将自动成为软删除)
然后我创建了一个触发器来调用这个软删除函数:

CREATE TRIGGER _soft_delete_function
AFTER
    UPDATE OF deleted_at 
    OR DELETE
    ON function
FOR EACH ROW
EXECUTE PROCEDURE tr_soft_delete_row();

(触发器名称前的下划线确保在调用任何其他触发器之前调用触发器)
现在我创建“function\u 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)
);

以及此表的触发器函数和触发器:

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();

现在当我创建函数和函数包含时:

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';

我现在没有得到一个错误,并且我在引发的通知中得到了正确的信息(从函数\u inclusion上的after delete触发器):

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

这种方法的一个缺点是,现在在我的sql代码中的任何地方,我都需要记住使用“selectfrom only function”而不是“selectfrom function”。

相关问题