Re: Understanding recovery conflict due to bufferpin

Hi Laurenz,

Thank you for replying back to me.

My query is does the buffer pin pins block present in shared
buffers(assuming the block is already read into memory previously) or it's
pinned to the block at the OS level?

Also have another query:

Do we have any queue mechanism to attain buffer pins on a block?

To provide an example:

Let's say, I need to read tuples present in a block by multiple backends.
Shouldn't the backends wait in FIFO order to hold pins on the block?

To give you an example, if I have the few backends requesting access to
a block in the following order:

-> backend 1: reads the tuples from block
-> backend 2: reads the tuples from block
-> backend 3: reads the tuples from block
-> WAL replay: waiting to modify block either due to replaying change or
Vacuum operation.

Will the order of requests to access blocks be maintained in a cache or any
other memory area?

If possible to implement lazy cache invalidation (invalidating blocks in
buffer cache when no conflicts query is run), it would be great and helpful
to lots of users who query data from standby to avoid recovery conflict and
don't have to re-run the entire query once again consuming resources.

Thanks

On Wed, Mar 17, 2021 at 6:11 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2021-03-17 at 10:14 +0530, vinay kumar wrote:
> > Need some understanding regarding recovery conflict due to buffer pin.
> >
> > We see "User was holding shared buffer pin for too long" .
> >
> > I understand that this can be due to holding lock to update content of a
> db block or page.
> >
> > 1> Is the lock held on a buffer page or page at disk level?
>
> A buffer page. And it is not a lock, it is a pin
>
> > 2> Is there any serialization present to maintain the locking mechanism?
>
> I don't quite understand - what you observe *is* the serialization
> of access.
>
> See this blog for some details:
>
> https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
>
> Yours,
> Laurenz Albe
>
>