How can I fix PostgreSQL canceling statement error on Google SQL?

Question

We have PostgreSQL instances (1 master + 1 read replica) on Google SQL. Our Django (1.11.12) application uses these databases via PostGIS engine. When we try to use the database, we saw this error message:

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

When I search for a solution, they generally say that I need to change hot_standby_feedback flag. But as you know Google SQL service has some restrictions about settings. I can't set the flag.

How can I fix this?

Answer 1

If “Google SQL” allows that, you can set max_standby_streaming_delay to -1 so that replication is delayed if a conflict is detected.

Then the query will not be canceled, but replication may lag if applying changes would cause a conflict.

Consider getting an “unfettered” PostgreSQL.

Answer 2

If you would like set hot_standby_feedback = on, I'll suggest that you indicate your interest in the open feature request on Google Cloud Platform's Public Issue Tracker tool. That way someone can look into the handling query conflict issues your Cloud SQL PostgreSQL instance encountered.

I've also been monitoring an open thread in the Issue Tracker about making max_standby_archive_delay and max_standby_streaming_delay flags available to users to set. You can track it there as well. Hope this helps!