Re: temp files getting me down

On May 25, 2011, at 7:36 PM, Merlin Moncure wrote:

> On Wed, May 25, 2011 at 6:44 PM, Ben Chobot <bench(at)silentmedia(dot)com> wrote:
>>
>>
>> Well, the query itself was calling a plpgsql function, and the function itself was doing:
>>
>> DECLARE
>> row formatted_replication_queue%ROWTYPE;
>> BEGIN
>> for row in select * from formatted_replication_queue where
>> distributor_id IS NULL AND
>> ('{{%,%}}'::varchar[] @> ARRAY[source_site, dest_site]::varchar[] OR
>> '{{%,%}}'::varchar[] @> ARRAY['%', dest_site]::varchar[] OR
>> '{{%,%}}'::varchar[] @> ARRAY[source_site, '%']::varchar[] OR
>> '{{%,%}}'::varchar[] @> ARRAY['%', '%']::varchar[])
>> ORDER BY update_time ASC
>> limit 4000
>> for update
>> LOOP
>> UPDATE replication_queue SET distributor_id = 't32' WHERE filehash = row.filehash;
>> RETURN NEXT row;
>> END LOOP;
>> RETURN;
>> END
>>
>> Doing that select manually didn't seem to be causing the same issues. formatted_replication_queue is a simple view that reformats some columns but does no sorting.
>
> um, right. how about for starters querying the view outside of the
> function and see what plan we get...

Sorry, I thought I'd mentioned that I had run this select outside the function but was unable to replicate the temp file creation that way. That's how I got the explain analyze plan.

> also you still haven't posted the original query. one major gotcha
> with plpgsql is the function executing more times than you think.
> posting the calling query will help, or simple logging from inside the
> function (raise notice, etc).

Well the calling query was merely "select * from f(4000,'t32');" It wasn't in a join, and the args were constants. It's a simple "give me more work from the work queue" function.

I really don't think it was being called more than I thought, because I saw each query was taking about 3 minutes. During those 3 minutes, I observed a temp file building up. Then it would go away right as the query finished.

I'll be lax about staying on top of bloat for this database and if it returns to previous levels of bloat, perhaps the problem will return.