Re: Self-referencing table question

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