I'm trying to understand what postgresql doing in an issue that I'm having. 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. The query returns total 100 rows. 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:01 print to file : 16:03:01 print to file : 16:03:01 print to file : 16:**03**:01 print to file : 16:**10**:00 print to file : 16:10:00 ......
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 ?