Create View on External Table automatically appends ONLY Keyword

Products

• VMware Tanzu Greenplum

Issue/Introduction

In GPDB 5.28.3 and below, creating a view on an external table will automatically append ONLY keyword to the view definition:

gpadmin=# select version();
                                                                                               version                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.23 (Greenplum Database 5.28.0 build commit:a832433219ef0159bacde60f00df614c5f31e743) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jun 25 2020 23:01:59
(1 row)

gpadmin=# CREATE EXTERNAL TABLE kevin_ext 
gpadmin-#    (id int, name text, sponsor text) 
gpadmin-#    LOCATION ( 'gpfdist://gpdb-m:8099/test.csv' ) 
gpadmin-#    FORMAT 'CSV' ( DELIMITER ',' );
CREATE EXTERNAL TABLE

gpadmin=# create or replace view ####_ext_v as select * from ####_ext;                                                                                                                                            CREATE VIEW
gpadmin=# \d+  kevin_ext_v
               View "public.####_ext_v"
 Column  |  Type   | Modifiers | Storage  | Description 
---------+---------+-----------+----------+-------------
 id      | integer |           | plain    | 
 name    | text    |           | extended | 
 sponsor | text    |           | extended | 
View definition:
 SELECT kevin_ext.id, ####_ext.name, ####_ext.sponsor
   FROM ONLY kevin_ext;

In 6.x, it does not do that:

gpadmin=# select version();
                                                                                                version                                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.24 (Greenplum Database 6.11.1 build commit:df5f06d6fecffb4de64ab4ed2a1deb3a45efa37c) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Sep 17 2020 03:08:40
(1 row)

gpadmin=# create or replace view #####_ext_v as select * from kevin_ext;     
CREATE VIEW
gpadmin=# \d+ kevin_ext_v;

               View "public.#####_ext_v"
 Column  |  Type   | Modifiers | Storage  | Description 
---------+---------+-----------+----------+-------------
 id      | integer |           | plain    | 
 name    | text    |           | extended | 
 sponsor | text    |           | extended | 
View definition:
 SELECT ####_ext.id,
    ####_ext.name,
    ####_ext.sponsor
   FROM ####_ext;

As a result, ORCA in general does not support the ONLY keyword, but in 5.x it will ignore it for external tables and allow ORCA to be used. If you were to run the same query in 6.x after a data migration, then the query will fallback to the legacy planner. If your query contains a combination of this view and normal heap tables, then it will likely perform better. However, in 6.x the same query would fall back to the legacy planner and cause unexpected performance issues.

Environment

Product Version: 6.10

Resolution

As this has no impact in 5.x, you would only feel the impact of query performance in the following situations:

1. Upgrade to version 5.28.4
2. Migrations from version 5.28.3 and below to 6.x

In both scenarios, after you upgrade or migrate, you will have to recreate the views without the ONLY keyword as the create view behavior is changed in version 5.28.4 to not auto-append the ONLY keyword to view definitions.