Re: idea: global temp tables

On Apr 27, 2009, at 6:01 PM, Kevin Grittner wrote:

> "A.M." <agentm(at)themactionfaction(dot)com> wrote:
>> On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote:
>>> Le 27 avr. 09 à 23:32, A.M. a écrit :
>>>> When will postgresql offer "global" temporary tables with data
>>>> which are shared among sessions? Such tables are great for
>>>> transient data such as web session data where writing to the WAL is
>
>>>> a waste. (On DB startup, the tables would simply be empty.) We're
>
>>>> currently stuck with the memcached plugin which makes it impossible
>
>>>> to use database constructs such as foreign keys against the
>>>> temporary data.
>>>
>>>
>>> If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
>>> session management transactions, it'll skip the WAL fsync'ing, which
>
>>> is already a good start.
>>
>> That's pretty close, but it's not table specific and wouldn't let us
>
>> to reliably mix transient data changes with real data changes.
>
> Yeah, we have a dozen or so tables we use with the pattern you
> describe; so the feature you describe would also have some value for
> us. To avoid confusion, we don't refer to these as "temporary
> tables", but rather as "permanent work tables". Again, I can't
> comment on practical issues regarding implementation; but it would be
> a "nice feature" to add some day. The tricky bit would be to figure
> out how to ensure that it got cleaned up properly, especially if the
> PostgreSQL went down or client processes wend down before tidying up.

Actually, for our usage, that's the easiest part- truncate all the
"permanent work tables" whenever the db starts. That's really the
only sane thing to do anyway. That's what I mean by "transient" data-
if it's there, that's great, if not, I can re-generate it (cache) or
I don't care because, if the database goes down, then the data is
useless on restart anyway.

Cheers,
M