Re: Problems with inconsistant query performance.

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;
>> ==========================================================================