Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dnKVk-0004ao-4b for pgsql-performance@arkaria.postgresql.org; Thu, 31 Aug 2017 08:07:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dnKVj-0005uX-3N for pgsql-performance@arkaria.postgresql.org; Thu, 31 Aug 2017 08:07:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dnKVi-0005tx-9U for pgsql-performance@postgresql.org; Thu, 31 Aug 2017 08:07:34 +0000 Received: from mail-wr0-x22c.google.com ([2a00:1450:400c:c0c::22c]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dnKVf-0006hv-31 for pgsql-performance@postgresql.org; Thu, 31 Aug 2017 08:07:33 +0000 Received: by mail-wr0-x22c.google.com with SMTP id p14so24238408wrg.3 for ; Thu, 31 Aug 2017 01:07:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=vWkW8IKGXkkkVxz/YLvWhaeYMH8F6EUYNuFcigYHhYE=; b=mZIUZikjChCHObFIH0mO0NMxAHn1JYb2aWdO+GN7jMk22jYHW8Hg5CnwxPYAemXrY3 +efnnupLinF8EERHLe264V9k+SzqjeLfkFIeLY8S1bXZ5S54ihuiScwkYdatbQbXTZjG ekbDaTWoyQJpCgRl8E0xAMOnRTKNeACgc4gSJRH2kAFIW12DDZ1DWI6r4CeNkMloZds+ DjnEMPgc99mzkK1znkK2l9icQz7jnd0Xs5Yr6dE2JN945shepUkhq2cZ+LiCmTEkhIcQ foBarWngGmgzce1Q8sTJKJXES1xpAZtqj7jzh7bBRyESOdyfi9VbCbW8plkBdrQYfScx ZGMg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=vWkW8IKGXkkkVxz/YLvWhaeYMH8F6EUYNuFcigYHhYE=; b=pEHhJHl7JFy+dHO+TYnv3BvHmuD4M5yCNjDzs+vOGnv8FefW+gKldSac9rzQ62IZbO 1+Ze6XkMiZvZk57bySAP5oX+IXSUNNpS13VadqIg6mEtW4RIDNhYtO3v50qOudxNdku2 BxE/Q3r1o/kJRydXAeOkx8wtE0w3O1iN/yi3xAU/RAAT6nLf49aiTaxQN6FUakTfC52l qJo6rtg0MjZwnEqrUxqvVwYIo5s29qkuj1VMdjOBvyU33Bcx2nSnSlpM7+4YbzfrYaVF fyTp2IP76Hsum2UG9Q2PveaEddQ7GhAFjihwNy8mB5jxR7BgqQ6oV+/VTVue9rGLmlxZ mUAg== X-Gm-Message-State: AHYfb5gQi1xIfPjUHhQNKzDV6FavVvd+nYNh5FMCCqZMNT3wsidNzr4K mxOqBVwtw0A9iOclKWRQUhvsew8TRXaf X-Google-Smtp-Source: ADKCNb5aj3lWJn0ZBgIQK7E2sVikL2fwNbDmm8xF8yyWL/QloSg3k1Fdg2mGDyzZFsWJwegu81PmcLA6cTheDn2kWOQ= X-Received: by 10.223.148.101 with SMTP id 92mr2392928wrq.182.1504166849458; Thu, 31 Aug 2017 01:07:29 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.144.139 with HTTP; Thu, 31 Aug 2017 01:07:28 -0700 (PDT) In-Reply-To: References: From: Mariel Cherkassky Date: Thu, 31 Aug 2017 11:07:28 +0300 Message-ID: Subject: Re: printing results of query to file in different times To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="94eb2c0d2200bec913055808225a" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --94eb2c0d2200bec913055808225a Content-Type: text/plain; charset="UTF-8" Anyone? 2017-08-24 16:15 GMT+03:00 Mariel Cherkassky : > 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 ? > > > --94eb2c0d2200bec913055808225a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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&= #39;m having. Our app team wrote a function that runs with a cursor over th= e 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 i= n the fs in parts. I mean that it runs the query and it takes time to get b= ack results and when I see that the results back postgresql write to file t= he data and then suddenly stops for X minutes. After those x minutes it sta= rts again to write the data and it continues that way until its done. The q= uery returns total 100 rows. I want to understand why it stops suddenly. There arent any lock= s in the database during this operation.

my function looks like that :=C2=A0

func(a,b,c...)

cursor cr for

select ab,c,d,e.....

begin

raise notice - 'sta= rting loop time - %',timeofday();

=C2=A0for cr_record in cr

=C2=A0 =C2= =A0 Raise notice 'print to file - '%',timeofday();

=C2=A0 =C2= =A0 utl_file.write(file,cr_record)

=C2=A0end loop

end

I see the log of the runn= ing the next output :=C2=A0

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:0= 10

......



Can somebody explain to me thi= s kind of behavior ? Why is it taking some much time to write and in differ= ent minutes after the query already been executed and finished ? Mybe I'= ;m getting from the cursor only part of the rows ?


<= /div>
--94eb2c0d2200bec913055808225a--