Re: Temp Tables & Connection Pooling

David Olbersen wrote:
>
> On Fri, 2 Mar 2001, Gerald Gutierrez wrote:
>
> ->Recently I wanted to implement Dijkstra's algorithm as a stored procedure,
> ->and finding that PL/PGSQL cannot return record sets, I thought about using
> ->a temporary table for the results. If tempoary tables are session-specific,
> ->however, then wouldn't connection pooling make it unusable since the table
> ->might "disappear" from one query to the next? What are alternative
> ->approaches to implementing Dijkstra's algorithm inside the database?
>
> <newbie>
> Wouldn't a VIEW do what you want?
> </newbie>
>
> -- Dave

Presumably Gerald's after speed here - IIRC Dijkstra's is shortest path
finder, so probably not cheap.

I was thinking about the temp table problem the other day, and the best
I could come up with involved creating a higher-level connection
(application-level session basically). You'd create a table mytempNNN
(where NNN is a unique number to identify your user's session) and add a
line to a tracking table (NNN,now())

Every time you use mytempNNN update the tracking table's time and run a
separate reaper process to kill anything not used for 15 minutes (or whatever).

You should be able to automate this to a degree with triggers etc.

- Richard Huxton