Обсуждение: Cannot refresh materialized view concurrently if you have a column name called "mv"

Поиск
Список
Период
Сортировка

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

От
Rupert Agnew
Дата:

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

 


Rupert Agnew
Software Engineer

AlbaCore Capital Group
55 St James’s Street | London | SW1A 1LA | United Kingdom
T: 
+44 207 881 6028 | M: +44 793 881 9333 
RAgnew@albacorecapital.com | www.albacorecapital.com







Disclaimer
AlbaCore Capital Group is a trading name of AlbaCore Capital LLP which is authorised and regulated by the Financial Conduct Authority.
The information contained in this communication (including any attachments) from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful. AlbaCore Capital Group accepts no liability (to the fullest extent permitted by law) for any advice, opinions, conclusions, or other information in this message, other than that which can reasonably be expected by the intended recipient. Except as may be expressly set forth in this message, it is not intended, nor should it be taken, to create a legal or contractual relationship.
The information is not intended to influence you in making any investment decisions and should not be considered as investment advice or recommendation. Any Information may not be suitable for all investors and investors must make their own investment decisions using their own independent advisers and relevant offering material. Any investment decisions must be based upon an investor’s specific financial situations and investment objectives and should be based solely on the information in the relevant offering memorandum. Income from an investment may fluctuate and the price or value of any financial instruments referenced in this email may rise or fall. Past performance is not necessarily indicative of future results.

Вложения

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

От
Bruce Momjian
Дата:
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@sss.pgh.pa.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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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