pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vladimir Borodin <root@simply.name>
To: Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Oleksii Kliukin <alexk@hintbits.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [ADMIN] Replication slots and isolation levels
Date: Tue, 3 Nov 2015 12:51:22 +0300
Message-ID: <431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name> (raw)
In-Reply-To: <20151103083842.GQ11897@awork2.anarazel.de>
References: <CAB7nPqQZAqvwpJ3eQ0BXGdw26fjNuKXXGHijvdahVXreNbsASA@mail.gmail.com>
	<0E6700E4-195C-450D-A051-D19F92B2A9D7@simply.name>
	<CAB7nPqTSVXR1WQegikpnkATjR3oz8FQE3hUf9RUk+o9VN0+zrQ@mail.gmail.com>
	<86D2C6D2-C332-4BF9-A82E-C9F7FA471712@simply.name>
	<B0D38B8D-C174-44EE-9F84-31B8EE68AA8C@hintbits.com>
	<CA+TgmoZzi8-72PksOf8NDBz8nGU97NH_VUgNybxyB_5U_tBBCg@mail.gmail.com>
	<088B276D-4073-4582-82D4-08F50E43B426@simply.name>
	<CA+TgmoYn=Myxu9QHBHzwZmQtsHn=GYCix6O+LNyHxQrgP86T8w@mail.gmail.com>
	<95FD0B1A-7BD1-4CCE-86D4-2DF2B2AE2C68@simply.name>
	<CA+TgmoamGeAtziNP1KtAuVxM8GHhog0ZOdrrvjNErnfEuqfBsA@mail.gmail.com>
	<20151103083842.GQ11897@awork2.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>


> 3 нояб. 2015 г., в 11:38, Andres Freund <andres@anarazel.de> написал(а):
> 
> On 2015-11-02 15:37:57 -0500, Robert Haas wrote:
>> On Fri, Oct 30, 2015 at 9:49 AM, Vladimir Borodin <root@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-E96F18D761FB@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 /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?


> 
> 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…
https://simply.name

view thread (19+ messages)

Message-ID: <431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name>
Permalink:  ../431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name/
Also on:    postgresql.org/message-id/431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: root@simply.name, andres@anarazel.de, robertmhaas@gmail.com, alexk@hintbits.com, michael.paquier@gmail.com, pgsql-hackers@postgresql.org
  Subject: Re: [ADMIN] Replication slots and isolation levels
  In-Reply-To: <431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name>
  References: <CAB7nPqQZAqvwpJ3eQ0BXGdw26fjNuKXXGHijvdahVXreNbsASA@mail.gmail.com> <0E6700E4-195C-450D-A051-D19F92B2A9D7@simply.name> <CAB7nPqTSVXR1WQegikpnkATjR3oz8FQE3hUf9RUk+o9VN0+zrQ@mail.gmail.com> <86D2C6D2-C332-4BF9-A82E-C9F7FA471712@simply.name> <B0D38B8D-C174-44EE-9F84-31B8EE68AA8C@hintbits.com> <CA+TgmoZzi8-72PksOf8NDBz8nGU97NH_VUgNybxyB_5U_tBBCg@mail.gmail.com> <088B276D-4073-4582-82D4-08F50E43B426@simply.name> <CA+TgmoYn=Myxu9QHBHzwZmQtsHn=GYCix6O+LNyHxQrgP86T8w@mail.gmail.com> <95FD0B1A-7BD1-4CCE-86D4-2DF2B2AE2C68@simply.name> <CA+TgmoamGeAtziNP1KtAuVxM8GHhog0ZOdrrvjNErnfEuqfBsA@mail.gmail.com> <20151103083842.GQ11897@awork2.anarazel.de> <431D21F8-BD78-4953-80EC-FFF1BF7DA6F5@simply.name>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox