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

On Mon, Oct 1, 2012 at 8:36 AM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
> I am working on an audit logging trigger that gets called for every row
> inserted, updated or deleted on any table.
> For this, I need to store a couple of temporary session variables such as
> the ID of the user performing the change, which can be set at the start of
> the session.
> Until now I have been using a permanent table to store the session
> variables, but it has been difficult to wipe the data properly at the end of
> the session.
> So I have decided to try to implement them using temporary tables.
>
> The problem now is that for every row now, I need to check for the existence
> of the temporary table before I access it, in order to avoid exceptions.
> Either I can do all such accesses within a BEGIN...EXCEPTION block, or I can
> precede any such accesses with CREATE TEMP TABLE IF NOT EXISTS.
> Is one of these much faster than the other? Will I be slowing things down
> inordinately by doing this for every row?

Couple points:
*) Functions without exception blocks are faster than those with.
*) Therefore, CREATE/IF NOT EXISTS is probably faster (test to be sure)
*) 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.
*) 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.

merlin