On Thu, Jul 2, 2020 at 8:44 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
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.)
The citext extension seems to have been installed into the public schema as well which could introduce the CVE-2018-1058 fix as a potential moving part.
It seems a bit odd though since the textual query does specify "DISTINCT mails_contacts_opens.email::public.citext" so it does seem to be search_path induced as the view couldn't exist if the extension was simply missing not extension specific equality operator were present to match in front of the default equality operator. But then those casts also make me question whether the source tables are defined using text instead of citext in which case the joins using text equality would be expected and their using citext equality in the new queries potentially suspect.
David J.
