6

I'm creating a reporting engine that makes a couple of long queries over a standby server and process the result with pandas. Everything works fine but sometimes I have some issues with the execution of those queries using a psycopg2 cursor: the query is cancelled with the following message: 

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

I was investigating this issue

PostgreSQL ERROR: canceling statement due to conflict with recovery

https://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT

but all solutions suggest fixing the issue making modifications to the server's configuration. I can't make those modifications (We won the last football game against IT guys :) ) so I want to know how can I deal with this situation from the perspective of a developer. Can I resolve this issue using python code? My temporary solution is simple: catch the exception and retry all the failed queries. Maybe could be done better (I hope so).

Thanks in advance

Community
  • 1
  • 1
Juan David
  • 2,676
  • 4
  • 32
  • 42

4 Answers4

8

There is nothing you can do to avoid that error without changing the PostgreSQL configuration (from PostgreSQL 9.1 on, you could e.g. set hot_standby_feedback to on).

You are dealing with the error in the correct fashion – simply retry the failed transaction.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    I'm having this same issue, as far as I can tell this configuration can not be changed from python/psycopg2, is that correct? Simply retrying the transaction is not working for me... – Max Power Apr 04 '18 at 19:09
  • You can change `hot_standby_feedback` with `ALTER SYSTEM` and then `pg_reload_conf()`. Perhaps your problem persists because there is so much activity on the primary and your query is so long that you keep running into the problem again and again. – Laurenz Albe Apr 05 '18 at 07:21
3

The table data on the hot standby slave server is modified while a long running query is running. A solution (PostgreSQL 9.1+) to make sure the table data is not modified is to suspend the replication on the slave and resume after the query.

select pg_xlog_replay_pause(); -- suspend
select * from foo; -- your query
select pg_xlog_replay_resume(); --resume
David Jaspers
  • 241
  • 2
  • 5
3

I recently encountered a similar error and was also in the position of not being a dba/devops person with access to the underlying database settings.

My solution was to reduce the time of the query where ever possible. Obviously this requires deep knowledge of your tables and data, but I was able to solve my problem with a combination of a more efficient WHERE filter, a GROUPBY aggregation, and more extensive use of indexes.

By reducing the amount of server side execute time and data, you reduce the chance of a rollback error occurring.

However, a rollback can still occur during your shortened window, so a comprehensive solution would also make use of some retry logic for when a rollback error occurs.

Update: A colleague implemented said retry logic as well as batching the query to make the data volumes smaller. These three solutions have made the problem go away entirely.

AZhao
  • 13,617
  • 7
  • 31
  • 54
1

I got the same error. What you CAN do (if the query is simple enough), is deviding the data into smaller chunks as a workaround. I did this within a python loop to call the query multiple times with the LIMIT and OFFSET parameter like:

query_chunk = f"""
        SELECT *
        FROM {database}.{datatable}
        LIMIT {chunk_size} OFFSET {i_chunk * chunk_size}
"""

where database and datatable are the names of your sources.. The chunk_size is individually and to set this to a not too high value is crucial for the query to finish.

Fabian Wörenkämper
  • 63
  • 4
  • Also if you're using python you can wrap your chunked query in a function then decorate it with a `@retry` decorator from the `tenacity` library. That way if one of the chunks conflicts with recovery it'll retry rather than throw out of the loop. – David Waterworth Mar 16 '23 at 23:12