Refresh Materialized View results in ERROR: no pre-assigned OID for relation "pg_temp_xxxxx" in VMware Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

When trying to refresh a materialized view containing a subquery that uses an underlying function, you get the following error:

orange=# CREATE MATERIALIZED VIEW kevin2 AS
orange-# SELECT t.id, t.name, t.dt
orange-#   FROM ( SELECT *
orange(#       FROM y
orange(#      WHERE y.dt = tpa.get_current_table_date('ip_locations'::text)) t
orange-#    JOIN x ON t.id = x.id
orange-#  ORDER BY id, name
orange-#  Distributed by (id);
SELECT 1
orange=# refresh materialized view kevin2;
ERROR:  no pre-assigned OID for relation "pg_temp_2124866" (oid_dispatch.c:692)  (seg2 192.168.99.101:35014 pid=6468) (oid_dispatch.c:692)

Environment

Product Version: 6.16

Resolution

This is an internal issue with the way the Query Dispatcher (QD) handles OIDs. Normally when refreshing a materialized view, the QD will create a temporary table with an associated OID. The OID is temporarily stored so on dispatch, the OID in the segments will be the same as the master. 

However, when a function that contains an underlying SQL expression is involved, it can be pre-evaluated, which means a dispatch occurs and uses the OID that was stored previously. When the statement is again dispatched later, you will see the pre-assigned OID error.

This error will be fixed in a later version of VMware Tanzu Greenplum.