From: Edmund Bacon <ebacon@onesystem.com>
To: Sean Davis <sdavis2@mail.nih.gov>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Self-referencing table question
Date: Thu, 24 Mar 2005 12:37:16 -0700
Message-ID: <424316EC.2090207@onesystem.com> (raw)
In-Reply-To: <62cd9fffe536b0665a1da78478c4ec20@mail.nih.gov>
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>
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.
>
>
> Thanks again,
> Sean
>
--
Edmund Bacon <ebacon@onesystem.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ebacon@onesystem.com, sdavis2@mail.nih.gov
Subject: Re: Self-referencing table question
In-Reply-To: <424316EC.2090207@onesystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox