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

On Mon, Oct 1, 2012 at 12:22 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> >> *) Functions without exception blocks are faster than those with.
> >> *) 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.
>
> A vanilla create table has to scan the catalogs also.
>

Yes but that is irrelevant to the discussion. I am comparing the speed of
repeated table existence checks with the speed of repeated exception blocks
that access said table.

> We already use connection pooling with pgbouncer, but upon disconnect, it
> > issues a DISCARD ALL statement [...]
>
> Especially if you're using pgbouncer transaction mode, using temporary
> tables is probably not a good idea.
>

We are using it in session mode, so none of that is relevant to my
situation.

>> *) You can rig permanent tables around pg_backend_pid(). [...]
> >
> > 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.
>
> IMO the right way to do it is to generate a unique application token
> [...] when your application session logs in. That token should be passed
> into
> *all* your session specific backend functions [...]
>

No, this will not work because the backend functions are trigger functions,
so they cannot be passed this data.

Thanks.

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