From: | Roman Konoval <rkonoval(at)gmail(dot)com> |
---|---|
To: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: printing results of query to file in different times |
Date: | 2017-08-31 12:26:34 |
Message-ID: | E4E980F5-04DA-4ACF-B8B0-A56C6C8704D4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
Can you show explain with analyze and buffers options for your query?
Regards,
Roman Konoval
rkonoval(at)gmail(dot)com
> On Aug 24, 2017, at 15:15, Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)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 ?
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michal Merta | 2017-08-31 14:52:05 | Amount of unused space on a storage device |
Previous Message | Mariel Cherkassky | 2017-08-31 08:44:04 | Fwd: printing results of query to file in different times |
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2017-08-31 13:24:35 | Re: printing results of query to file in different times |
Previous Message | Mariel Cherkassky | 2017-08-31 08:44:04 | Fwd: printing results of query to file in different times |