Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

Merlin,

On Mon, Oct 1, 2012 at 10:28 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>
> Couple points:
> *) Functions without exception blocks are faster than those with.
>

Clearly.

> *) Therefore, CREATE/IF NOT EXISTS is probably faster (test to be sure)
>

I don't think that can be assumed by your premise above. Essentially we are
comparing the price of starting an exception block against checking the
catalog for a table.

> *) Carefully consider if you you will ever in the future introduce
> connection pooling. If you do, relying on session scoped objects like
> temp tables is probably not a good idea.
>

We already use connection pooling with pgbouncer, but upon disconnect, it
issues a DISCARD ALL statement, which should take care of this.

> *) You can rig permanent tables around pg_backend_pid(). On session
> login, clear session private records that have your pid (if any).
> Transaction temporary data can be similarly rigged around
> txid_current() with an even simpler maintenance process.
>

We currently do use permanent tables using pg_backend_pid(). It's because
of the connection pooling specifically that we are having problems with
stale data. I have been unable to find a way to automatically clear that
data upon start or end of a session, or at least check if it's been set in
this session or not.

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com