Jim, Thanks for the help. I went and looked at that example and I don't see how it's different than the "INSERT into radutmp_tab" I'm already doing. Both raise an exception, the only difference is that I'm not doing anything with it. Perhaps you are talking about the "IF (NOT FOUND)" I put after the "UPDATE radutmp_tab". Should this be an EXCEPTION instead? Also I don't know how this could cause a race condition. As far as I understand each proc is run in it's own transaction, and the code in the proc is run serially. Can you explain more why this could case a race? Thanks, schu Jim C. Nasby wrote: > Periodically taking longer is probably a case of some other process in > the database holding a lock you need, or otherwise bogging the system > down, especially if you're always running acctmessage from the same > connection (because the query plans shouldn't be changing then). I'd > suggest looking at what else is happening at the same time. > > Also, it's more efficient to operate on chunks of data rather than one > row at a time whenever possible. If you have to log each row > individually, consider simply logging them into a table, and then > periodically pulling data out of that table to do additional processing > on it. > > BTW, your detection of duplicates/row existance has a race condition. > Take a look at example 36-1 at > http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > for a better way to handle it. >> ========================================================================== >> CREATE acctmessage( <lots of accounting columns> )RETURNS void AS $$ >> BEGIN >> INSERT into tmpaccounting_tab ( ... ) values ( ... ); >> >> IF _acctType = 'start' THEN >> BEGIN >> INSERT into radutmp_tab ( ... ) valuse ( ... ); >> EXCEPTION WHEN UNIQUE_VIOLATION THEN >> NULL; >> END; >> ELSIF _acctType = 'stop' THEN >> UPDATE radutmp_tab SET ... WHERE sessionId = _sessionId AND userName = >> _userName; >> IF (NOT FOUND) THEN >> INSERT into radutmp_tab ( ... ) values ( ... ); >> END IF; >> >> END IF; >> END; >> $$ >> LANGUAGE plpgsql; >> ==========================================================================
