Received: from malur.postgresql.org ([2a02:16a8:dc51::56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1fxwJU-0007FR-Tq
	for pgsql-admin@arkaria.postgresql.org; Thu, 06 Sep 2018 15:35:21 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1fxwJT-0006vS-7j
	for pgsql-admin@arkaria.postgresql.org; Thu, 06 Sep 2018 15:35:19 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <gsievers19@comcast.net>)
	id 1fxwJS-0006ub-TM
	for pgsql-admin@lists.postgresql.org; Thu, 06 Sep 2018 15:35:19 +0000
Received: from resqmta-ch2-03v.sys.comcast.net
 ([2001:558:fe21:29:69:252:207:35])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <gsievers19@comcast.net>)
	id 1fxwJQ-0004vT-AQ
	for pgsql-admin@postgresql.org; Thu, 06 Sep 2018 15:35:17 +0000
Received: from resomta-ch2-01v.sys.comcast.net ([69.252.207.97])
	by resqmta-ch2-03v.sys.comcast.net with ESMTP
	id xuEefHGj1PUIwxwJOfuQt8; Thu, 06 Sep 2018 15:35:14 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net;
	s=q20161114; t=1536248114;
	bh=ZEEdrbZC5pxOAncAQYaf+9Py2o0bxvpsfCXNT11tS7A=;
	h=Received:Received:Received:From:To:Subject:Date:Message-ID:
	 MIME-Version:Content-Type;
	b=NUD/5RhxoqsnATd7GAv6irEQWmdM9d0mJc5z1Lg9O879UuB48G0TMY0W0BEUqVyyb
	 2hwndICCPmjegWJGI5lwYOfoquCDyTj+BPG6bWldgr7RiSLQPbXul9W5Ejkh2CciSI
	 Cl7x7xelNt8Ejz7+uwd0ab2iUhtOIGokSiyZijAyLISL8vf5DuX/2KfumOyX/Xluhk
	 NoDjxc5bgA0bHrwjZi9iPJmkiCl4EGdHOqmRdtlhNd6lh8VlXkELa1+m/RoMvQVEfp
	 gnp1p1hmr8TGpEpelYS0SU4uzGm7Q2UnxvWKQ7Xs0xfjgkmcvHKGTWRIcJF68pw/AX
	 p4pg5xtm8QsVA==
Received: from jsievers.comcast.net ([12.187.116.132])
	by resomta-ch2-01v.sys.comcast.net with ESMTPA
	id xwJFfzMOdDKDWxwJFfw0bb; Thu, 06 Sep 2018 15:35:12 +0000
Received: from jsievers.enova.com (localhost [127.0.0.1])
	by jsievers.comcast.net (Postfix) with ESMTP id 51658260056;
	Thu,  6 Sep 2018 10:35:05 -0500 (CDT)
From: Jerry Sievers <gsievers19@comcast.net>
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Determine which query prevents applying WALs on standby
References: 
 <CAJw4d1WDwy9CBvuagZDfHbjWmU3smfBR4X2uxL698e7m5JZzxQ@mail.gmail.com>
Date: Thu, 06 Sep 2018 10:35:05 -0500
In-Reply-To: 
 <CAJw4d1WDwy9CBvuagZDfHbjWmU3smfBR4X2uxL698e7m5JZzxQ@mail.gmail.com>
	(Andrey Zhidenkov's message of "Thu, 6 Sep 2018 19:09:41 +0700")
Message-ID: <87worywseu.fsf@jsievers.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-CMAE-Envelope: 
 MS4wfCYu8MGN0undPw5MCcLEL5oktRMuAscV5gYnNemKD/OmKlB+0x3ga10YLZgYAdbJKfmRdWwKrBeXtzjXsTRvnaMnurcjUGOpafIZGWJ+CoTU+574j7zq
 XZ+nz0mV+TKGFP+3nFVns524dX6Mtp7VNVef/Yb1zDgW32Dy6GY7Jv6l03Zw+Blq7q0dgpF70I23E3goz6qwFxHnRNMjtkMVf2putiUSfq72kbjJs1TORinD
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk

Andrey Zhidenkov <andrey.zhidenkov@gmail.com> writes:

> Is there a way to detect which query (or queries) on hot standby holds
> streaming replication? Looked into the sources and found out that
> function ResolveRecoveryConflictWithVirtualXIDs do that job but have
> no idea how to do that with SQL.

The pg_locks view on your standby should reveal something.

>
> --
> With best regards, Andrey Zhidenkov.
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800