Наличие нескольких триггерных событий при перенаправлении вставок в таблицы разделов

Я пытаюсь настроить триггеры для событий вставки и обновления для главной таблицы некоторых таблиц разделов в PostgreSQL. Каждый раз, когда вставка выполняется в основную таблицу, триггерное событие вставки перенаправляет ее в правильную таблицу разделов. Следовательно, мне нужно будет вернуть NULL из этого вызова функции, так как я не хочу, чтобы основная таблица также заполнялась. Если главная таблица получает событие обновления, она обновляет метку времени перед внесением изменений в таблицу. Проблема в том, что триггер обновления никогда не срабатывает. Я использую PostgreSQL версии 9.6.

Я попытался объединить триггерные функции в одну, а также объединил вызванные триггерные процедуры в одну, но результаты такие же. Триггер обновления срабатывает только в том случае, если я возвращаю NEW из функции триггера вставки (которая заполняет главную таблицу) или если я вообще закомментирую функцию триггера вставки.

DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
SET SCHEMA 'test';

CREATE TYPE test_type AS ENUM ('unit', 'performance');

CREATE TABLE test (
    type test_type NOT NULL,
    score INTEGER NOT NULL CHECK (score > 0),
    id SERIAL PRIMARY KEY,
    updated_at TIMESTAMP DEFAULT current_timestamp
);

CREATE TABLE performance_test (
    CHECK (type = 'performance')
) INHERITS (test);

CREATE FUNCTION insert_test()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO performance_test VALUES (NEW.*);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION update_timestamp()
RETURNS trigger AS
$$
BEGIN
    RAISE NOTICE 'This is never reached.';

    UPDATE performance_test
    SET updated_at = current_timestamp
    WHERE id = NEW.id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insertion BEFORE INSERT ON test
    FOR EACH ROW EXECUTE PROCEDURE insert_test();

CREATE TRIGGER test_update BEFORE UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

---------------------------------------------------------------------------

INSERT INTO test VALUES ('performance', 10);

SELECT * FROM performance_test;

UPDATE test SET score = 20 WHERE id = 1;

SELECT * FROM performance_test;

Я не уверен, что с помощью этого метода можно добиться того, чего я хочу, поэтому я обращаюсь сюда за любым советом. Заранее спасибо!

/ Хампус


postgresql postgresql-9.6 database-partitioning database-trigger
person Hampus Lidin    schedule 17.01.2019    source источник
comment
Если вам нужно разбиение на разделы, то почему бы не перейти на Postgres 11 и не воспользоваться значительно улучшенным удобством использования и производительностью разбиения? Там вам даже не нужен триггер   -  person a_horse_with_no_name    schedule 17.01.2019
comment
Да, я бы, если бы я не ограничивался версией, которую GCP использует для своего Cloud SQL, то есть 9.6.   -  person Hampus Lidin    schedule 17.01.2019

Ответы (1)


arrow_upward
0
arrow_downward

Триггеры строк должны быть определены для отдельных разделов, а не для многораздельной таблицы. См. https://www.postgresql.org/docs/10/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS

Я не знаю, почему в документации для 9.6 это не упоминается.

рабочий триггер обновления:

CREATE FUNCTION update_timestamp()
RETURNS trigger AS
$$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_update BEFORE UPDATE ON performance_test
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

если вы делаете UPDATE test SET score = 30, updated_at=DEFAULT; или UPDATE test SET score = 30, updated_at=current_timestamp;, вам может не понадобиться триггер обновления.

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

Если вы действительно уверены, что вам это нужно, вы должны убедиться, что вы понимаете это в деталях, в противном случае я бы рекомендовал вам избегать этого, большинство проблем с медленными запросами можно решить, убедившись, что статистика таблицы актуальна, используя правильный индексы, оптимизация запросов, изменение конфигурации Postgres или добавление дополнительного оборудования.

person Björn Nilsson    schedule 17.01.2019
comment
Спасибо за ваш ответ. Я не думал иметь триггер на разделе. Однако я думаю, что последую вашей рекомендации не использовать разделы, поскольку у нас будет много разделов (100+), и я не уверен, что разделы будут даже лучше для производительности в этом случае. - person Hampus Lidin; 18.01.2019
comment
Рад слышать, что это то, что вы можете изменить позже, если упрется в производительность. - person Björn Nilsson; 19.01.2019