Re: vacuum freeze wait_event BufferPin

On Fri, Feb 2, 2024 at 3:25 PM abrahim abrahao <a_abrahao(at)yahoo(dot)com(dot)br>
wrote:

> SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) should not be blocked
> by ACCESS SHARE (AccessShareLock). Am I wrong about it? If I am not wrong
> why it still locking it?
>

Those locks with no other context are somewhat of a red herring. The
important part is not that the AccessShare is somehow blocking
ShareUpdateExclusive, but that the ShareUpdateExclusive process is NOT
blocking new AccessShare processes! In the internals of postgres,
vacuumlazy.c tries to grab a buffer lock (different concept from all the
user-visible locks above). It politely tries to wait[1] until nobody else
is grabbing it (aka pinning it), then proceeds. The problem is that other
processes are allowed to come along and put a pin in it as well - the
vacuum's shareupdateexclusive lock does not prevent that.

So the timeline is:

Process X runs a long select and pins the buffer
Process V runs a vacuum freeze and tries to lock the buffer. It detects
other pins, so it waits. It assumes that whoever is holding the pin will
release it someday.
Process Y runs another long select and also pins the buffer.
Process X ends, and removes its pins.
Process V still cannot move - it just knows there are still pins. Where
they come from does not matter.

As long as there is at least one other process holding a pin, the vacuum
freeze cannot continue[2].

That's my understanding of the code, anyway. This could be argued as a bug.
I am not sure what a solution would be. Cancelling user queries just for a
vacuum would not be cool, but we could maybe pause future pin-creating
actions somehow?

For the time being, forcing a super-quick moment of no table access would
seem to be your best bet, as described earlier.

Cheers,
Greg

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/vacuumlazy.c;hb=HEAD#l975
See backend/storage/buffer/bufmgr.c for LockBufferForCleanup()

[2] Quick duplication script:
drop table if exists foobar;
create table foobar as select 1 as id;
alter table foobar set (autovacuum_enabled = off);
update foobar set id = id;

Process 1:
begin; select *, pg_sleep(111111) from foobar;

Process 2:
vacuum(freeze,verbose) foobar; /* blocked */

Process 3:
begin; select *, pg_sleep(333333) from foobar;

Run in order. Kill Process 1 and Process 2 is still blocked. Kill Process 3
and Process 2 finished the vacuum.
Note that a regular vacuum (without a freeze) will not get blocked.

Cheers,
Greg