locks within select

On Monday, December 13, 2021, Marc Millas <marc.millas@mokadb.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.

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.g.johnston@gmail.com> wrote:

On Tuesday, December 14, 2021, Marc Millas <marc.millas@mokadb.com> wrote:

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

The transactions are present on the secondary. It just realizes that
applying them may cause problems (think drop table or truncate) and so it
chooses not to until local queries have completed. Since WAL is strictly
linear all sessions are affected. There is no locking needed, or IIUC even
possible, and MVCC is not applicable as it only concerns itself with the
behavior lf the writable primary system.

I suspect there are better attempts at describing this than my
half-conscious effort here, though I’ve captured the crux of it - streaming
WAL is its own unique thing.

David J.

On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote:

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...

Perhaps this can explain some of what is going on:

https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Excellent !
thanks a lot, it was exactly what I was looking for.

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, Dec 14, 2021 at 6:14 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote: