Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f23FYCx18029 for ; Sat, 3 Mar 2001 10:34:12 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id PAA07756; Sat, 3 Mar 2001 15:35:16 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id A3FAD2A476; Sat, 3 Mar 2001 15:32:15 +0000 (GMT) Message-ID: <3AA10E77.B63D172B@archonet.com> Date: Sat, 03 Mar 2001 15:32:07 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: David Olbersen Cc: Gerald Gutierrez , pgsql-sql@postgresql.org Subject: Re: Temp Tables & Connection Pooling References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/37 X-Sequence-Number: 1682 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? > > > Wouldn't a VIEW do what you want? > > > -- 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