Re: Different results from identical matviews

On Thu, Jul 2, 2020 at 3:44 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

I used a manual `diff` earlier, but this sure was easier. But yes, the
stored queries are identical:

mm_prod=> select pg_get_viewdef('aakpnews.segments_with_contacts') =
pg_get_viewdef('aakpnews.segments_with_contacts_2');
?column?
----------
t
(1 row)

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

You're right, ev_action is indeed different:

mm_prod=> select x1.ev_type = x2.ev_type as ev_type_equal, x1.ev_enabled =
x2.ev_enabled as enabled_equal, x1.is_instead = x2.is_instead as
is_instead_equal, x1.ev_qual = x2.ev_qual as ev_qual_equal, x1.ev_action =
x2.ev_action as ev_action_equal
from
(select pr.* from pg_namespace pn inner join pg_class pc on pc.relnamespace
= pn.oid inner join pg_rewrite pr on pr.ev_class = pc.oid where pn.nspname
= 'aakpnews' and pc.relname = 'segments_with_contacts') x1,
(select pr.* from pg_namespace pn inner join pg_class pc on pc.relnamespace
= pn.oid inner join pg_rewrite pr on pr.ev_class = pc.oid where pn.nspname
= 'aakpnews' and pc.relname = 'segments_with_contacts_2') x2;
ev_type_equal | enabled_equal | is_instead_equal | ev_qual_equal |
ev_action_equal
---------------+---------------+------------------+---------------+-----------------
t | t | t | t | f
(1 row)

Is there somehow I can format them to make it easier to compare? My basic
attempts didn't help me much. I put them up in all their glories in
pastebins, since they are rather large. Please let me know if there is
somehow I can make this easier to look into.

ev_action for segments_with_contacts - the origial matview:
https://pastebin.com/MBJ45prC
ev_action for segments_with_contacts_2 - the similar newly created matview:
https://pastebin.com/sL4WjzBj

Best,
-- a.