From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | printing results of query to file in different times |
Date: | 2017-08-24 13:15:19 |
Message-ID: | CA+t6e1msXcLNKXc_AeVBWWzYCHxNK3LVP-bUgcia_LhsKQKbiA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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 | Don Seiler | 2017-08-26 22:58:02 | Standby Mechanics: WAL vs Streaming |
Previous Message | Aleksander Kamenik | 2017-08-23 09:38:14 | pg_current_xlog*_location and pg_stat_replication.replay_location > 0 for synced replication connection |
From | Date | Subject | |
---|---|---|---|
Next Message | Claudio Freire | 2017-08-24 16:14:05 | Re: performance problem on big tables |
Previous Message | Mariel Cherkassky | 2017-08-24 07:51:11 | Re: performance problem on big tables |