| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Fred Habash <fmhabash(at)gmail(dot)com> |
| Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Autovacuum Hung Due to Bufferpin |
| Date: | 2023-01-11 15:32:10 |
| Message-ID: | 493122.1673451130@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Fred Habash <fmhabash(at)gmail(dot)com> writes:
> pg_locks shows no blockers while this is happening. This view shows a
> constant 13 sessions running SELECT statements on this table posting
> AccessShareLock. Of course, these is also the AV sessions
> with ShareUpdateExclusiveLock
> ...
> Why is AV blocked by bufferpin given the fact that this table does not get
> an DML changes. It is purely read only. What can be done to resolve this?
Apparently there has been some DML on it in the past, leaving dead rows
that vacuum now needs to clean up --- but it needs a transient buffer
lock for long enough to do that. If you have a constant stream of readers
it will never be able to get that lock. You'll need to find a way to
momentarily block those readers.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fred Habash | 2023-01-11 17:46:59 | Re: Autovacuum Hung Due to Bufferpin |
| Previous Message | Fred Habash | 2023-01-11 13:59:38 | Autovacuum Hung Due to Bufferpin |