Re: Understanding recovery conflict due to bufferpin

Thanks for the clarification.

On Wed, Mar 24, 2021, 03:12 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2021-03-23 at 22:48 +0530, vinay kumar wrote:
> > 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.
>
> A pin is not a lock, it just protects a page from being swapped out of
> cache.
> Several backends can pin the same page simultaneously.
>
> The buffer pin is an internal PostgreSLQ concept and has nothing to do
> with the operating system.
>
> I recommend that you start by reading the appropriate README:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/buffer/README
> That explains the concepts better than I could.
>
> The key sentence when it comes to replication conflicts is this:
>
> To physically remove a tuple or compact free space on a page, one
> must hold a pin and an exclusive lock, *and* observe while holding the
> exclusive lock that the buffer's shared reference count is one (ie,
> no other backend holds a pin).
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>