On Thu, Jul 2, 2020 at 3:01 PM Jeremy Smith <jeremy@xxxxxxxxxxxxxx> wrote:
It looks like you are using now() fairly often in that query. That would, of course, give different results in different transactions, but it could also give different results if a) the things you are comparing now() to are timestamp without time zone and b) the session time zone of the user doing the refresh is different from the session time zone of the user running the query. I'd also be suspicious about any other timestamp comparisons that aren't comparing timestamp to timestamp or timestamptz to timestamptz.
Thanks, but as mentioned in another part of the thread this data is stale (unmodified during these days we're looking into this). And the result is different between the erroneous matview and identical manual query regardless, whether I run them in the same transaction or not. And the results were the same yesterday, same days before that when the issue came up, and same today. So I can't see how times are the issue, unfortunately. :-/
Best,
-- a.
