Querying Foreign Tables via greenplum_fdw with WHERE Clause Fails with ERROR: portal "" does not exist

Products

• VMware Tanzu Data
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

When executing a query with a WHERE clause against a foreign table created via greenplum_fdw, the query fails with the following error in JDBC clients (e.g., DBeaver) or Python applications using psycopg:

SQL Error [34000]: ERROR: portal "" does not exist

However, directly running the query via psql to the database works fine.

Environment

• Database Engine: Greenplum Database (GPDB) 6.x / 7.x
• Extension: greenplum_fdw 
• Drivers Tested & Affected:
  • Pivotal Greenplum JDBC Driver (com.pivotal.jdbc.GreenplumDriver)
  • PostgreSQL JDBC Driver (org.postgresql.Driver)
  • Python psycopg (v3) client library
• Client Applications: DBeaver, Custom Java Applications, Python Applications

Cause

This issue stems from a known issue in the Greenplum server's Extended Query Protocol handling when dispatching queries involving greenplum_fdw.

Resolution

Permanent Resolution

Upgrade to Greenplum 7.8.2 or higher.

 

Temporary Workaround

Option 1: Force Simple Query Mode

Modify your JDBC connection string or driver properties to enforce the Simple Query Protocol instead of the Extended Query Protocol.

• Implementation (DBeaver/JDBC): Go to Connection Properties -> Driver Properties -> Add or set preferQueryMode = simple.

Important Considerations for Option 1:

• Loss of Server-Side Precompilation: Forcing simple mode means the driver will no longer use PreparedStatement optimization on the server side. SQL statements are sent as raw text strings.
• Increased Network Overhead: Data types are sent as text rather than binary, which might marginally increase network payload size for massive datasets.

Option 2: Disable Autocommit (`autocommit=false`)

Disable the driver's automatic transaction commitment to artificially extend the lifecycle of the unnamed portal.

• Implementation (DBeaver): Toggle the toolbar connection status from Auto-Commit to Manual Commit.
• Implementation (Java/JDBC): Explicitly invoke connection.setAutoCommit(false); in your application code.

Important Considerations for Option 2:

• Risk of "Idle in Transaction" / Locking: Because automatic commit is disabled, every query begins a long-running transaction. If you modify rows (DML) or hold locks and forget to call COMMIT, you can lock out other users and block production workloads.
• Manual Transaction Overhead: You (or your application logic) assume 100% responsibility for executing explicit COMMIT; or ROLLBACK; statements at the end of every business logic unit.