Cannot refresh materialized view concurrently if you have a column name called "mv" - Mailing list pgsql-bugs

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

This is unfortunate as the column name “mv” is very common in financial services (meaning market-value!)

Thanks,

Rupert