Re: BUG #19622: io_method=worker retains file descriptors on dropped relations - Mailing list pgsql-bugs

Hi Alexandre,

Thank you for investigating this issue, preparing the patch, and
submitting it to the CommitFest. I really appreciate the time and
effort you put into this.

Best regards,
SeongHan

2026년 9월 21일 (월) 오전 5:36, Alexandre Felipe <o.alexandre.felipe@gmail.com>님이 작성:
>
>
>
>
> Hi SeongHan,
>
> I submitted a patch fixing it:
> https://commitfest.postgresql.org/patch/7322/
>
>
> Thank you for the extensive explanation
>
> On Thu, Sep 17, 2026 at 8:37 PM PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> PostgreSQL version: 18.6
>
>
> It seems unlikely that this will be backpatched.
>
>>
>> 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.
>
> Regards,
> Alexandre