Report Sometimes Return: "ERROR: canceling statement due to conflict with recovery"


#1

Hi everyone,

This is one of the common issues that we receive from our customers, and usually pointing Holistics directly to their production cluster. Basically your report will run for a while, and then return this error:

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

Answer:
This likely happens because you’re connecting Holistics to a PostgreSQL replica server, and the replica server has hot-standby mode enabled.

When your long-running query is running, some of the data is being cleaned up (vacuum) and deleted, thus causing the currently running query to halt.

The quick solution is to set this config on a replica server (do get your backend/data engineer to help with the change):

hot_standby_feedback = on

For a more detailed explanation, you can also refer to this StackOverflow link.

Do note that if you’re regularly getting this error, perhaps it’s time to start looking into setting up a proper data warehouse.