Fwd: printing results of query to file in different times

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Fwd: printing results of query to file in different times
Date: 2017-08-31 08:44:04
Message-ID: CA+t6e1=H+Drnd28bgtahr6waDWr_n7uyxhp8XJONEgm219JgmA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

Our app team wrote a function that runs with a cursor over the results of a
query and via the utl_file func they write some columns to a file. I dont
understand why, but postgresql write the data into the file in the fs in
parts. I mean that it runs the query and it takes time to get back results
and when I see that the results back postgresql write to file the data and
then suddenly stops for X minutes. After those x minutes it starts again to
write the data and it continues that way until its done. I want to
understand why it stops suddenly. There arent any locks in the database
during this operation.

my function looks like that :

func(a,b,c...)

cursor cr for

select ab,c,d,e.....

begin

raise notice - 'starting loop time - %',timeofday();

for cr_record in cr

Raise notice 'print to file - '%',timeofday();

utl_file.write(file,cr_record)

end loop

end

I see the log of the running the next output :

starting loop 16:00

print to file : 16:03

print to file : 16:03

print to file : 16:03

print to file : 16:03

print to file : 16:07

print to file : 16:07

print to file : 16:07

print to file : 16:010

......

Can somebody explain to me this kind of behavior ? Why is it taking some
much time to write and in different minutes after the query already been
executed and finished ? Mybe I'm getting from the cursor only part of the
rows ?

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Roman Konoval 2017-08-31 12:26:34 Re: printing results of query to file in different times
Previous Message Mariel Cherkassky 2017-08-31 08:07:28 Re: printing results of query to file in different times

Browse pgsql-performance by date

  From Date Subject
Next Message Roman Konoval 2017-08-31 12:26:34 Re: printing results of query to file in different times
Previous Message Mariel Cherkassky 2017-08-31 08:07:28 Re: printing results of query to file in different times