Operating system: Debian Linux aarch64 (official postgres:18 Docker) Description:
SUMMARY =======
With io_method=worker (the default in PostgreSQL 18), AIO I/O worker processes retain open file descriptors on relation files that have been dropped. After the files are unlinked -- for the first segment this happens at the first checkpoint after DROP -- /proc/<pid>/fd shows io workers still holding descriptors on them:
...
Regular backends do not accumulate them: they receive SMGR invalidation via the shared invalidation queue and call smgrclose(). io workers are auxiliary processes and never register as invalidation receivers, so that message never reaches them.
This is the answer.
...
If a regression test for this is wanted, the pieces are mostly there -- pg_stat_activity gives the io worker pids and the injection point above already hooks the reopen path -- but a portable assertion would probably need the worker's open-segment count exposed from inside the backend rather than read from /proc. We are happy to help with that if it is the direction people want.
I don't think we need a test for that at this stage, given that it is not an observable behaviour. If we are to add a test case it would likely be along the lines of enabling debug verbosity, and having a debug message showing that the file was removed.
POSSIBLE FIX DIRECTIONS (for discussion) ========================================
1. Have io workers call smgrcloseall() (or smgrreleaseall()) when they go idle, or on some bounded schedule -- the direct analogue of the bgwriter's checkpoint-time smgrcloseall(). Cheap and simple, at the cost of some reopens after an idle period.
2. Extend PROCSIGNAL_BARRIER_SMGRRELEASE, or add a narrower relation- scoped equivalent, so that relation drops also reach auxiliary processes. io workers already handle the barrier, so the delivery machinery exists; the question is the cost of a barrier per DROP.
3. Have io workers participate in shared invalidation. This is the most precise fix but the largest change, since it means giving an auxiliary process an invalidation slot and catchup handling.
4. Have smgr_aio_reopen() close the descriptor after the IO completes. Correct but presumably too expensive, as it reopens on every IO.
We do not have a strong opinion on which is right; (1) looks like the smallest change that removes the unbounded behavior. We are happy to test any patch against both the reproducer and the production workload.
I implemented (3) the most precise, not sure if the largest.
I implemented this, the most precise, not too large src/backend/storage/aio/method_worker.c | 31 +++++++++++++++++++++++++ 1 file changed, 31 insertions(+)
REPRODUCER SCRIPT
I adapted your script to run faster 128kB shared buffers and a sub 1MB table is good enough to show the issue.
