Re: How about a proper TEMPORARY TABLESPACE? - Mailing list pgsql-hackers

* Matheus de Oliveira (matioli.matheus@gmail.com) wrote:
> Hi Hackers,

Having read only the subject- +1 from me on the idea.  Maybe +1000.

> 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.

Right.

> So, what you guys think about letting PG know somehow that a tablespace is
> temporary?

PG would need to enforce that it's only used for temporary objects as
well, of course..  Or at least, that was my thinking on this.

> 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:
>
>     CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ...
>
> 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.

Not sure about that specific syntax (don't we have SET options now?) but
I do like the general idea.

> 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.

Yeah, +1 here and it should go into an appropriate commitfest to get a
proper review.  This would be *really* nice to have.
Thanks,
    Stephen