Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Merlin,

On Mon, Oct 1, 2012 at 10:28 AM, Merlin Moncure <mmoncure@xxxxxxxxx> 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.
 
*) 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.
 
*) 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.
 
-- 
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@xxxxxxxxxxxx | 
www.neadwerx.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux