Alias collision in `refresh materialized view concurrently`

Поиск
Список
Период
Сортировка
От Mathis Rudolf
Тема Alias collision in `refresh materialized view concurrently`
Дата
Msg-id 109c267a-10d2-3c53-b60e-720fcf44d9e8@credativ.de
обсуждение исходный текст
Ответы Re: Alias collision in `refresh materialized view concurrently`  (Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>)
Список 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


Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Greg Nancarrow
Дата:
Сообщение: Re: Parallel INSERT SELECT take 2
Следующее
От: Dean Rasheed
Дата:
Сообщение: Re: pgbench test failing on 14beta1 on Debian/i386