From: Matthew Schumacher <matt.s@aptalaska.net>
To: Jim C. Nasby <jim@nasby.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with inconsistant query performance.
Date: Wed, 27 Sep 2006 13:33:09 -0800
Message-ID: <451AEE15.6090103@aptalaska.net> (raw)
In-Reply-To: <20060927203155.GT19827@nasby.net>
References: <451AC4E2.90106@aptalaska.net>
<20060927203155.GT19827@nasby.net>
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-TRAPPIN...
> 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;
>> ==========================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: matt.s@aptalaska.net, jim@nasby.net
Subject: Re: Problems with inconsistant query performance.
In-Reply-To: <451AEE15.6090103@aptalaska.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox