pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedprinting results of query to file in different times
4+ messages / 2 participants
[nested] [flat]
* printing results of query to file in different times
@ 2017-08-24 13:15 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Mariel Cherkassky @ 2017-08-24 13:15 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
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 ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: printing results of query to file in different times
@ 2017-08-31 08:07 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Mariel Cherkassky @ 2017-08-31 08:07 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Anyone?
2017-08-24 16:15 GMT+03:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
> 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 ?
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Fwd: printing results of query to file in different times
@ 2017-08-31 08:44 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Mariel Cherkassky @ 2017-08-31 08:44 UTC (permalink / raw)
To: pgsql-admin
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 ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: printing results of query to file in different times
@ 2017-08-31 12:26 Roman Konoval <rkonoval@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Roman Konoval @ 2017-08-31 12:26 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@postgresql.org
Can you show explain with analyze and buffers options for your query?
Regards,
Roman Konoval
rkonoval@gmail.com
> On Aug 24, 2017, at 15:15, Mariel Cherkassky <mariel.cherkassky@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 ?
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-08-31 12:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-24 13:15 printing results of query to file in different times Mariel Cherkassky <mariel.cherkassky@gmail.com>
2017-08-31 08:07 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2017-08-31 08:44 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2017-08-31 12:26 ` Roman Konoval <rkonoval@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox