Столбец кеша столбца в PostgreSQL

В моей базе данных есть таблицы tasks и comments. Каждая задача имеет много комментариев.

Я хотел бы создать столбец tasks.comments_count, который будет автоматически обновляться PostgreSQL, поэтому я могу получить comments_count (и отсортировать/отфильтровать по нему) в O (1) время при выборе всех задач.

Я знаю, что существуют речевые решения, такие как счетчик кэша ActiveRecord, но я не хочу их использовать (я нахожу их хрупкими). Я бы хотел, чтобы PostgreSQL позаботился о таких кэшах счетчиков.

Я также знаю, что PostgreSQL поддерживает триггеры, но их трудно писать и использовать (не твердое решение)

В идеале это будет расширение PostgreSQL или некоторая встроенная функция, о которой я не знаю.

Ленивый расчет таких счетчиков будет отличным бонусом.

Ответы

Ответ 1

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

Это довольно просто. Достаточно просто, что я сомневаюсь, что кто-то будет беспокоиться о создании расширения (не говоря уже о языковой функции), чтобы избавить вас от неприятностей. И это, конечно, проще (и, как вы указали, более безопасно), чем все, что происходит в ActiveRecord под капотом.

Что-то вроде этого, как правило, все, что требуется (я не тестировал это, так что вы можете это сделать...):

CREATE FUNCTION maintain_comment_count_trg() RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    UPDATE tasks SET comment_count = comment_count - 1 WHERE id = old.task_id;
  END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    UPDATE tasks SET comment_count = comment_count + 1 WHERE id = new.task_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER maintain_comment_count
AFTER INSERT OR UPDATE OF task_id OR DELETE ON comments
FOR EACH ROW
EXECUTE PROCEDURE maintain_comment_count_trg();

Если вы хотите, чтобы он был герметичным, вам понадобится дополнительный триггер для TRUNCATE на comments; стоит ли вам того, что вам нужно.

Чтобы обрабатывать обновления для значения tasks.id, на которое ссылаются (либо с помощью отложенных ограничений, либо с помощью действий ON UPDATE), то там немного больше, но это необычный случай.

И если ваша клиентская библиотека /ORM достаточно наивна для отправки через каждое поле в каждом выражении UPDATE, вам может понадобиться отдельный триггер UPDATE, который срабатывает только тогда, когда значение действительно изменилось:

CREATE TRIGGER maintain_comment_count_update
AFTER UPDATE OF task_id ON comments
FOR EACH ROW
WHEN (old.task_id IS DISTINCT FROM new.task_id)
EXECUTE PROCEDURE maintain_comment_count_trg();