Postgresql - Materialized views in postgres seems to be only partially updating or somehow caching old data

Question

so I'm working with a group that has a materialized view in postgres.
It depends on another view - lets call it A.
They've added a "refresh_time" - field which is just now() in the materialized view - so they can see when it's refreshed.

They maintain that when they run it from their machines, it works fine - and picks up the latest value from A. However, when they run it from the server process - it refreshes fine [they can see the refresh time has changed] - but it does not get the latest value from A.

I believe that's completely impossible [barring a bug in postgres, and I reckon that's unlikely]- and that somehow A must not be updated when it runs - but I'm asking here if I'm wrong - is there any other way that behaviour can occur? (eg permissions?)

question from:https://stackoverflow.com/questions/65911392/postgresql-materialized-views-in-postgres-seems-to-be-only-partially-updating

Answer 1

No, unless there is a bug in PostgreSQL, a materialized view will never contain values older than the time when it was refreshed.

However, you should consider concurrency and multiversioning:

If a materialized views is refreshed with REFRESH MATERIALIZED VIEW CONCURRENTLY, then the changes won't be visible until the transaction that executes the REFRESH statement is done. Now if two materialized views are refreshed at the same time, they won't see each other's changes. You would have to ascertain that the transaction refreshing A is committed by the time the dependent materialized view gets refreshed.