"ALTER MATERIALIZED VIEW ... RENAME TO" problem

[nuko-yokohama]

If a view created with "CREATE INCREMENT MATERIALIZED VIEW" is renamed, subsequent INSERT operations to the base table will fail.

Error message.

ERROR:  could not open relation with OID 0

Execution log.

[ec2-user@ip-10-0-1-10 ivm]$ psql -U postgres test -e -f ~/test/ivm/alter_rename_bug.sql
DROP TABLE IF EXISTS table_x CASCADE;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:1: NOTICE:  drop cascades to materialized view group_imv
DROP TABLE
CREATE TABLE table_x AS
   SELECT generate_series(1, 10000) AS id,
   ROUND(random()::numeric * 100, 2) AS data,
   CASE (random() * 5)::integer
     WHEN 4 THEN 'group-a'
     WHEN 3 THEN 'group-b'
     ELSE 'group-c'
   END AS part_key
;
SELECT 10000
               Table "public.table_x"
  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 id       | integer |           |          |
 data     | numeric |           |          |
 part_key | text    |           |          |

DROP MATERIALIZED VIEW IF EXISTS group_imv;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:15: NOTICE:  materialized view "group_imv" does not exist, skipping
DROP MATERIALIZED VIEW
CREATE INCREMENTAL MATERIALIZED VIEW group_imv AS
SELECT part_key, COUNT(*), MAX(data), MIN(data), SUM(data), AVG(data)
FROM table_x
GROUP BY part_key;
SELECT 3
                 List of relations
 Schema |   Name    |       Type        |  Owner
--------+-----------+-------------------+----------
 public | group_imv | materialized view | postgres
 public | table_x   | table             | postgres
(2 rows)

             Materialized view "public.group_imv"
      Column       |  Type   | Collation | Nullable | Default
-------------------+---------+-----------+----------+---------
 part_key          | text    |           |          |
 count             | bigint  |           |          |
 max               | numeric |           |          |
 min               | numeric |           |          |
 sum               | numeric |           |          |
 avg               | numeric |           |          |
 __ivm_count_max__ | bigint  |           |          |
 __ivm_count_min__ | bigint  |           |          |
 __ivm_count_sum__ | bigint  |           |          |
 __ivm_count_avg__ | bigint  |           |          |
 __ivm_sum_avg__   | numeric |           |          |
 __ivm_count__     | bigint  |           |          |

SELECT * FROM group_imv ORDER BY part_key;
 part_key | count |  max  | min  |    sum    |         avg
----------+-------+-------+------+-----------+---------------------
 group-a  |  1966 | 99.85 | 0.05 |  98634.93 | 50.1703611393692777
 group-b  |  2021 | 99.99 | 0.17 | 102614.02 | 50.7738842157347848
 group-c  |  6013 | 99.99 | 0.02 | 300968.43 | 50.0529569266589057
(3 rows)

ALTER MATERIALIZED VIEW group_imv RENAME TO group_imv2;
ALTER MATERIALIZED VIEW
                 List of relations
 Schema |    Name    |       Type        |  Owner
--------+------------+-------------------+----------
 public | group_imv2 | materialized view | postgres
 public | table_x    | table             | postgres
(2 rows)

            Materialized view "public.group_imv2"
      Column       |  Type   | Collation | Nullable | Default
-------------------+---------+-----------+----------+---------
 part_key          | text    |           |          |
 count             | bigint  |           |          |
 max               | numeric |           |          |
 min               | numeric |           |          |
 sum               | numeric |           |          |
 avg               | numeric |           |          |
 __ivm_count_max__ | bigint  |           |          |
 __ivm_count_min__ | bigint  |           |          |
 __ivm_count_sum__ | bigint  |           |          |
 __ivm_count_avg__ | bigint  |           |          |
 __ivm_sum_avg__   | numeric |           |          |
 __ivm_count__     | bigint  |           |          |

SET client_min_messages = debug5;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:30: DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
INSERT INTO table_x VALUES (10000001, ROUND(random()::numeric * 100, 2),
'gruop_d');
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:  relation "public.group_imv" does not exist
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:  relation "public.group_imv" does not exist
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: ERROR:  could not open relation with OID 0
RESET client_min_messages;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:34: DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
RESET
SELECT * FROM group_imv2 ORDER BY part_key;
 part_key | count |  max  | min  |    sum    |         avg
----------+-------+-------+------+-----------+---------------------
 group-a  |  1966 | 99.85 | 0.05 |  98634.93 | 50.1703611393692777
 group-b  |  2021 | 99.99 | 0.17 | 102614.02 | 50.7738842157347848
 group-c  |  6013 | 99.99 | 0.02 | 300968.43 | 50.0529569266589057
(3 rows)

ALTER MATERIALIZED VIEW group_imv2 RENAME TO group_imv;
ALTER MATERIALIZED VIEW
INSERT INTO table_x VALUES (10000001, ROUND(random()::numeric * 100, 2),
'gruop_d');
INSERT 0 1
SELECT * FROM group_imv ORDER BY part_key;
 part_key | count |  max  |  min  |    sum    |         avg
----------+-------+-------+-------+-----------+---------------------
 group-a  |  1966 | 99.85 |  0.05 |  98634.93 | 50.1703611393692777
 group-b  |  2021 | 99.99 |  0.17 | 102614.02 | 50.7738842157347848
 group-c  |  6013 | 99.99 |  0.02 | 300968.43 | 50.0529569266589057
 gruop_d  |     1 | 81.43 | 81.43 |     81.43 | 81.4300000000000000
(4 rows)

[ec2-user@ip-10-0-1-10 ivm]$

This may be because IVM internal information is not modified when the view name is renamed.

[yugo-n]

Thanks! Please post the following thread for continued discussion. https://www.postgresql.org/message-id/201DD0641B056142AC8C6645EC1B5F62014B9DA86E%40SYD1217

Note for fixing: The basic causal is that view OID is retrieved by the view name. We need to fix this so that the OID will be passed as a argument of the trigger functions instead of the view name.

[yugo-n]

@nuko-yokohama This was fixed by the above commit. Could you please confirm and close this issue?

[nuko-yokohama]

I'm sorry that the confirmation was delayed. I have confirmed that the problem has been fixed. (commitid = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)