Индекс силы будет обновлен

Question

Я передаю права собственности как отношения "Проект -> Владение -> Пользователь", и следующая функция получает имена владельцев проекта в виде текста:

CREATE FUNCTION owners_as_text(projects) RETURNS TEXT AS $$
  SELECT trim(both concat_ws(' ', screen_name, first_name, last_name)) FROM users
    INNER JOIN ownerships ON users.id = ownerships.user_id
    WHERE deleted_at IS NULL AND ownable_id = $1.id AND ownable_type = 'Project'
$$ LANGUAGE SQL IMMUTABLE SET search_path = public, pg_temp;

Затем он используется для построения индекса, который игнорирует акценты:

CREATE INDEX index_projects_on_owners_as_text
  ON projects
  USING GIN(immutable_unaccent(owners_as_text(projects)) gin_trgm_ops)

Когда проект обновляется, этот индекс также обновляется. Однако, когда, например, имя владельца меняется, этот индекс не будет затронут, верно?

Как я могу заставить индекс обновляться на регулярной основе, чтобы наверстать упущенное в этом случае?

(REINDEX это не вариант, так как он блокирует и вызывает взаимные блокировки, когда действия записи должны выполняться одновременно.)

Answer 1

Так как вы солгали PostgreSQL, сказав, что функция IMMUTABLE когда это на самом деле STABLEнеудивительно, что индекс изменяется при изменении базы данных.

Решение не в том, чтобы создать такой индекс.

Было бы лучше не использовать эту функцию, но представление, которое имеет выражение, которое вы хотите найти в виде столбца. Тогда запрос, использующий представление, может быть оптимизирован, а индекс immutable_unaccent(btrim(concat_ws(' ', screen_name, first_name, last_name))) может быть использован.

Вероятно, можно обманывать unaccentволатильность...

Answer 2

Идея ошибочно принята, потому что индекс построен на функции, которая на самом деле не является неизменной. Для документации:

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

Проблемы, с которыми вы сейчас сталкиваетесь, возникают из-за неверных предположений.