3

After encountering (on an RDS Postgres instance) for several times: 

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

I ran (on the hot standby):

SELECT *
FROM pg_stat_database_conflicts;

And found that all the conflicts have to do with confl_snapshot

Which is explained in the documentation as:

confl_snapshot: Number of queries in this database that have been canceled due to old snapshots

What might be causing this conflict (being an old snapshot)?

If it helps, here are some of the relevant settings (by running SHOW ALL ; on the stand by):

hot_standby: on
hot_standby_feedback: off
max_standby_archive_delay: 30s
max_standby_streaming_delay: 1h
name,setting
old_snapshot_threshold: -1
vacuum_defer_cleanup_age: 0
vacuum_freeze_min_age: 50000000
vacuum_freeze_table_age: 150000000
vacuum_multixact_freeze_min_age: 5000000
vacuum_multixact_freeze_table_age: 150000000
wal_level: replica
wal_receiver_status_interval: 10s
wal_receiver_timeout: 30s
wal_retrieve_retry_interval: 5s
wal_segment_size: 16MB
wal_sender_timeout: 30s
wal_writer_delay: 200ms
alt-f4
  • 2,112
  • 17
  • 49
  • See https://stackoverflow.com/questions/14592436/postgresql-error-canceling-statement-due-to-conflict-with-recovery – pifor May 09 '20 at 15:45
  • @pifor I understand the potential solutions mentioned in your link; however, that is not exactly what I am asking. I am particularly asking about confl_snapshot and how it relates to these settings. Which is not directly answered there. – alt-f4 May 09 '20 at 16:01
  • In [this documentation section](https://www.postgresql.org/docs/11/hot-standby.html#HOT-STANDBY-CONFLICT) there is no direct reference to confl_snapshot (it only says: _The number of query cancels and the reason for them can be viewed using the pg_stat_database_conflicts system view on the standby server_) but it describes what you a experiencing (_The most common reason for conflict between standby queries and WAL replay is “early cleanup”._) and related parameters. – pifor May 09 '20 at 16:36
  • What version of the database are you using? – jjanes May 10 '20 at 13:54
  • "I am particularly asking about confl_snapshot and how it relates to these settings" confl_snapshot is a count of how many times the error like the one you first reported has occurred. Surely you meant to ask something more interesting than that? But I can't really figure out what it is. – jjanes May 10 '20 at 14:04

0 Answers0