Re: nooby Q: temp tables good for web apps?

From: Erik Jones <ejones(at)engineyard(dot)com>
To: Kenneth Tilton <kentilton(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: nooby Q: temp tables good for web apps?
Date: 2009-04-08 22:27:11
Message-ID: 14B12F27-8CE1-404B-8B42-1193011824C1@engineyard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Apr 7, 2009, at 7:32 PM, Kenneth Tilton wrote:

> Scott Marlowe wrote:
>> On Tue, Apr 7, 2009 at 7:12 PM, Kenneth Tilton
>> <kentilton(at)gmail(dot)com> wrote:
>>>
>>> Scott Marlowe wrote:
>>>> You can use a different method if you need a table available to the
>>>> same session. Create a schema based on the session id, and put
>>>> your
>>>> temp tables there, only don't call them temp tables. You'll either
>>>> need to make sure you always clean up your temp schema your session
>>>> created or come up with a daemon that comes along every hour or
>>>> so and
>>>> kills off old schemas that aren't in use anymore.
>>> I am LMAO because Lisp (my server-side lang) does this to noobs,
>>> too: three
>>> (at least) ways to do everything. Well, if all things are equal
>>> dropping one
>>> schema and not kludging up mangled table names has a lot of
>>> appeal. Thx.
>> Schemas, search_path and views together can let you do some pretty
>> cool things in terms of integrating external postgresql based apps
>> with each other.
>
> Or between XHRs? It just occurred to me that if I go with a schema
> instead of temp tables then I do not need to worry about hanging on
> to a connection/pgsession, or even worry about routing a web session
> to the same process if all state is stored in pg under the session id.
>
> ken *coming up to speed slowly, going to look up search_path*

If you're using pg_dump for backups then you'll probably want at least
a standard prefix on your "temp" schemas so that you can easily have
pg_dump ignore them when doing backups with it's -N flag.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Chris 2009-04-08 22:49:49 Re: Table has 22 million records, but backup doesn't see them
Previous Message Ron Mayer 2009-04-08 22:25:25 Re: Are there performance advantages in storing bulky field in separate table?