On Mon, Dec 5, 2022 at 7:24 PM Vahur Sinijärv <vahursi@icloud.com> wrote:
> Where are these files normally created and what names do they have? We may still have them since last crash.
Something like base/pgsql_tmp/pgsql_tmp21949.19 where 21949 is a pid
(print MyProcPid in your core), and 19 is a number that keeps going
up. It may be hard to figure out which one it is though because there
should be some power of 2 number of logical spill files for a single
hash join, and then if any file exceeded 1GB it would spill into
another file (for historical reasons, 90s computers couldn't all do
large files) with hard to predict suffix, and it'd be hard to see
which ones go together. Hopefully your files didn't exceed 1GB so
hopefully you'll see a set of files all < 1GB created around the same
time, so none of them are 'continuations' that are hard to stitch
together. Then you could start from the beginning of each file and
walk tuple by tuple to see if you find the {0,0} header.
Those files get cleaned up when you restart, though.