ORCA fallback to Postgres Planner with reason: Query Parameter not supported in DXL

Products

• VMware Tanzu Greenplum

Issue/Introduction

When running functions in the Greenplum, we might see the below error

-- From client end:
DETAIL:  GPDB Expression type: Query Parameter not supported in DXL

-- logs:
2024-09-19 15:39:02.806764 CST,"gpadmin","gpadmin",p15583,th-1652299648,"[local]",,2024-09-19 15:38:57 CST,0,con26,cmd9,seg-1,,,,sx1,"LOG","00000","2024-09-19 15:39:02:806572 CST,THD000,ERROR,""GPDB Expression type: Query Parameter not supported in DXL"",

Noted that, in the recent version of the Greenplum, the fallback message has changed to below (Also the log level changed from ERROR to NOTICE)

-- client:
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  Feature not supported: Query Parameter

-- logs:
2024-09-19 15:45:40.263130 CST,"gpadmin","gpadmin",p16861,th1289992320,"[local]",,2024-09-19 15:45:36 CST,0,con235,cmd4,seg-1,,,,sx1,"LOG","00000","2024-09-19 15:45:40:263032 CST,THD000,NOTICE,""Feature not supported: Query Parameter"",

Resolution

This is an expected error because ORCA does not support Query Parameter in DXL, for example:

CREATE table temp01 as select id,now() from generate_series(1,10000) id distributed by (id);
CREATE OR REPLACE FUNCTION test_func(id int)
RETURNS character varying AS $$
Declare
    record text;
BEGIN
    select now from temp01 where temp01.id = $1 into record;
    RETURN record;
END;
$$ LANGUAGE plpgsql;

set optimizer_trace_fallback to on;
select test_func(123);
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  Feature not supported: SIRV functions
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  GPDB Expression type: Query Parameter not supported in DXL
CONTEXT:  SQL statement "SELECT record"

The query will still be able to run with Postgres planner. 

For customers on supported versions, you can enable optimizer_enable_query_parameter = on at the session level to allow GPORCA to handle these parameterized queries without falling back to the legacy planner. To verify behavior after this setting change, use the parameter optimizer_trace_fallback = on.

Noted that, this has been supported after Greenplum 7.2, refer to the release note for more details: 
VMware Greenplum 7.x Release Notes

Release 7.2.0

Enhancements
GPORCA now supports a number of features previously only supported in the Postgres-based Planner:

...
Functions containing query parameters
...