Re: temporary tables

If you create a LOCAL TEMPORARY table, it will be automatically deleted
at the end of the transaction. Also, in 7.3, temporary table data will
_not_ be synced/forced to disk like ordinary tables, though disk will
still be used as backing store for the table. You can drop them
manually too.

---------------------------------------------------------------------------

Greg Stark wrote:
>
> I've always avoided using temporary tables for OLTP applications because I was
> afraid the rapid creating and deleting of temporary data would limit
> scalability.
>
> Now I'm finding they might be necessary for a particular problem so I'm
> looking for information on how efficient they are with Postgres. In my case
> it's being used for a set of purely read-only queries for performance reasons.
> I won't be updating the temporary table at all once created.
>
> Will postgres write out the data to disk or just store it in local memory in
> the single backend using it? Will it be able to handle lots of users
> simultaneously creating and destroying these temporary tables without
> performance degrading?
>
> I'm only looking at using them because I have four separate queries based on a
> data set that is the result of aggregating a much larger data set. The only
> reason to use the temporary tables is for performance reasons, so if it won't
> help over rerunning the slow aggregate subquery four times then, well, I have
> a problem, because that would be too slow.
>
> Incidentally, temporary tables don't seem to appear anywhere in the online
> docs, and are only mentioned briefly in my book (yes, the same book). Is there
> any way to control when the temporary tables are destroyed other than at the
> end of the session? Is there any way to tell Postgres to destroy them at the
> end of a transaction or do i assume i have to do it manually? Do I use drop
> table normally?
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073