In-Reply-To: <424316EC.2090207@onesystem.com>
References: <0af8c6d3baaf3d4a6d918df294019507@mail.nih.gov>
	<7e154914b76fb1a7c55912b621400860@mail.nih.gov>
	<4240874E.2080301@archonet.com> <4242E732.3050600@onesystem.com>
	<1303f92d183bfd2ff7c522d4ad5afde8@mail.nih.gov>
	<424302D1.7000602@onesystem.com>
	<62cd9fffe536b0665a1da78478c4ec20@mail.nih.gov>
	<424316EC.2090207@onesystem.com>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <527567d18e0de8ad2c686699880f2bf0@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: Self-referencing table question
Date: Thu, 24 Mar 2005 15:30:09 -0500
To: Edmund Bacon <ebacon@onesystem.com>


On Mar 24, 2005, at 2:37 PM, Edmund Bacon wrote:

> Sean Davis wrote:
>
>> Nice.  Thanks for doing my work for me!
>
> Yeah, well put it down to a certain amount of curiosity and a slack 
> period at work ...
>
>> I guess I will have to think about it more seriously.
>>
>> It could be a slight bit complicated because my code is running under 
>> mod_perl, so connections are cached.  As I understand it, the temp 
>> table will stick around, so I will have to be careful to explicitly 
>> drop it if I don't want it to persist?
>
> I'm guessing so.    However you could put everything in a transaction 
> and use CREATE TEMP TABLE ... ON COMMIT DROP, and use INSERT INTO 
> rather than SELECT INTO.  The speed should be about equivalent - but 
> you'd have to test to make sure.
>
>> Also each table will need a unique name (I have a session_id I can 
>> use), as it is possible that multiple temp tables will exist and be 
>> visible to each other?
>
> Each session (connection in your case?) has it's own temporary table 
> space, so you shouldn't have to worry about that.
>

Sessions don't map 1-to-1 with connections in the web environment.  It 
is possible that a connection to the database would be simultaneously 
serving multiple users (sessions), if I understand Apache::DBI 
correctly.  In any case, this is probably a viable solution.

Sean