| From: | Mathis Rudolf <mathis(dot)rudolf(at)credativ(dot)de> | 
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org | 
| Subject: | Alias collision in `refresh materialized view concurrently` | 
| Date: | 2021-05-19 12:03:01 | 
| Message-ID: | 109c267a-10d2-3c53-b60e-720fcf44d9e8@credativ.de | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-hackers | 
Hello,
we had a Customer-Report in which `refresh materialized view 
CONCURRENTLY` failed with: `ERROR: column reference "mv" is ambiguous`
They're using `mv` as an alias for one column and this is causing a 
collision with an internal alias. They also made it reproducible like this:
```
create materialized view testmv as select 'asdas' mv; --ok
create unique index on testmv (mv); --ok
refresh materialized view testmv; --ok
refresh materialized view CONCURRENTLY testmv; ---BAM!
```
```
ERROR: column reference "mv" is ambiguous
LINE 1: ...alog.=) mv.mv AND newdata OPERATOR(pg_catalog.*=) mv) WHERE ...
                                                               ^
QUERY:  CREATE TEMP TABLE pg_temp_4.pg_temp_218322_2 AS SELECT mv.ctid 
AS tid, newdata FROM public.testmv mv FULL JOIN pg_temp_4.pg_temp_218322 
newdata ON (newdata.mv OPERATOR(pg_catalog.=) mv.mv AND newdata 
OPERATOR(pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid
```
The corresponding Code is in `matview.c` in function 
`refresh_by_match_merge`. With adding a prefix like `_pg_internal_` we 
could make collisions pretty unlikely, without intrusive changes.
The appended patch does this change for the aliases `mv`, `newdata` and 
`newdata2`.
Kind regards,
Mathis
| Attachment | Content-Type | Size | 
|---|---|---|
| 0001-Fix-alias-collision-for-REFRESH-MV-CONCURRENTLY.patch | text/x-patch | 4.1 KB | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dean Rasheed | 2021-05-19 12:06:24 | Re: pgbench test failing on 14beta1 on Debian/i386 | 
| Previous Message | Greg Nancarrow | 2021-05-19 11:55:24 | Re: Parallel INSERT SELECT take 2 |