Не работает instead of триггер в представлении для партиционной таблицы

[DmitriyAr]

Добрый день!
Мне необходимо использовать триггеры для заполнения некоторых полей перед вставкой / обновлением партиционной таблицы. Обычные триггеры, например, типа BEFORE INSERT FOR EACH ROW, похоже, не работают (ERROR: partitioned column's value should not be NULL) .
Для решения этой проблемы, создаю, представление и вешаю на нее триггер INSTEAD OF INSERT OR UPDATE OR DELETE, в котором генерирую скрипт вставки / обновления / удаления записи из базовой таблицы.
Однако, если таблица партицирована pg_pathman триггер работает только для INSERT. Пример, см. ниже.
Создаю базовую таблицу:

CREATE TABLE public._items (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    code     BIGINT);

INSERT INTO public._items (id, name, code)
SELECT g, md5(g::text), random() * 100000
FROM generate_series(1, 100000) as g;

Создаю представление:
CREATE VIEW public.items as SELECT * FROM public._items;

Создаю триггер:

CREATE OR REPLACE FUNCTION public.test_items_tr ()
RETURNS trigger AS
$$
BEGIN
  RAISE EXCEPTION '%', TG_OP;
  RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER items_tr
  INSTEAD OF INSERT OR UPDATE OR DELETE 
  ON public.items FOR EACH ROW 
  EXECUTE PROCEDURE public.test_items_tr();

Пробую выполнить операции вставки / обновления / удаления:

INSERT INTO public.items values(1,  md5(1::text), random() * 100000); // ERROR:  INSERT
UPDATE public.items SET name = '' WHERE id = 1; // ERROR:  UPDATE
DELETE FROM public.items where id = 1;  // ERROR:  DELETE

пока все ОК, триггер работает для все операций.

Теперь создаю партиции:
SELECT public.create_range_partitions('public._items', 'id', 1, 100000, NULL ,true);

Пробую снова выполнить операции вставки / обновления / удаления:

INSERT INTO public.items values(1,  md5(1::text), random() * 100000); // ERROR:  INSERT
UPDATE public.items SET name = '' WHERE id = 1; // Query OK, 0 rows affected (execution time: 0 ms; total time: 0 ms)
DELETE FROM public.items where id = 1;  // Query OK, 0 rows affected (execution time: 0 ms; total time: 0 ms)

Триггер теперь срабатывает только для INSERT.

[funbringer]

Добрый день!

Обычные триггеры, например, типа BEFORE INSERT FOR EACH ROW, похоже, не работают (ERROR: partitioned column's value should not be NULL) .

Вы отчасти правы: триггеры родителя не сработают при вставке строк в партиции. Однако вы можете создать триггеры на партициях, и тогда они будут работать:

create function test_trig_func() returns trigger as $$
begin
    raise notice 'hi!';
    return new;
end;
$$ language plpgsql;

create trigger test_trig
before insert ON test_1 for each row
execute procedure test_trig_func();

explain (analyze, costs off) insert into test values(1, 0);
NOTICE:  hi!
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Insert on test (actual time=0.191..0.191 rows=0 loops=1)
   ->  Custom Scan (PartitionFilter) (actual time=0.055..0.056 rows=1 loops=1)
         ->  Result (actual time=0.001..0.001 rows=1 loops=1)
 Planning time: 0.310 ms
 Trigger test_trig on test_1: time=0.063 calls=1
 Execution time: 0.237 ms
(6 rows)

На данный момент сделать "проброс" тригера с родителя на партиции не представляется возможным. В некоторых случаях postgresql получает список триггеров партиции напрямую из каталога, что приводит к неприятным последствиям.

[DmitriyAr]

Спасибо, это я понимаю. Но мне нужно в триггере делать инициализацию первичного ключа значением из sequence, и по этому же полю должно выполняться range партицирование.
Эта задача у меня решается при обычном партицированием с помощью VIEW с INSTEAD OF INSERT OR UPDATE OR DELETE триггерами.
Вопрос в том, почему перестает нормально работать (работает только для INSERT - см. мой пост выше) триггер на VIEW если партицирование таблицы сделать с помощью pg_pathman?

Кстати, если отключить партицирование:
select public.disable_pathman_for('public._items');
то триггер на VIEW снова работает правильно, для всех операций.

[funbringer]

@DmitriyAr,

Я решил как следует разобраться с вашим примером, и кажется, нашел кое-что интересное. Проблем с UPDATE и DELETE действительно не должно быть. Возможно, это как-то связано с VIEW. Складывается ощущение, что в некоторых случаях дерево запроса обрабатывается не совсем правильно.

Я думаю, мы решим эту поблему :)

[DmitriyAr]

Отлично, буду ждать решения!
Спасибо.

[DmitriyAr]

Добрый день!

На текущий момент этот баг для нас становится критичным и блокирует дальнейшее использование pg_pathman в проекте.
Не могли бы Вы примерно сориентировать по возможному сроку решения проблемы?
Спасибо, за поддержку!

[funbringer]

to @DmitriyAr,

Прошу простить за задержку.

Скажите, вы используете ванильный postgres или дистрибутив PostgresPro? Мы столкнулись с трудностями при решении проблем с планированием запросов, и на первый взгляд стабильно устранить ее можно только в рамках нашего дистрибутива.

Исправление с ходу не заработало, на решение требуется не меньше недели.

[DmitriyAr]

Добрый день!

Мы используем стандартный postgres:
PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit
Возможности использовать PostgresPro, к сожалению, нет.

[maksm90]

@DmitriyAr, добрый день!

Мы, конечно, попытаемся разрешить проблему вызова instead of триггера с ванильным постгресом, однако эта задача будет у нас иметь низкий приоритет.

[DmitriyAr]

Добрый день!

Возможно, поможет, в решении проблемы. Найден workaround.
Eсли в запросе VIEW добавить сортировку, например:

CREATE VIEW public.items as SELECT * FROM public._items ORDER BY id;

Instead of триггeр будет работать правильно.

[funbringer]

Исправлено в 1.4.2.