Thanks again Greg, I really appreciated all information.
On Friday, February 2, 2024 at 08:16:41 p.m. EST, Greg Sabino Mullane <htamfids@xxxxxxxxx> wrote:
On Fri, Feb 2, 2024 at 3:25 PM abrahim abrahao <a_abrahao@xxxxxxxxxxxx> 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
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;
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
