ENH: support SpooledTemporaryFile
- [x] closes #44748
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit) for how to run them
- [x] whatsnew entry

A trimmed down version of this PR could go to 1.3.* as it technically fixes a regression. Let me know whether it should be back-ported or not, based on that I will create a whatsnew for either 1.3 or 1.4. let's just put in 1.4, trying to avoid backporting anything unless its critical Thank you for rebasing @jreback, green except for (unrelated?) codecov error. thanks @twoertwein this looks great (yeah a bit longer but way more readable). waiting to merge as i hope some CI patches get us back to green and then can rebase here. are the windows checks related? It should be unrelated (the test case doesn't seem to open any files). Will rebase and force push to test it again. Cannot reproduce it on linux. The error disappeared but I can imagine that it might be related: if mmap fails, opened handles will not be released.

edit: not from this PR but as a result from https://github.com/pandas-dev/pandas/pull/44777 thanks @twoertwein 