Message-ID: <451AEE15.6090103@aptalaska.net>
Date: Wed, 27 Sep 2006 13:33:09 -0800
From: Matthew Schumacher <matt.s@aptalaska.net>
User-Agent: Thunderbird 1.5.0.7 (X11/20060909)
MIME-Version: 1.0
To: "Jim C. Nasby" <jim@nasby.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Problems with inconsistant query performance.
References: <451AC4E2.90106@aptalaska.net> <20060927203155.GT19827@nasby.net>
In-Reply-To: <20060927203155.GT19827@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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