Re: Different results from identical matviews - Mailing list pgsql-general
| From | Anders Steinlein |
|---|---|
| Subject | Re: Different results from identical matviews |
| Date | |
| Msg-id | CAC35HNmfNmJNC34tKhWsLMx5D6wTJiQiKTuXQYDEBA2ADhRMUg@mail.gmail.com Whole thread Raw |
| In response to | Re: Different results from identical matviews (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Different results from identical matviews
|
| List | pgsql-general |
On Thu, Jul 2, 2020 at 3:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Anders Steinlein <anders@e5r.no> writes:
> On Thu, Jul 2, 2020 at 2:02 AM Tom Lane <tgl@sss.pgh.pa.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)
?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.
pgsql-general by date: