DiskManager's temp files can be deleted too early

[alamb]

Describe the bug

After #7510, we saw intermittent errors in a test reading spill files: #7537 and #7523

Some great debugging by @sarutak and @viirya (see description on #7538 (comment)) found that the NamedTempFiles returned by DiskManager::create_tmp_file can be deleted too soon, if the DiskManager is dropped while the plan is still outstanding

I don't think this is likely to be a problem in practice as users will likely have a SessionContext or TaskContext that outlives the plan execution.

To Reproduce

Run a plan and drop the disk manager

Expected behavior

It should not be possible to have the NamedtempFiles deleted out from under a running plan.

There are a bunch of various proposals on #7538

Additional context

No response