Thread: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

[HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
Right now we don't document that temp_tablespaces can use
non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe? 
Should we document this?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Claudio Freire
Date:
On Mon, May 29, 2017 at 3:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Right now we don't document that temp_tablespaces can use
> non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
> Should we document this?

I have set up things like that, but it's nontrivial.

Just pointing the tablespace to non'restart'safe storage will get you
an installation that fails to boot after a restart, since there's a
tree structure that is expected to survive, and when it's not found,
postgres just fails to boot.

Or just use the tablespaces, I forget which. But in essence, it won't
do to do just that. What I ended up doing was backing up the empty
tablespace and adding a restore to system bootup scripts, so that
after a restart postgres finds an empty tablespace there.

So, there's a lot of internal hackery to document if you'd like to
document that kind of usage.



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Alvaro Herrera
Date:
Claudio Freire wrote:
> On Mon, May 29, 2017 at 3:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Right now we don't document that temp_tablespaces can use
> > non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
> > Should we document this?
> 
> I have set up things like that, but it's nontrivial.

I think it'd be smart to support the use case directly, because there's
interest in it being actually supported (unlike the statu quo).
Something like restoring the tablespace to the empty state on boot, if
it's known to need it.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Andres Freund
Date:

On May 29, 2017 12:15:37 PM PDT, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>Claudio Freire wrote:
>> On Mon, May 29, 2017 at 3:53 PM, Bruce Momjian <bruce@momjian.us>
>wrote:
>> > Right now we don't document that temp_tablespaces can use
>> > non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
>> > Should we document this?
>>
>> I have set up things like that, but it's nontrivial.
>
>I think it'd be smart to support the use case directly, because there's
>interest in it being actually supported (unlike the statu quo).
>Something like restoring the tablespace to the empty state on boot, if
>it's known to need it.

Has the danger of making recovery harder after a restart where somebody forgot to mount some subdirectory ...

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Tom Lane
Date:
Andres Freund <andres@anarazel.de> writes:
> On May 29, 2017 12:15:37 PM PDT, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> I think it'd be smart to support the use case directly, because there's
>> interest in it being actually supported (unlike the statu quo).
>> Something like restoring the tablespace to the empty state on boot, if
>> it's known to need it.

> Has the danger of making recovery harder after a restart where somebody forgot to mount some subdirectory ...

Or even worse, the mount happens after PG starts (and creates directories
on the root volume, not knowing they should go onto the mount instead).

I'm too lazy to search the archives right now, but there was some case
years ago where somebody destroyed their database via an ill-thought-out
combination of automatic-initdb-if-$PGDATA-isn't-there and slow mounting.
We'd have to be very sure that any auto-directory-creation behavior didn't
have a potential for that.  Perhaps doing it only for temp tablespaces
alleviates some of the risk, but it still seems pretty scary.
        regards, tom lane



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Christoph Berg
Date:
Re: Tom Lane 2017-05-29 <28291.1496087708@sss.pgh.pa.us>
> Andres Freund <andres@anarazel.de> writes:
> > On May 29, 2017 12:15:37 PM PDT, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >> I think it'd be smart to support the use case directly, because there's
> >> interest in it being actually supported (unlike the statu quo).
> >> Something like restoring the tablespace to the empty state on boot, if
> >> it's known to need it.
> 
> > Has the danger of making recovery harder after a restart where somebody forgot to mount some subdirectory ...
> 
> Or even worse, the mount happens after PG starts (and creates directories
> on the root volume, not knowing they should go onto the mount instead).
> 
> I'm too lazy to search the archives right now, but there was some case
> years ago where somebody destroyed their database via an ill-thought-out
> combination of automatic-initdb-if-$PGDATA-isn't-there and slow mounting.
> We'd have to be very sure that any auto-directory-creation behavior didn't
> have a potential for that.  Perhaps doing it only for temp tablespaces
> alleviates some of the risk, but it still seems pretty scary.

stats_temp_directory has the same problem. In that case, the risk of
breaking something by calling mkdir() instead of aborting startup
seems pretty low to me.

Christoph



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Mon, May 29, 2017 at 03:55:08PM -0400, Tom Lane wrote:
> I'm too lazy to search the archives right now, but there was some case
> years ago where somebody destroyed their database via an ill-thought-out
> combination of automatic-initdb-if-$PGDATA-isn't-there and slow mounting.
> We'd have to be very sure that any auto-directory-creation behavior didn't
> have a potential for that.  Perhaps doing it only for temp tablespaces
> alleviates some of the risk, but it still seems pretty scary.

Here is the Joe Conway report from 2004:
https://postgr.es/m/41BFAB7C.5040108@joeconway.com

and later references to the report from Joe Conway (2005):
https://postgr.es/m/425ABB17.305%40joeconway.com

and you (2007):
https://postgr.es/m/18897.1197775682%40sss.pgh.pa.us

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> On Mon, May 29, 2017 at 03:55:08PM -0400, Tom Lane wrote:
>> I'm too lazy to search the archives right now, but there was some case
>> years ago where somebody destroyed their database via an ill-thought-out
>> combination of automatic-initdb-if-$PGDATA-isn't-there and slow mounting.

> Here is the Joe Conway report from 2004:
>     https://postgr.es/m/41BFAB7C.5040108@joeconway.com

Yeah, that's the thread I was remembering.  Thanks for looking it up.
        regards, tom lane



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Joe Conway
Date:
On 05/30/2017 10:54 AM, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> On Mon, May 29, 2017 at 03:55:08PM -0400, Tom Lane wrote:
>>> I'm too lazy to search the archives right now, but there was some case
>>> years ago where somebody destroyed their database via an ill-thought-out
>>> combination of automatic-initdb-if-$PGDATA-isn't-there and slow mounting.
>
>> Here is the Joe Conway report from 2004:
>>     https://postgr.es/m/41BFAB7C.5040108@joeconway.com
>
> Yeah, that's the thread I was remembering.  Thanks for looking it up.

Wow, reading that was a blast from the past!

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Mark Dilger
Date:
> On May 29, 2017, at 11:53 AM, Bruce Momjian <bruce@momjian.us> wrote:
> 
> Right now we don't document that temp_tablespaces can use
> non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe? 
> Should we document this?

The only safe way to do temporary tablespaces that I have found is to extend
the grammar to allow CREATE TEMPORARY TABLESPACE, and then refuse
to allow the creation of any non-tempoary table (or index on same) in that
tablespace.  Otherwise, it is too easy to be surprised to discover that your
table contents have gone missing.

If the project wanted to extend the grammar and behavior in this direction,
maybe temp_tablespaces would work that way?  I'm not so familiar with what
the temp_tablespaces GUC is for -- only ever implemented what I described
above.

Mark Dilger



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Robert Haas
Date:
On Tue, May 30, 2017 at 6:50 PM, Mark Dilger <hornschnorter@gmail.com> wrote:
>> On May 29, 2017, at 11:53 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> Right now we don't document that temp_tablespaces can use
>> non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
>> Should we document this?
>
> The only safe way to do temporary tablespaces that I have found is to extend
> the grammar to allow CREATE TEMPORARY TABLESPACE, and then refuse
> to allow the creation of any non-tempoary table (or index on same) in that
> tablespace.  Otherwise, it is too easy to be surprised to discover that your
> table contents have gone missing.

I think this would be a sensible approach.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Wed, May 31, 2017 at 07:53:53AM -0400, Robert Haas wrote:
> On Tue, May 30, 2017 at 6:50 PM, Mark Dilger <hornschnorter@gmail.com> wrote:
> >> On May 29, 2017, at 11:53 AM, Bruce Momjian <bruce@momjian.us> wrote:
> >> Right now we don't document that temp_tablespaces can use
> >> non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
> >> Should we document this?
> >
> > The only safe way to do temporary tablespaces that I have found is to extend
> > the grammar to allow CREATE TEMPORARY TABLESPACE, and then refuse
> > to allow the creation of any non-temporary table (or index on same) in that
> > tablespace.  Otherwise, it is too easy to be surprised to discover that your
> > table contents have gone missing.
> 
> I think this would be a sensible approach.

Just to clarify, the TEMPORARY clause would allow the tablespace to
start up empty, while normal tablespaces can't do that, right?  One big
problem is that we don't have any way to prevent non-temporary
tablespaces from being created on transient storage.  I wonder if we
should document this restriction, but it seems awkward to do.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Robert Haas
Date:
On Wed, May 31, 2017 at 9:04 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Just to clarify, the TEMPORARY clause would allow the tablespace to
> start up empty, while normal tablespaces can't do that, right?

Yeah.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Mark Dilger
Date:
> On May 31, 2017, at 6:04 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, May 31, 2017 at 07:53:53AM -0400, Robert Haas wrote:
>> On Tue, May 30, 2017 at 6:50 PM, Mark Dilger <hornschnorter@gmail.com> wrote:
>>>> On May 29, 2017, at 11:53 AM, Bruce Momjian <bruce@momjian.us> wrote:
>>>> Right now we don't document that temp_tablespaces can use
>>>> non-restart-safe storage, e.g. /tmp, ramdisks.  Would this be safe?
>>>> Should we document this?
>>>
>>> The only safe way to do temporary tablespaces that I have found is to extend
>>> the grammar to allow CREATE TEMPORARY TABLESPACE, and then refuse
>>> to allow the creation of any non-temporary table (or index on same) in that
>>> tablespace.  Otherwise, it is too easy to be surprised to discover that your
>>> table contents have gone missing.
>>
>> I think this would be a sensible approach.
>
> Just to clarify, the TEMPORARY clause would allow the tablespace to
> start up empty, while normal tablespaces can't do that, right?  One big
> problem is that we don't have any way to prevent non-temporary
> tablespaces from being created on transient storage.  I wonder if we
> should document this restriction, but it seems awkward to do.

It depends what you mean by allowing the tablespace to start up empty.
It must not be empty once users can run queries, since the catalogs will still
have entries for the tablespace and its dependent objects.  So, what must
happen is that during startup the tablespace and its temporary tables and
indexes get recreated if they are missing.

Mark Dilger


Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Wed, May 31, 2017 at 07:53:22AM -0700, Mark Dilger wrote:
> > Just to clarify, the TEMPORARY clause would allow the tablespace to
> > start up empty, while normal tablespaces can't do that, right?  One big
> > problem is that we don't have any way to prevent non-temporary
> > tablespaces from being created on transient storage.  I wonder if we
> > should document this restriction, but it seems awkward to do.
> 
> It depends what you mean by allowing the tablespace to start up empty.
> It must not be empty once users can run queries, since the catalogs will still
> have entries for the tablespace and its dependent objects.  So, what must
> happen is that during startup the tablespace and its temporary tables and
> indexes get recreated if they are missing.

Uh, I thought only the sessions that created the temporary objects could
see them, and since they are not in WAL and autovacuum can't see them,
their non-existence in a temporary tablespace would not be a problem.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Mark Dilger
Date:
> On May 31, 2017, at 7:58 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, May 31, 2017 at 07:53:22AM -0700, Mark Dilger wrote:
>>> Just to clarify, the TEMPORARY clause would allow the tablespace to
>>> start up empty, while normal tablespaces can't do that, right?  One big
>>> problem is that we don't have any way to prevent non-temporary
>>> tablespaces from being created on transient storage.  I wonder if we
>>> should document this restriction, but it seems awkward to do.
>>
>> It depends what you mean by allowing the tablespace to start up empty.
>> It must not be empty once users can run queries, since the catalogs will still
>> have entries for the tablespace and its dependent objects.  So, what must
>> happen is that during startup the tablespace and its temporary tables and
>> indexes get recreated if they are missing.
>
> Uh, I thought only the sessions that created the temporary objects could
> see them, and since they are not in WAL and autovacuum can't see them,
> their non-existence in a temporary tablespace would not be a problem.

You are correct.  I was thinking about an extension to allow unlogged
tablespaces on temporary filesystems, but got the words "unlogged" and
"temporary" mixed up in my thinking and in what I wrote.  I should have
written that unlogged tablespaces would only host unlogged tables and
unlogged indexes, such that users are not surprised to find their data
missing.

On reflection, I think both features are worthwhile, and not at all exclusive
of each other, though unlogged tablespaces is probably considerably more
work to implement.

Mark Dilger


Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Wed, May 31, 2017 at 08:28:51AM -0700, Mark Dilger wrote:
> > Uh, I thought only the sessions that created the temporary objects could
> > see them, and since they are not in WAL and autovacuum can't see them,
> > their non-existence in a temporary tablespace would not be a problem.
> 
> You are correct.  I was thinking about an extension to allow unlogged
> tablespaces on temporary filesystems, but got the words "unlogged" and
> "temporary" mixed up in my thinking and in what I wrote.  I should have
> written that unlogged tablespaces would only host unlogged tables and
> unlogged indexes, such that users are not surprised to find their data
> missing.
> 
> On reflection, I think both features are worthwhile, and not at all exclusive
> of each other, though unlogged tablespaces is probably considerably more
> work to implement.

TODO item added:
Allow tablespaces on RAM-based partitions for temporary objects 

and I wrote a blog entry about this:
https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Jerry Sievers
Date:
Bruce Momjian <bruce@momjian.us> writes:

> On Wed, May 31, 2017 at 08:28:51AM -0700, Mark Dilger wrote:
>
>> > Uh, I thought only the sessions that created the temporary objects could
>> > see them, and since they are not in WAL and autovacuum can't see them,
>> > their non-existence in a temporary tablespace would not be a problem.
>> 
>> You are correct.  I was thinking about an extension to allow unlogged
>> tablespaces on temporary filesystems, but got the words "unlogged" and
>> "temporary" mixed up in my thinking and in what I wrote.  I should have
>> written that unlogged tablespaces would only host unlogged tables and
>> unlogged indexes, such that users are not surprised to find their data
>> missing.

Very late to the discussion here...

At my site, we have an NVME disk as temp_tablespace.

It's used not only for disk-spilling temp operations.  There are temp
tables, UNLOGGEd persistent tables.

And you guessed it.  In spite of our warnings, users have created LOGGEd
tables in there.

This has made on a few ocasions our SAN snapshots, started on a
different system fail apparently due to something in the crash recovery
data trying to update pages on one of the real tables :-)

The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
is no attempt to copy the NVME device when the snaps are made.

There's an event trigger plus batch job now running tou avoid this risk.

We realize too that there are implications here if a backup is
instantiated and PITR is done.

Just FYI that there could be others running like this ignorant of the
potential gotchas.

>> 
>> On reflection, I think both features are worthwhile, and not at all exclusive
>> of each other, though unlogged tablespaces is probably considerably more
>> work to implement.
>
> TODO item added:
>
>     Allow tablespaces on RAM-based partitions for temporary objects 
>
> and I wrote a blog entry about this:
>
>     https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017
>
> -- 
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +                      Ancient Roman grave inscription +

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Mon, Jun  5, 2017 at 04:38:32PM -0500, Jerry Sievers wrote:
> The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
> is no attempt to copy the NVME device when the snaps are made.
> 
> There's an event trigger plus batch job now running tou avoid this risk.
> 
> We realize too that there are implications here if a backup is
> instantiated and PITR is done.
> 
> Just FYI that there could be others running like this ignorant of the
> potential gotchas.

Yes, if we implement the TODO you will create a TEMPORARY tablespace
that can't contain non-temporary and/or non-unlogged tables.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Andres Freund
Date:
On 2017-06-05 22:34:17 -0400, Bruce Momjian wrote:
> On Mon, Jun  5, 2017 at 04:38:32PM -0500, Jerry Sievers wrote:
> > The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
> > is no attempt to copy the NVME device when the snaps are made.
> > 
> > There's an event trigger plus batch job now running tou avoid this risk.
> > 
> > We realize too that there are implications here if a backup is
> > instantiated and PITR is done.
> > 
> > Just FYI that there could be others running like this ignorant of the
> > potential gotchas.
> 
> Yes, if we implement the TODO you will create a TEMPORARY tablespace
> that can't contain non-temporary and/or non-unlogged tables.

FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones,
increases the complexity of that project noticeably.  For TEMPORARY you
basically don't need to do much but to recreate the structure inside the
tablespace at start - fairly simple.  But for UNLOGGED you need to find
a way to recreate the relevant file and init forks - otherwise we might
not notice what needs to be reset at a crash restart, and we might error
out when executing selects etc. and then the table's not there.
Presumably recreating files & init forks that at first table access is
doable, but it's not entirely trivial to do locking wise.

- Andres



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Mon, Jun  5, 2017 at 07:38:43PM -0700, Andres Freund wrote:
> On 2017-06-05 22:34:17 -0400, Bruce Momjian wrote:
> > On Mon, Jun  5, 2017 at 04:38:32PM -0500, Jerry Sievers wrote:
> > > The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
> > > is no attempt to copy the NVME device when the snaps are made.
> > > 
> > > There's an event trigger plus batch job now running tou avoid this risk.
> > > 
> > > We realize too that there are implications here if a backup is
> > > instantiated and PITR is done.
> > > 
> > > Just FYI that there could be others running like this ignorant of the
> > > potential gotchas.
> > 
> > Yes, if we implement the TODO you will create a TEMPORARY tablespace
> > that can't contain non-temporary and/or non-unlogged tables.
> 
> FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones,
> increases the complexity of that project noticeably.  For TEMPORARY you
> basically don't need to do much but to recreate the structure inside the
> tablespace at start - fairly simple.  But for UNLOGGED you need to find
> a way to recreate the relevant file and init forks - otherwise we might
> not notice what needs to be reset at a crash restart, and we might error
> out when executing selects etc. and then the table's not there.
> Presumably recreating files & init forks that at first table access is
> doable, but it's not entirely trivial to do locking wise.

Agreed, that is why they are separate adjacent items on the TODO list. 
:-)

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Alvaro Herrera
Date:
Andres Freund wrote:

> FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones,
> increases the complexity of that project noticeably.  For TEMPORARY you
> basically don't need to do much but to recreate the structure inside the
> tablespace at start - fairly simple.  But for UNLOGGED you need to find
> a way to recreate the relevant file and init forks - otherwise we might
> not notice what needs to be reset at a crash restart, and we might error
> out when executing selects etc. and then the table's not there.
> Presumably recreating files & init forks that at first table access is
> doable, but it's not entirely trivial to do locking wise.

I was thinking that you could create the init fork for each unlogged
table in a permanent tablespace (probably the default one for the
database).

FWIW I don't think calling these tablespaces "temporary" is the right
word.  It's not the tablespaces that are temporary.  Maybe "evanescent".
Also, do we really need it to be a keyword after CREATE?  We could put
the option in the WITH clause of CREATE TABLESPACE instead.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Bruce Momjian
Date:
On Tue, Jun  6, 2017 at 04:39:50AM -0300, Alvaro Herrera wrote:
> Andres Freund wrote:
> 
> > FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones,
> > increases the complexity of that project noticeably.  For TEMPORARY you
> > basically don't need to do much but to recreate the structure inside the
> > tablespace at start - fairly simple.  But for UNLOGGED you need to find
> > a way to recreate the relevant file and init forks - otherwise we might
> > not notice what needs to be reset at a crash restart, and we might error
> > out when executing selects etc. and then the table's not there.
> > Presumably recreating files & init forks that at first table access is
> > doable, but it's not entirely trivial to do locking wise.
> 
> I was thinking that you could create the init fork for each unlogged
> table in a permanent tablespace (probably the default one for the
> database).
> 
> FWIW I don't think calling these tablespaces "temporary" is the right
> word.  It's not the tablespaces that are temporary.  Maybe "evanescent".

I was thinking "transient".  Amazon uses "ephemeral".

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Peter Eisentraut
Date:
On 6/6/17 03:39, Alvaro Herrera wrote:
> FWIW I don't think calling these tablespaces "temporary" is the right
> word.  It's not the tablespaces that are temporary.

The SQL standard meaning of temporary is that the content goes away.  It
is only in PostgreSQL that it also means that the object goes away.  I
think it's OK that it can encompass both meanings.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
"Sven R. Kunze"
Date:
On 06.06.2017 14:31, Bruce Momjian wrote:
On Tue, Jun  6, 2017 at 04:39:50AM -0300, Alvaro Herrera wrote:
I was thinking that you could create the init fork for each unlogged
table in a permanent tablespace (probably the default one for the
database).

FWIW I don't think calling these tablespaces "temporary" is the right
word.  It's not the tablespaces that are temporary.  Maybe "evanescent".
I was thinking "transient".  Amazon uses "ephemeral".


Just dropping a translator result here:

[1] results of https://www.dict.cc/deutsch-englisch/fl%C3%BCchtig.html

What about VOLATILE like computer memory (RAM)?

Sven

[1]
volatile {adj}chem.
briefly {adv}
fleeting {adj}
fugitive {adj} [temporary, fleeing]
brief {adj}
cursory {adj}
transient {adj}
vaguely {adv} [look, interested]
transitory {adj}
perfunctory {adj}
hasty {adj}
ephemeral {adj}
fleetingly {adv}
momentary {adj}
evanescent {adj}
passing {adj}
absconded {adj}
casual {adj} [glance, acquaintance etc.]
desultory {adj} [reading]
tangential {adj}
ethereal {adj}chem.
etherial {adj}chem.
flighty {adj}
sketchy {adj} [not detailed]
quick {adj}
perfunctorily {adv}
sketchily {adv}
fugitively {adv}
volatilely {adv}
hit and run {adj}
ephemerally {adv}
non-permanent {adj}
fugacious {adj} [literary]

Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

From
Robert Haas
Date:
On Tue, Jun 6, 2017 at 3:39 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> FWIW I don't think calling these tablespaces "temporary" is the right
> word.  It's not the tablespaces that are temporary.  Maybe "evanescent".

While I would personally find it pretty hilarious to see the
EVANESCENT in kwlist.h, I think it's probably a good idea to avoid
choosing keywords that will cause even proficient speakers of English
to need a dictionary.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company