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

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@gmail.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