Re: locks within select

I did read this, before asking my question...

but... when you do setup a streaming replication, there is, in
postgresql.conf, a variable:
max_standby_streaming_delay = 30s # max delay before canceling queries when
reading streaming WAL;

as the secondary is, by nature, read only, I was wondering why it can be
necessary to cancel a read request...
So, I read another time some locking doc, which states that the access
share lock positioned by a read request DO block demands of exclusive
locking
like vacuum full, table reorg, etc. thus blocking the streaming replication
flow.

also, on the real world POV, while monitoring streaming replication lag, I
see lags of 90 seconds, quite frequently.
and I was guessing what can block the streaming flow for that long.
--neither cpu nor network nor I/O looks saturated
--as its a production instance, and we dont do vacuum full or table reorg
or... during business hours, obviously !
--but a bunch of read request are sent to the secondary, some of them a bit
big in terms of multiple join, etc..

hence my original question.

So, if its not a locking scheme, what can block hundreds of transaction to
get to the secondary ???

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, Dec 14, 2021 at 7:29 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Monday, December 13, 2021, Marc Millas <marc(dot)millas(at)mokadb(dot)com> wrote:
>>
>> I was wondering if for example, within a join, some kind of lock may be
>> acquired so that the dataset concerned is not changed during execution ?
>> (for example a delete then autovacuum ??)
>>
>
> Read this primer on MVCC from the docs:
>
> https://www.postgresql.org/docs/current/mvcc-intro.html
>
> David J.
>
>