Re: OK to put temp tablespace on volatile storage or to omit it from backups?

On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ian Lawrence Barwick <barwick(at)gmail(dot)com> writes:
>> 2013/5/1 Yang Zhang <yanghatespam(at)gmail(dot)com>:
>>> That is unfortunate. Good thing I asked, I guess. Do you have a
>>> pointer to said blog post?
>
>> I think this is the post in question:
>> http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/
>
> Appears to be sheer blather, or at least not tempered by any thoughts
> of whether it'd work in special cases. The main reality underlying it,
> I think, is that WAL replay will complain if files are missing. But
> there will be no WAL log entries for temp tables.
>
> The main concern I'd have about Yang's idea is that just because *he*
> thinks a tablespace is "temp" doesn't mean the system knows it is,
> so there would be no protection against accidentally creating a regular
> table there; whereupon he's at risk of replay failures.

So this is interesting: if it's OK to put the temp tablespace on
volatile storage, is it OK to put indexes for non-temp tables into the
same temp tablespace (and everything works)?

>
> Having said that, there's no substitute for testing ;-). I wouldn't be
> surprised for instance if the DB won't restart until you create the
> tablespace directories, and maybe even PG_VERSION files therein. But it
> really shouldn't have an issue with the files underlying a temp table
> not being there anymore; at worst you'd get some bleats in the log.

Do you know what exactly I would need to create in place for this to work out?

This isn't exactly the same test as what I should be running (pulling
the cord), but I just tried:

create tablespace ephemeral location '/mnt/eph0/pgtmp';

Then reloading PG with temp_tablespaces = 'ephemeral' in postgresql.conf.

At this point I (cleanly) stop PG, ran rm -rf /mnt/eph0/pgtmp/,
started PG, and ran:

create temp table foo (a int);

which failed with:

ERROR: could not create directory
"pg_tblspc/16384/PG_9.1_201105231/11919": No such file or directory

Once I did

mkdir -p /mnt/eph0/pgtmp/PG_9.1_201105231/11919

everything seems to be back to normal.

Is this the extent of what I can expect, *always*, even if I had run
the proper experiment involving pulling the cord (or at least kill
-9)?