Re: Cannot refresh materialized view concurrently if you have a column name called "mv"

On Thu, Mar 3, 2022 at 02:53:47PM +0000, Rupert Agnew wrote:
> Hi PSQL bugs,
>
> I have discovered a bug where a column name called “mv” in a materialized view
> cannot be refreshed concurrently due to an internal PostgreSQL table alias also
> named “mv”.
>
> Refreshing materialized view without the concurrently keyword runs just fine.
>
> This can be replicated with the following few lines of code:
>
> create materialized view tmp_mv as
>
> select 1 id, 2 mv;
>
> create unique index tmp_mv_index on tmp_mv(id);
>
> refresh materialized view concurrently tmp_mv;
>
> The error (42702) outputted:
>
> psycopg2.errors.AmbiguousColumn: column reference "mv" is ambiguous
>
> LINE 1: ...alog.=) mv.id AND newdata OPERATOR(pg_catalog.*=) mv) WHERE ...
>
> ^
>
> QUERY: CREATE TEMP TABLE pg_temp_13.pg_temp_153460785_2 AS SELECT mv.ctid AS
> tid, newdata FROM public.tmp_mv mv FULL JOIN pg_temp_13.pg_temp_153460785
> newdata ON (newdata.id OPERATOR(pg_catalog.=) mv.id AND newdata OPERATOR
> (pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid
>
> To solve this you need to add the table alias to the column name in the where
> statement on the final line in the error message highlighted above.
>
>
>
> I am running the following PostgreSQL version:
>
> PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by
> x86_64-unknown-linux-gnu-gcc (GCC) 4.9.4, 64-bit

Uh, this is fixed in Postgres 10.18:

https://www.postgresql.org/docs/10/release-10-18.html

Avoid alias conflicts in queries generated for REFRESH MATERIALIZED VIEW
CONCURRENTLY (Tom Lane, Bharath Rupireddy)

This command failed on materialized views containing columns with
certain names, notably mv and newdata.

by this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=9179a82d7a

commit 9179a82d7a
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Sat Aug 7 13:29:32 2021 -0400

Really fix the ambiguity in REFRESH MATERIALIZED VIEW CONCURRENTLY.

Rather than trying to pick table aliases that won't conflict with
any possible user-defined matview column name, adjust the queries'
syntax so that the aliases are only used in places where they can't be
mistaken for column names. Mostly this consists of writing "alias.*"
not just "alias", which adds clarity for humans as well as machines.
We do have the issue that "SELECT alias.*" acts differently from
"SELECT alias", but we can use the same hack ruleutils.c uses for
whole-row variables in SELECT lists: write "alias.*::compositetype".

We might as well revert to the original aliases after doing this;
they're a bit easier to read.

Like 75d66d10e, back-patch to all supported branches.

Discussion: https://postgr.es/m/2488325.1628261320@sss.pgh.pa.us

So, upgrade to 10.18, or idally 10.20, and it should work fine.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.