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

On Mon, Oct 1, 2012 at 10:21 AM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
> 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.

A vanilla create table has to scan the catalogs also.

>> *) 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.

Especially if you're using pgbouncer transaction mode, using temporary
tables is probably not a good idea. When you DISCARD them, it
invalidates all your function plans which is going to be painful if
you have a lot of pl/pgsql (test any non-trivial pl/pgsql routine and
you'll see it's much slower on the first invocation). Also, if you
need to share data between transactions, it's not clear how you're
organizing such that different application sessions are going to tromp
over each other's data.

>> *) 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.

IMO the right way to do it is to generate a unique application token
(sequence is ok if you're not worried about it being guessed) when
your application session logs in. That token should be passed into
*all* your session specific backend functions and can be used to
organize session specific temporary data in your permanent tables.

To deal with ungraceful application client exit, you can consider
implementing an on_proc_exit handler to close the session down so that
it can be appropriately cleaned up (there are severe limits to the SQL
you can execute in the handler but you can make dblink calls). If
some of them still sneak through, periodic sweep on stale pids
against pg_stat_activity should take care of them.

Note, if your users have some type of unique identifier (like a login
or an email) and if they are only allowed to have one active session
at a time, you can organize your session data around that instead of
generating a token.

merlin