Hi Sergei,
Thanks for the report and root cause analysis.
Attached is a patch adding a vacuum_delay_point() call at the top of
hashbulkdelete()'s per-bucket loop, before any buffer lock is taken.
The function currently has no interrupt check outside the one inside
hashbucketcleanup(), which is ineffective for the reason you already
found (InterruptHoldoffCount stays > 0 for the whole bucket once
LockBufferForCleanup() is called). This adds a check at the one spot
where nothing is locked yet, so a pending shutdown or cancel is
noticed at the next bucket boundary instead of only after the whole
index scan finishes.
No automated test: both patched and unpatched code eventually honor
the cancel, so a TAP test would need a hardcoded time threshold,
which risks flaking on slower CI hosts. Verified manually instead
(script attached): on an 8M-row table with ~90% dead tuples, cancelling
a VACUUM during the "vacuuming indexes" phase took ~2.08s on unpatched
master vs ~0.014s with the patch.
Not addressed: a single bucket with a very long overflow chain.
hashbucketcleanup() uses lock chaining (locks the next overflow page
before releasing the current one) to prevent a race with concurrent
scans overtaking a partially vacuumed bucket, per the hash AM README.
So interrupts are never truly clear during one bucket's own cleanup,
and this patch can't help there without changing the locking scheme.
I'd treat that as a separate, riskier follow-up.
Likely a backpatch candidate (real bug in shipped versions), but
I'll leave that call to whoever reviews this.
Regards,
Mostafa
The following bug has been logged on the website:
Bug reference: 19628
Logged by: Sergei Kornilov
Email address: sk@zsrv.org
PostgreSQL version: 16.14
Operating system: ubuntu
Description:
Hello!
During the installation of minor update (from 16.14 to 16.15), I discovered
that PostgreSQL was shutting down for a long time (about 6 minutes) because
it was waiting for the autovacuum process to terminate.
I was able to find the cause in the vacuum implementation for hash indexes
(this database has a fairly big hash index):
1) vacuum executes hashbulkdelete ( src/backend/access/hash/hash.c )
2) LockBufferForCleanup in loop_top calls HOLD_INTERRUPTS() under the hood
3) then we execute hashbucketcleanup , which can take a long time. Here, the
loop calls vacuum_delay_point (which calls CHECK_FOR_INTERRUPTS), but it
does nothing because HOLD_INTERRUPTS is still in effect.
4) Only at the end of hashbucketcleanup RESUME_INTERRUPTS is called from
LWLockRelease
I checked with gdb: hashbucketcleanup is actually executed with a non-zero
InterruptHoldoffCount.
Breakpoint 1, hashbucketcleanup (rel=rel@entry=0x7f1cf95ec080,
cur_bucket=cur_bucket@entry=0,
bucket_buf=bucket_buf@entry=11847, bucket_blkno=bucket_blkno@entry=1,
bstrategy=0x55f74ae62ca8, maxbucket=32573,
highmask=32767, lowmask=16383, tuples_removed=0x7fff95dc5a98,
num_index_tuples=0x7fff95dc5a90, split_cleanup=false,
callback=0x55f7395a6fa8 <vac_tid_reaped>, callback_state=0x7f1ce9b6d048)
at hash.c:691
691 {
(gdb) n
700 if (split_cleanup)
(gdb) n
717 vacuum_delay_point();
(gdb) p InterruptHoldoffCount
$1 = 1
I'm not sure how to properly fix hashbucketcleanup. At the very least, an
additional vacuum_delay_point in hashbulkdelete before LockBufferForCleanup
would improve the situation slightly.
regards, Sergei
-- Mostafa Nabil Software Engineer
