public inbox for [email protected]  

help / color / mirror / Atom feed

From: Roman Konoval <[email protected]>
To: Mariel Cherkassky <[email protected]>
Cc: [email protected]
Subject: Re: printing results of query to file in different times
Date: Thu, 31 Aug 2017 14:26:34 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+t6e1msXcLNKXc_AeVBWWzYCHxNK3LVP-bUgcia_LhsKQKbiA@mail.gmail.com>
References: <CA+t6e1msXcLNKXc_AeVBWWzYCHxNK3LVP-bUgcia_LhsKQKbiA@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

Can you show explain with analyze and buffers options for your query?

Regards,
Roman Konoval
[email protected]



> On Aug 24, 2017, at 15:15, Mariel Cherkassky <[email protected]> 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 ?
> 
> 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: printing results of query to file in different times
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox