Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qi58w-0058gE-8c for pgsql-admin@arkaria.postgresql.org; Mon, 18 Sep 2023 03:41:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qi58u-00ESn5-84 for pgsql-admin@arkaria.postgresql.org; Mon, 18 Sep 2023 03:41:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qi58t-00ESme-Pg for pgsql-admin@lists.postgresql.org; Mon, 18 Sep 2023 03:41:47 +0000 Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26] helo=impout002.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qi58j-005oqL-0z for pgsql-admin@lists.postgresql.org; Mon, 18 Sep 2023 03:41:45 +0000 Received: from cerberus.crazybean.net ([68.116.174.119]) by cmsmtp with ESMTP id i58fqeQ3DEPSpi58fqiat2; Mon, 18 Sep 2023 03:41:34 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.4 cv=NvDCzuRJ c=1 sm=1 tr=0 ts=6507c6ee a=Cf+DCfj9UYuaXH9fDMmlHQ==:117 a=Cf+DCfj9UYuaXH9fDMmlHQ==:17 a=xqWC_Br6kY4A:10 a=zNV7Rl7Rt7sA:10 a=cbhV4p7HAAAA:8 a=4kdzh0xoXMcEspAluFsA:9 a=CjuIK1q_8ugA:10 a=IEfQGrb3VBexRzpZ:21 a=_W_S_7VecoQA:10 a=lqcHg5cX4UMA:10 a=z-V2QqvRhG52jeeRkh-B:22 Received: from smtpclient.apple ( [2001:470:e117:8:81df:6356:b769:e321]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 5314ea2d (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Sun, 17 Sep 2023 23:41:32 -0400 (EDT) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_151BD729-6F6F-4287-80AB-BAF8D1CF330D" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.4\)) Subject: Re: Blocking queries on a hot standby? Date: Sun, 17 Sep 2023 23:41:31 -0400 In-Reply-To: Cc: Pgsql-admin To: Victor Sudakov References: X-Mailer: Apple Mail (2.3696.120.41.1.4) X-CMAE-Envelope: MS4xfGqqVhJR0yBSY/TkiblxvdfMHCzJOpkKfI3WMozF+l6mp4huN7sKPHvthfrqvefH59YocLEdlnzv62EivxytTfVNsSTKa5+AcL9toj6cmBXkG9/RWEA7 hWFyd0BvKEdCpqNtl7cBr2ZZacshqLqEWDWc3p0mpgJOqXl9qoF7a2rVfQtKs1SINKmyL+6FZADX2NdmNOeUGuGnKTqSUtNYhS8IP0cU3I+Xou54qSLpfQV4 V37qNYB2VzNbO9VtuPk9Ww== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_151BD729-6F6F-4287-80AB-BAF8D1CF330D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Sep 15, 2023, at 12:30 AM, Victor Sudakov wrote: >=20 > Can you imagine, at least theoretically, any kind of query on a > physical replica (hot standby) which would block other queries on the > same replica? The only thing that comes to mine is advisory locks and also queries can = block replication replay. prod=3D# select pg_is_in_recovery(); -[ RECORD 1 ]-----+-- pg_is_in_recovery | t prod=3D# select * from blocked_session; -[ RECORD 1 ]---+----------------------------- pid | 39330 tree | 39330 root | 39330 level | 0 user | rui state | idle state_duration | 00:01:01.663597 wait_event_type | Client wait_event | ClientRead lockmode | ExclusiveLock locktype | advisory granted | t lock_wait |=20 blocked | 1 query | select pg_advisory_lock(42); objects |=20 -[ RECORD 2 ]---+----------------------------- pid | 39327 tree | 39330.39327 root | 39330 level | 1 user | rui state | active state_duration | 00:00:51.407894 wait_event_type | Lock wait_event | advisory lockmode | ExclusiveLock locktype | advisory granted | f lock_wait | 00:00:51.407746 blocked | 0 query | select pg_advisory_lock(42); objects |=20 --Apple-Mail=_151BD729-6F6F-4287-80AB-BAF8D1CF330D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

On Sep 15, 2023, at 12:30 AM, Victor Sudakov <vas@sibptus.ru> = wrote:

Can you = imagine, at least theoretically, any kind of query on a
physical replica (hot standby) which would block other = queries on the
same replica?

The only thing that comes to mine is = advisory locks and also queries can block replication replay.

prod=3D# = select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

prod=3D# select * from = blocked_session;
-[ = RECORD 1 ]---+-----------------------------
pid           =   | 39330
tree            | = 39330
root            | = 39330
level =           | 0
user            | = rui
state =           | idle
state_duration  | = 00:01:01.663597
wait_event_type | Client
wait_event      | ClientRead
lockmode        | = ExclusiveLock
locktype        | = advisory
granted =         | t
lock_wait       | 
blocked         | = 1
query =           | select = pg_advisory_lock(42);
objects         | 
-[ RECORD 2 = ]---+-----------------------------
pid             | = 39327
tree            | = 39330.39327
root            | = 39330
level =           | 1
user            | = rui
state =           | active
state_duration  | = 00:00:51.407894
wait_event_type | Lock
wait_event      | advisory
lockmode        | = ExclusiveLock
locktype        | = advisory
granted =         | f
lock_wait       | = 00:00:51.407746
blocked =         | 0
query           | select = pg_advisory_lock(42);
objects         = | 

= --Apple-Mail=_151BD729-6F6F-4287-80AB-BAF8D1CF330D--