> 3 нояб. 2015 г., в 11:38, Andres Freund <and...@anarazel.de> написал(а):
> On 2015-11-02 15:37:57 -0500, Robert Haas wrote:
>> On Fri, Oct 30, 2015 at 9:49 AM, Vladimir Borodin <r...@simply.name> wrote:
>>> I’ve tried two ways - bare SELECT in autocommit mode and BEGIN; SELECT;
>>> ROLLBACK. I first described the problem in thread on pgsql-admin@ [0], there
>>> is copy-paste from psql there, but during conversation initial description
>>> was lost.
>>> [0]
>>> http://www.postgresql.org/message-id/7f74c5ea-6741-44fc-b6c6-e96f18d76...@simply.name
>> Hmm.  That behavior seems unexpected to me, but I might be missing something.
> The conflict is because of a relation lock, not because of
> visibility. Hot-Standby feedback changes nothing about that.
> I presume all the other conflicts are all because of relation level
> locks? Check pg_stat_database_conflicts and the server logs to verify.

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 
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 
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 
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 
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 

And am I right that the only way not to have confl_lock is to increase 

> Andres
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

May the force be with you…

Reply via email to