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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Moshe Jacobson <moshe(at)neadwerx(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
Date: 2012-10-01 16:28:54
Message-ID: CAHyXU0yEbdiitChqBZWOsRB1-pSzPzMtnBHE-GmziYiq0PNFaA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2012-10-01 16:59:40 Re: Again, problem with pgbouncer
Previous Message Merlin Moncure 2012-10-01 16:22:03 Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?