Merlin,
On Mon, Oct 1, 2012 at 10:28 AM, Merlin Moncure
<mmoncure@gmail.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.
*) 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
