CODEWITHSUNDEEP
postgresqldatabase-indexesfunctional-index
svoop
svoop

Reputation: 3454

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.)

Upvotes: 0

Views: 139

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246383

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...

Upvotes: 0

klin
klin

Reputation: 121594

The idea is erroneously assumed because the index is built on a function that in fact is not immutable. For the documentation:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list.

The problems you are now facing arise from incorrect assumptions.

Upvotes: 1

Related Questions