From: abrahim abrahao <a_abrahao@yahoo.com.br>
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: vacuum freeze wait_event BufferPin
Date: Wed, 7 Feb 2024 19:59:32 +0000 (UTC)
Message-ID: <299985666.43419.1707335972964@mail.yahoo.com> (raw)
In-Reply-To: <CAKAnmmKYFFt4J3-bV5x6kp0Qa0MeexbtJua9rXSyvp-credE4g@mail.gmail.com>
References: <1059371874.2807306.1706727919318.ref@mail.yahoo.com>
<1059371874.2807306.1706727919318@mail.yahoo.com>
<CAKAnmmLrGZTEURQwEd2HvuKg4u3X1FuXNvugMP7fAuMVweSTAw@mail.gmail.com>
<1866957898.3683025.1706905527172@mail.yahoo.com>
<CAKAnmmKYFFt4J3-bV5x6kp0Qa0MeexbtJua9rXSyvp-credE4g@mail.gmail.com>
Thanks again Greg, I really appreciated all information.
On Friday, February 2, 2024 at 08:16:41 p.m. EST, Greg Sabino Mullane <htamfids@gmail.com> wrote:
On Fri, Feb 2, 2024 at 3:25 PM abrahim abrahao <a_abrahao@yahoo.com.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...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: a_abrahao@yahoo.com.br, htamfids@gmail.com
Subject: Re: vacuum freeze wait_event BufferPin
In-Reply-To: <299985666.43419.1707335972964@mail.yahoo.com>
References: <1059371874.2807306.1706727919318.ref@mail.yahoo.com> <1059371874.2807306.1706727919318@mail.yahoo.com> <CAKAnmmLrGZTEURQwEd2HvuKg4u3X1FuXNvugMP7fAuMVweSTAw@mail.gmail.com> <1866957898.3683025.1706905527172@mail.yahoo.com> <CAKAnmmKYFFt4J3-bV5x6kp0Qa0MeexbtJua9rXSyvp-credE4g@mail.gmail.com> <299985666.43419.1707335972964@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox