Re: Blocking queries on a hot standby?

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Sep 15, 2023, at 12:30 AM, Victor Sudakov <vas@xxxxxxxxxx> 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         |