FATAL: terminating connection due to conflict with recovery

Products

• VMware Tanzu Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• Greenplum

Issue/Introduction

When reading data from an external Postgres database table, the SELECT returns an error:

Error :
  Reason:
SQL Error [XX000]: ERROR: transfer error (18): Transferred a partial file from '127.0.0.1:5888' (libchurl.c:930)  (seg1 slice1 192.XX.XX.XX:40001 pid=20174) (libchurl.c:930)
  Detail: curl error buffer: transfer closed with outstanding read data remaining
  Where: External table ext_test_table 

The pxf-service.log file reports:

2024-09-18 15:57:06.721 EST ERROR [:   ] 677975 --- [-exec-4139] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.greenplum.pxf.api.error.PxfRuntimeException: FATAL: terminating connection due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.
  Hint: In a moment you should be able to reconnect to the database and repeat your command.] with root cause

org.postgresql.util.PSQLException: FATAL: terminating connection due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.
  Hint: In a moment you should be able to reconnect to the database and repeat your command.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.4.3]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.4.3]
        at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2562) ~[postgresql-42.5.1.jar:42.4.3]
        at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:2145) ~[postgresql-42.5.1.jar:42.4.3]
         :
         :

 

Cause

The Greenplum DB is accessing an external table which is using Platform Extension Framework(PXF) to read data from a read-only standby Postgres DB.

The standby Postgres DB reports an error if the query takes too long to run in Postgres.

This is due to the settings max_standby_archive_delay and max_standby_streaming_delay being too low to allow.

Resolution

Option 1

Configure the Greenplum external table to read from the primary Postgres database instead of the standby database.

Option 2

Increase the parameters "max_standby_archive_delay" and "max_standby_streaming_delay" in the Postgres DB to be long enough to allow the query to run in the Postgres database.

See stackoverflow.com messages for more information

See Postgres Documenatation for more information on the parameters.