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

On Mon, Oct 1, 2012 at 11:22 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> 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

actually, you can't do that (on_proc_exit or scan for pids) if you're
using transaction mode connection pooling. In our case, we modified
pgbouncer to pass async notifications and would have used that to
periodically scan connected clients if we didn't have the luxury of
one client/session only.

merlin