3

I currently have a PostgreSQL slave node that is presenting problems, that don't arise in the master node. Seems to be related with the node sync process.

Complete stacktrace:

org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:542)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:518)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.updateIgnicion(StatusSemanalClientes.java:448)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.access$2(StatusSemanalClientes.java:447)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:414)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:1)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

OS Version:

Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

The question is: how can I debug the actual cause of the problem and/or solve/avoid it? Don't hesitate to ask for more info if needed to pinpoint the problem, right now just don't know what else to provide.

PS: Error does NOT appear if the offending query is repeated, so it's not strictly related to the query itself, but perhaps with a table update/sync transient.

Improve this question

2 Answers 2

Reset to default
5

What you see is a common experience for anyone running standbys and bigger queries on the master. There are a couple of possible causes:

There are also additional types of conflict that can occur with Hot Standby. These conflicts are hard conflicts in the sense that queries might need to be canceled and, in some cases, sessions disconnected to resolve them. The user is provided with several ways to handle these conflicts. Conflict cases include:

Access Exclusive locks taken on the primary server, including both explicit LOCK commands and various DDL actions, conflict with table accesses in standby queries.

  • Dropping a tablespace on the primary conflicts with standby queries using that tablespace for temporary work files.
  • Dropping a database on the primary conflicts with sessions connected to that database on the standby.
  • Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows to be removed.
  • Application of a vacuum cleanup record from WAL conflicts with queries accessing the target page on the standby, whether or not the data to be removed is visible.

When a query will be killed for one of the above reasons is hard to predict, but usually there are some that appear more often. It is also configurable, for options read the documentation page further.

The most important option is hot_standby_feedback, which helps against the most common cause (VACUUM removing recently-dead rows). It has its own costs (might cause table bloat), but the outcome is usually still better than running the same queries on the master.

Improve this answer
7
  • I guess now I understand why this happens, but know I'd need to know how to overcome it to get the hot standby server "usable" and not only sitting idle with no actual workload. I've read that there are WAL-related parameters that may be handy, but I don't want to corrupt my slave server. Any hints on such?
    – Gonzalo Vasquez
    Dec 6, 2016 at 20:10
  • @GonzaloVasquez hot_standby_feedback is usually enough. You can try the other methods mentioned by the docs (setting WAL application delay), but it only makes these errors happening less often, not completely disappear. In both cases, the slave might be more or less behind the master (reflecting an earlier state of it) but never inconsistent - there is no reason for fear from a corrupted slave in these cases.
    – dezso
    Dec 6, 2016 at 20:39
  • Setting hot_standby_feedback should be done on the standby, master or both?
    – Gonzalo Vasquez
    Dec 6, 2016 at 22:20
  • hot_standby_feedback is set on the replica, it propagates feedback upstream to the master about the queries that are executing on the replica.
    – bma
    Dec 9, 2016 at 17:10
  • 1
    'A value of -1 allows the standby to wait forever for conflicting queries to complete.' - that sounds like a bad idea if you keep the replica for HA, too. It means WAL will accumulate on the master, possibly filling up the whole disk, which you don't really want (unless you are very good at moving files and creating symlinks under pressure ;)
    – dezso
    Dec 14, 2016 at 15:55
0

We did experience the same couple of times, where in we wrote to master , and reads were from slave. The above errors started appearing when we read from slave but at the same instant some other process due to a functional constraint removed that data. Hence in order to resolve such issues, we added our own "read your own writes" method, wherein there is a requirement for an application thread to read what was just written to db. In that case that particular thread that writes to master will read from master. Yes, the flip side to this is that there is a slight compromise in read scalability. We moved all reads to slave (stand by)where we were sure of consequent transactions not changing the data.There are more complex workarounds, such as changing transaction isolation level to repeatable read, but the application code gets complex and hard to maintain. HTH.

Improve this answer
1
  • Wow, sounds like a quite complex solution!
    – Gonzalo Vasquez
    Dec 14, 2016 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.