Re: Different results from identical matviews

Anders Steinlein <anders(at)e5r(dot)no> writes:
> On Thu, Jul 2, 2020 at 2:02 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I suspect the query underlying the matviews is less deterministic than
>> you think it is.

> Thanks for the tip, but I'm having a hard time thinking that's the case,
> seeing as I'm unable to trigger the wrong result no matter how hard I try
> with a new definition/manual query.

Well, another line of thought is that there actually is some difference
between the stored query for the original matview and the ones you enter
afresh. You said they were the same, but I surely didn't attempt to
verify that. Comparing pg_get_viewdef() output for equality would be
a good first step. Even that perhaps isn't conclusive, so you could
also try comparing the pg_rewrite.ev_action fields for the views'
ON SELECT rules. (That might be a bit frustrating because of likely
inconsistencies in node "location" fields; but any other difference
is cause for suspicion.)

regards, tom lane