Автоматическое удаление функции при удалении таблицы в POSTGRESQL 11.7

Я каким-то образом пытаюсь вызвать автоматическое отключение функции, когда таблица удаляется, и я не могу понять, как это сделать.

TL; DR: есть ли способ вызвать сброс функции при удалении определенной таблицы? (POSTGRESQL 11.7)

Подробное объяснение

Я попытаюсь объяснить свою проблему, используя упрощенный вариант использования с фиктивными именами.

  1. У меня есть три таблицы: sensor1, sensor2 и sumSensors;

  2. Была создана ФУНКЦИЯ (sumdata) для ВСТАВКИ данных в таблицу sumSensors. Внутри этой функции я получу данные из таблиц sensor1 и sensor2 и вставлю их сумму в таблицу sumSensors;

  3. Для каждой таблицы датчиков был создан триггер, который выглядит следующим образом:

    СОЗДАТЬ ТРИГГЕР trig1 ПОСЛЕ ВСТАВКИ sensor1 ДЛЯ КАЖДОЙ СТРОКИ ВЫПОЛНИТЬ ФУНКЦИЮ sumdata ();

  4. Теперь, когда новая строка вставлена ​​ в таблицы sensor1 OR sensor2, функция sumdata будет выполнен, и вставит сумму последних значений из обоих в таблицу sumSensors

Если бы я хотел DROP FUNTION sumdata CASCADE;, триггеры автоматически удалялись бы из таблиц sensor1 и sensor2. До сих пор все нормально! Но я не этого хочу.

Моя проблема:

В: А если я просто DROP TABLE sumSensors CASCADE;? Что произойдет с функцией, которую нужно вставить в эту таблицу?

О: Как и ожидалось, поскольку нет связи между таблицей sumSensors и функцией sumdata, функция не будет удалена (все еще существует)! То же самое происходит с триггерами, которые его используют (все еще существуют). Это означает, что при вставке новой строки в таблицы датчиков функция sumdata будет выполнена и повреждена, что приведет к сбою (даже INSERT, который инициировал выполнение функции, на самом деле не будет вставлен).

Есть ли способ вызвать сброс функции при удалении определенной таблицы?

заранее спасибо


postgresql function triggers drop
person NelsonC    schedule 15.04.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

В PostgreSQL (начиная с версии 12) нет отслеживания зависимостей для функций.

Вы можете использовать триггеры событий, чтобы самостоятельно поддерживать зависимости.

Полный пример следует ниже.

Дополнительная информация: документация по функциям триггеров событий, функции поддержки.

BEGIN;

CREATE TABLE _testtable ( id serial primary key, payload text );

INSERT INTO _testtable (payload) VALUES ('Test data');

CREATE FUNCTION _testfunc(integer) RETURNS integer
LANGUAGE SQL AS $$ SELECT $1 + count(*)::integer FROM _testtable; $$;

SELECT _testfunc(100);

CREATE FUNCTION trg_drop_dependent_functions()
RETURNS event_trigger 
LANGUAGE plpgsql AS $$
DECLARE
    _dropped record;
BEGIN
    FOR _dropped IN
        SELECT schema_name, object_name
        FROM pg_catalog.pg_event_trigger_dropped_objects()
        WHERE object_type = 'table'
    LOOP
        IF _dropped.schema_name = 'public' AND _dropped.object_name = '_testtable' THEN
            EXECUTE 'DROP FUNCTION IF EXISTS _testfunc(integer)';
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_drop_dependent_functions ON sql_drop
EXECUTE FUNCTION trg_drop_dependent_functions();

DROP TABLE _testtable;

ROLLBACK;
person filiprem    schedule 16.04.2020
comment
Замечательно, это решило мою проблему. Честно, спасибо @filiprem! - person NelsonC; 16.04.2020