On Mon, Oct 1, 2012 at 8:36 AM, Moshe Jacobson <moshe@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
