I was facing a situation were we wanted to set temp_tablespaces to a tablespace on a ephemeral disk (yes, it is AWS ephemeral disk), and I know many users have faced the same situation. Although it seems safe to create a tablespace on ephemeral disks if you use it to store only temporary files (either created by queries or temp tables), PostgreSQL does not have such information, and can't safely prevent a careless user of creating a non-temporary relation on this tablespace.
Also, in case of a lost of this tablespace, its PG_TEMP_FILES_DIR should be created by hand after recovering. That is fine actually, but many users may not even noticed that.
So, what you guys think about letting PG know somehow that a tablespace is temporary?
I have took some small time to make a PoC just to see if that is doable. And so I did a new syntax like:
So, if TEMP or TEMPORARY is present, I mark a new column at pg_tablespace as true. On every table creation or moving to a new tablespace, I just check this, and fails if the tablespace is "temporary" but the "relpersistence" says the table is not.
The other part is, every time some query or relation is asked to be stored on this tablespace, I create (on-demand) the PG_TEMP_FILES_DIR inside of it (also if it is temporary).
The attached patch (and also on my Github, [1]), shows the PoC. For now, I'm not worried about the code quality, there are yet a lot of work to be done there (like ALTER TABLESPACE, better testing, use relcache, etc...), and it is my first hacking on PG (so I'm a newbie). But I'd like to hear from you guys if such feature is desirable and if I could starting working on that for real. Also some thoughts about better way of implementing it.
