Oh, good point, thank you, it gives the answer. Actually I’ve already done a switchover in this cluster, so pg_stat_database_conflicts started from scratch :( But the logs haven’t been rotated yet: root@rpopdb01e ~ # fgrep -e 562f9ef0.23df,6 -e 562fa107.451a -e 562fa1d9.5146 -e 562f9ef0.23df,10 -e 562fa259.56d1 /var/lib/pgsql/9.4/data/pg_log/postgresql-2015-10-27_185736.csv 2015-10-27 19:06:28.656 MSK,,,9183,,562f9ef0.23df,6,,2015-10-27 18:57:36 MSK,,0,LOG,00000,"parameter ""hot_standby_feedback"" changed to ""off""",,,,,,,,,"" 2015-10-27 19:10:05.039 MSK,"postgres","rpopdb",17690,"[local]",562fa107.451a,1,"",2015-10-27 19:06:31 MSK,12/54563,0,ERROR,40001,"canceling statement due to conflict with recovery","User query might have needed to see row versions that must be removed.",,,,,"select count(*) from rpop.rpop_imap_uidls;",,,"psql" 2015-10-27 19:10:05.995 MSK,"monitor","rpopdb",20806,"localhost:51794",562fa1d9.5146,1,"",2015-10-27 19:10:01 MSK,15/24192,0,ERROR,40001,"canceling statement due to conflict with recovery","User was holding shared buffer pin for too long.",,,,"SQL function ""to_timestamp"" statement 1","select cnt from monitor.bad_rpop_total",,,"" 2015-10-27 19:12:06.878 MSK,,,9183,,562f9ef0.23df,10,,2015-10-27 18:57:36 MSK,,0,LOG,00000,"parameter ""hot_standby_feedback"" changed to ""on""",,,,,,,,,"" 2015-10-27 19:17:57.056 MSK,"postgres","rpopdb",22225,"[local]",562fa259.56d1,1,"",2015-10-27 19:12:09 MSK,3/35442,0,FATAL,40001,"terminating connection due to conflict with recovery","User was holding a relation lock for too long.",,,,,"select count(*) from rpop.rpop_imap_uidls;",,,"psql" root@rpopdb01e ~ # So FATAL is due to relation lock and one ERROR is due to pinned buffers (this is actually from another user) but there is also one ERROR due to old snapshots (first line). But I actually turned off hs_feedback before first ERROR and turned it on after it. So it seems to work expectedly. Does it actually mean that I could get such conflicts (due to relation locks, for example) even in repeatable read or serializable? I mean, is there any dependency between transaction isolation level on standby and conflicts with recovery? And am I right that the only way not to have confl_lock is to increase max_standby_streaming_delay?
|
