Re: Different results from identical matviews

Anders Steinlein <anders@e5r.no> writes:
>> 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:
> ...
> 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.

Yeah, expression trees are pretty unreadable :-(.  I downloaded these,
changed all the "location" fields to -1 to make them more comparable,
and behold there are still a bunch of diffs.  Here's one:

original view:

{JOINEXPR :jointype 0 :isNatural false :larg
{RANGETBLREF :rtindex 1}
 :rarg
{RANGETBLREF :rtindex 2}
 :usingClause ("email") :quals
{OPEXPR :opno 98 :opfuncid 67 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args (
{RELABELTYPE :arg
{VAR :varno 1 :varattno 2 :vartype 106893 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 2
:location-1} 
 :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}
{RELABELTYPE :arg
{VAR :varno 2 :varattno 2 :vartype 106893 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 2 :varoattno 2
:location-1} 
 :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}
) :location -1}
 :alias <> :rtindex 3}

new view:

{JOINEXPR :jointype 0 :isNatural false :larg
{RANGETBLREF :rtindex 1}
 :rarg
{RANGETBLREF :rtindex 2}
 :usingClause ("email") :quals
{OPEXPR :opno 106108 :opfuncid 106101 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args (
{RELABELTYPE :arg
{VAR :varno 1 :varattno 2 :vartype 106893 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 2
:location-1} 
 :resulttype 106084 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}
{RELABELTYPE :arg
{VAR :varno 2 :varattno 2 :vartype 106893 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 2 :varoattno 2
:location-1} 
 :resulttype 106084 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}
) :location -1}
 :alias <> :rtindex 3}

This is the internal form of a "JOIN ... USING (email)" construct.
I didn't try to trace this back to exactly where it was in the source
queries.  The important thing here is that we have a couple of Vars
of type 106893, which I gather must be citext or a domain over it.
In the first tree, those are coerced via a no-op RelabelType operation
into plain text (type OID 25) and then compared with the built-in texteq
operator.  In the second tree, they are coerced to some other non-built-in
type (maybe plain citext?) and then compared with operator 106108.

I am betting that 106084 is citext, 106108 is citext's equality operator,
and the net implication of all this is that the original matview is doing
the JOIN using case-sensitive equality whereas the new one is using
case-insensitive equality.

A plausible explanation for how things got that way is that citext's
equality operator wasn't in your search_path when you created the original
matview, but it is in view when you make the new one, allowing that
equality operator to capture the interpretation of USING.  Unfortunately,
since the reverse-listing of this join is just going to say "USING
(email)", there's no way to detect from human-readable output that the
interpretation of the USING clauses is different.  (We've contemplated
introducing not-SQL-standard syntax to allow flagging such cases, but
haven't pulled the trigger on that.)

I count five places in the query with similar operator substitutions.
There are some other diffs in the trees that are a bit odd, but might be
explained if the new view was made by dump/reload rather than from the
identical SQL text the original view was made from; they all look like
they are references to JOIN output columns rather than the underlying
table columns or vice versa.  That's probably harmless, but the different
join operators certainly are not.

            regards, tom lane