postgresqldatabase-indexesfunctional-index

Force index to be updated

I'm handing ownerships as "Project -> Ownership -> User" relations and the following function gets the project owners' names as text:

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;

This is then used to build an index which ignores accents:

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

When the project is updated, this index is updated as well. However, when e.g. an owner name changes, this index won't be touched, right?

How can I force the index to be updated on a regular basis to catch up in that case?

(REINDEX is not an option since it's locking and will cause deadlocks should write actions happen at the same time.)

Solution

  • Since you lied to PostgreSQL by saying that the function was IMMUTABLE when it is actually STABLE, it is unsurprising that the index becomes corrupted when the database changes.

    The solution is not to create such an index.

    It would be better not to use that function, but a view that has the expression you want to search for as a column. Then a query that uses the view can be optimized, and an index on immutable_unaccent(btrim(concat_ws(' ', screen_name, first_name, last_name))) can be used.

    It is probably OK to cheat about unaccent's volatility...