Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZroDB-0003yQ-Ax for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Oct 2015 14:29:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZroDA-0005JU-QN for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Oct 2015 14:29:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZroDA-0005JF-32 for pgsql-hackers@postgresql.org; Thu, 29 Oct 2015 14:29:52 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZroD5-0000YJ-SE for pgsql-hackers@postgresql.org; Thu, 29 Oct 2015 14:29:51 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.42]) by mailout.nyi.internal (Postfix) with ESMTP id CD741207E2; Thu, 29 Oct 2015 10:29:45 -0400 (EDT) Received: from frontend2 ([10.202.2.161]) by compute2.internal (MEProxy); Thu, 29 Oct 2015 10:29:45 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=hintbits.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to:x-sasl-enc:x-sasl-enc; s=mesmtp; bh=hIPHM WvH9sLmxplfkHQ9lUOj1iw=; b=H4yp91MnUEsWtc/oe7uoYwn7zJ034XQZdmuX0 hlu0zXOaIxjeKrEaTu2P65gSRKT94Qc6gK/ia3YEZiEhuNX7OrHLMLdfYCy5WngL Pnu+Gam5XU5rG4/SZPdicKozrGHoWs2vLAhRdfQ2Q+Z6Us7AYvTSIt1wPtfLWQV2 ict6fE= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-sasl-enc :x-sasl-enc; s=smtpout; bh=hIPHMWvH9sLmxplfkHQ9lUOj1iw=; b=a7QrA EYm7kHJxHLw5M72oLqKVfEsv4N7SuWjujxjv1fClarlDgZngxqgyvU2tK3s5m0Ce NA0FaIEBOXS2DcZ/1c5h2EULZkt0FD5dlWHzl9Esmq7uY6g6/ARk/I65W0Xb3VoK AQtSbeP5mErgRHR2kEeZqCaL1pM02sfeP0UAHw= X-Sasl-enc: bXDyzBo5c6fHJ6N7VafFrzZiK5WlL4GnVor0NKndY//C 1446128985 Received: from [10.161.142.254] (unknown [94.135.236.132]) by mail.messagingengine.com (Postfix) with ESMTPA id E86CA680118; Thu, 29 Oct 2015 10:29:44 -0400 (EDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_2A29261B-06EB-4D09-A3AA-06B22FFF3FBA" Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) Subject: Re: [ADMIN] Replication slots and isolation levels From: Oleksii Kliukin In-Reply-To: <86D2C6D2-C332-4BF9-A82E-C9F7FA471712@simply.name> Date: Thu, 29 Oct 2015 15:29:43 +0100 Cc: Michael Paquier , pgsql-admin , PostgreSQL-development Message-Id: References: <7F74C5EA-6741-44FC-B6C6-E96F18D761FB@simply.name> <52570F37-5C6E-40F9-A2E3-01858705B3E8@simply.name> <0E6700E4-195C-450D-A051-D19F92B2A9D7@simply.name> <86D2C6D2-C332-4BF9-A82E-C9F7FA471712@simply.name> To: Vladimir Borodin X-Mailer: Apple Mail (2.2104) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --Apple-Mail=_2A29261B-06EB-4D09-A3AA-06B22FFF3FBA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 29 Oct 2015, at 14:39, Vladimir Borodin wrote: >=20 > f I understand right, with hot_standby_feedback =3D on standby tells = the master xmin of the earliest transaction on standby. And autovacuum = worker on master takes it into account when doing vacuum cleanup = (because it can see it from pg_replications_slots view), AFAIK with = GetOldestXmin function. But I don=E2=80=99t understand why with READ = COMMITED transactions xmin in pg_replication_slots view on master = continues to increase while with REPEATABLE READ xmin freezes until this = transaction finishes. Could it be a consequence of how REPEATABLE READ transactions handle = snapshots? With REPEATABLE READ the snapshot is acquired only once at = the beginning of a transaction; a READ COMMITTED transaction = re-evaluates its snapshot with each new command. http://www.postgresql.org/docs/current/static/transaction-iso.html Kind regards, -- Oleksii --Apple-Mail=_2A29261B-06EB-4D09-A3AA-06B22FFF3FBA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On 29 Oct 2015, at 14:39, Vladimir Borodin <root@simply.name> = wrote:

f I understand right, = with hot_standby_feedback =3D on standby tells the master xmin of the = earliest transaction on standby. And autovacuum worker on master takes = it into account when doing vacuum cleanup (because it can see it from = pg_replications_slots view), AFAIK with GetOldestXmin function. But = I don=E2=80=99t understand why with READ COMMITED transactions xmin in = pg_replication_slots view on master continues to increase while with = REPEATABLE READ xmin freezes until this transaction = finishes.

Could = it be a consequence of how REPEATABLE READ transactions handle = snapshots? With REPEATABLE READ the snapshot is acquired only once at = the beginning of a transaction; a READ COMMITTED transaction = re-evaluates its snapshot with each new command.


Kind regards,
--
Oleksii

= --Apple-Mail=_2A29261B-06EB-4D09-A3AA-06B22FFF3FBA--