Force index to be updated

Question

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

score 1 · Answer 1 · answered Jan 25 '19 at 15:16

1

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.

answered Jan 25 '19 at 15:16

klin

112,967
15
204
232

But say I have no other choice but to do it this way (the ORM and other parts in play here are limiting my options), can I fully rebuild the index in a non-locking fashion? – svoop Jan 25 '19 at 17:54
No, your solution just corrupts the index, so the only way is to `reindex`. – klin Jan 25 '19 at 17:58

score 0 · Accepted Answer · answered Jan 25 '19 at 15:22

0

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

answered Jan 25 '19 at 15:22

Laurenz Albe

209,280
17
206
263

Due to the ORM and a few other parts in play here, I cannot use views. The immutable function is a workaround, I'm aware of the index to grow out of sync though. This is why I'd like to fully rebuild the index on a regular bases. – svoop Jan 25 '19 at 17:43
You can use a view quite like a table (there are `INSTEAD OF` triggers), so it would have to be a strange ORM if it couldn't be made to work. What you are envisioning is a road to hell, paved with incorrect query result. – Laurenz Albe Jan 25 '19 at 18:08
I've played around a little and I'm getting close to a solution with a view triggered with `INSTEAD OF` which doesn't break too much existing code. The view pulls all projects columns and joins the name columns from users required to build the index. However, will this index on the view be updated when any of the name columns on users is modified? – svoop Jan 26 '19 at 08:38
An index is updated whenever the table on which it is defined is updated. As long as you only use immutable functions and the index depends only on the data of the current table row (not on data in other tables or other rows of the same table), it will always remain consistent. – Laurenz Albe Jan 28 '19 at 07:01

Force index to be updated

2 Answers2