From: Rui DeSousa <rui@crazybean.net>
To: Victor Sudakov <vas@sibptus.ru>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Blocking queries on a hot standby?
Date: Sun, 17 Sep 2023 23:41:31 -0400
Message-ID: <F1A25698-F203-48CB-ABED-3480FEF3A27F@crazybean.net> (raw)
In-Reply-To: <ZQPdzIoDQFQwnlQL@admin.sibptus.ru>
References: <ZQPdzIoDQFQwnlQL@admin.sibptus.ru>
> 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=# select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t
prod=# 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 |
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, vas@sibptus.ru, pgsql-admin@lists.postgresql.org
Subject: Re: Blocking queries on a hot standby?
In-Reply-To: <F1A25698-F203-48CB-ABED-3480FEF3A27F@crazybean.net>
* 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