Re: Unlogged vs. In-Memory

On Tue, May 3, 2011 at 2:44 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Tue, May 3, 2011 at 20:06, Thom Brown <thom(at)linux(dot)com> wrote:
>> On 3 May 2011 19:02, Rob Wultsch <wultsch(at)gmail(dot)com> wrote:
>>> Can Unlogged tables be located on a table space mount on a ram fs
>>> without hosing the instance if the server gets bounced?
>>
>> No more than anything else in a RAM filesystem.  There are of course
>> battery-backed RAM disk devices people can use, but those are a
>> special case.
>
> I think you're missing the scenario Rob is talking about. I think he
> mentions the sequence:
>
> CREATE TABLESPACE junk LOCATION '/tmp/junk';
> CREATE UNLOGGED TABLE meh(a int) TABLESPACE junk;
> <stop server>
> rm -rf /tmp/junk/*
> <start server>
> postgres=# select * from meh;
> ERROR:  could not open file
> "pg_tblspc/16434/PG_9.1_201104251/12008/16435": No such file or
> directory
>
> Now if the tablespace contains *only* unlogged tables, it should at
> least theoretically be possible to recover from this situation on
> startup, I think. But it's not now. Anybody have an idea about how
> much work that would be?

What you would need to do is save the _init forks of every relation
(only) and copy them back onto the ram disk before starting up
PostgreSQL. Assuming that the set of tables is static and that you
don't perform table-rewriting operations (like CLUSTER) on them, this
wouldn't be too hard to script.

To make PG do it automatically, we'd need to store the _init forks in
a different tablespace from the remaining forks. That's probably
possible, but it seems complicated.

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