I'm using an extension that is called orafce. Yes I add the raise notice in order to see what happening but it doesnt work faster. The execution plan isnt relevant because It happens for many queries not for a specific one. I didnt understand what do you mean by REPEATABLE_READ .
2017-08-31 16:24 GMT+03:00 George Neuner <gneun...@comcast.net>: > On Thu, 24 Aug 2017 16:15:19 +0300, Mariel Cherkassky < > mariel.cherkas...@gmail.com> wrote: > > >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 > >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 ? > > > First I'd ask where did you get utl_file from? Postrgesql has no such > facility, so you must be using an extension. And not one I'm familiar with > either - EnterpriseDB has a utl_file implementation in their Oracle > compatibility stuff, but it uses "get" and "put" calls rather than "read" > and "write". > > Second, raising notices can be slow - I assume you added them to see what > was happening? How does the execution time compare if you remove them? > > I saw someone else asked about the execution plan, but I'm not sure that > will help here because it would affect only the initial select ... the > cursor would be working with the result set and should be able to skip > directly to the target rows. I might expect several seconds for the loop > with I/O ... but certainly not minutes unless the server is severely > overloaded. > > One thing you might look at is the isolation level of the query. If you > are using READ_COMMITTED or less, and the table is busy, other writing > queries may be stepping on yours and even potentially changing your result > set during the cursor loop. I would try using REPEATABLE_READ and see what > happens. > > > George > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
