On Mon, Oct 1, 2012 at 12:22 PM, Merlin Moncure
<mmoncure@gmail.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
