On Mon, Oct 1, 2012 at 10:21 AM, Moshe Jacobson <moshe@xxxxxxxxxxxx> wrote: > 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. A vanilla create table has to scan the catalogs also. >> *) 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. Especially if you're using pgbouncer transaction mode, using temporary tables is probably not a good idea. When you DISCARD them, it invalidates all your function plans which is going to be painful if you have a lot of pl/pgsql (test any non-trivial pl/pgsql routine and you'll see it's much slower on the first invocation). Also, if you need to share data between transactions, it's not clear how you're organizing such that different application sessions are going to tromp over each other's data. >> *) 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. IMO the right way to do it is to generate a unique application token (sequence is ok if you're not worried about it being guessed) when your application session logs in. That token should be passed into *all* your session specific backend functions and can be used to organize session specific temporary data in your permanent tables. To deal with ungraceful application client exit, you can consider implementing an on_proc_exit handler to close the session down so that it can be appropriately cleaned up (there are severe limits to the SQL you can execute in the handler but you can make dblink calls). If some of them still sneak through, periodic sweep on stale pids against pg_stat_activity should take care of them. Note, if your users have some type of unique identifier (like a login or an email) and if they are only allowed to have one active session at a time, you can organize your session data around that instead of generating a token. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
