Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dnL58-0006bx-WE for pgsql-admin@arkaria.postgresql.org; Thu, 31 Aug 2017 08:44:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dnL58-00027W-It for pgsql-admin@arkaria.postgresql.org; Thu, 31 Aug 2017 08:44:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dnL57-00023q-IK for pgsql-admin@postgresql.org; Thu, 31 Aug 2017 08:44:09 +0000 Received: from mail-wm0-x236.google.com ([2a00:1450:400c:c09::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dnL54-00016v-OB for pgsql-admin@postgresql.org; Thu, 31 Aug 2017 08:44:08 +0000 Received: by mail-wm0-x236.google.com with SMTP id u26so137431wma.0 for ; Thu, 31 Aug 2017 01:44:06 -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=aUEyczcqwgE771Rf78Wiht4f+8S8ECYwgGBcslnogB4=; b=LTYbCbzqwzqwWnC+ZCuUNiB5TxuSJf7MbEBbBB7kCpfr8C5TO9wbguzzpXgD7n3Z1b beg4jf4Lp3UNree6+Z+xXmVes/uSXF0GcHvioZUdtcoXAXZDhsdNrYwFke1R/bxy40Pd fFemtV+olN0Ru0Vosmr6S5K6biRHFnfMUGe14BK9t72NPPG2+QIcKtlQjDvbqQg3tU99 eylLeL1kYP8unVx2hOrrufln2CkiY/D9TnYNE50akeNSBQkTUyQm50+GSyC+S+WDe5+w GPFCSKoR/dn72JUGBxyiFyYIXPpGFlgy/d4dfCBkiraFsGNzC7u100RWbw3LOtB58F9Q YYjg== 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=aUEyczcqwgE771Rf78Wiht4f+8S8ECYwgGBcslnogB4=; b=bW/ov/g63MoS2W479U+rm0996fXc8YNZGAxjKtKu1+fIJNOMZ1JLYTEDcOtfiYqtnV KiIl8hCgmEb1T+qQoC4399NtQQAv2tIlUgjQYYaC3QgB4LAEWnpMw14QqeCPsCawyMql yEznFjDHeIT2vxI4exK2+JgQcMuF/FndftIPj7zd7+ykAAYAWNZrHlB9nETGk02h4K3k thsA56wWvrm/RNyu+yubd+gQd9A/8ZNJKFQMuj/RlRVyogZ/wfKB1cRAHTA3x88p8/TB uOANAz3+jUlaTxO186XHorXYNdgZCI1NZo4KUOxqlqFLSG9rV+SaeBkL+mpM/9KjnY7h wMxQ== X-Gm-Message-State: AHPjjUgtmbkR2PaspjtsTCjvzklULLTkl4bvBt/WaYAhuU6+/m7LzC6c dPeyCIdy6EZFtejC0DWq0ibwVkzPdl7CMYw= X-Google-Smtp-Source: ADKCNb46sqZq6g/rgrpT3tZzTnoGfugRhSDlsbMl9omi/x1pT39zvRo21dnhZIoLaX35LzKp2yFy2THYW18gu3wVdUk= X-Received: by 10.28.27.200 with SMTP id b191mr2314wmb.129.1504169045027; Thu, 31 Aug 2017 01:44:05 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.144.139 with HTTP; Thu, 31 Aug 2017 01:44:04 -0700 (PDT) In-Reply-To: References: From: Mariel Cherkassky Date: Thu, 31 Aug 2017 11:44:04 +0300 Message-ID: Subject: Fwd: printing results of query to file in different times To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="001a114b40309c7182055808a597" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --001a114b40309c7182055808a597 Content-Type: text/plain; charset="UTF-8" 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 ? --001a114b40309c7182055808a597 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Our app team wrote a function that runs wit= h 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 da= ta into the file in the fs in parts. I mean that it runs the query and it t= akes time to get back results and when I see that the results back postgres= ql write to file the data and then suddenly stops for X minutes. After thos= e x minutes it starts again to write the data and it continues that way unt= il its done.=C2=A0I want to unde= rstand why it stops suddenly. There arent any locks in the database during = this operation.

my function l= ooks like that :=C2=A0

func(a,b,c...= )

cursor cr fo= r

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

begin

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

=C2=A0for cr_record in cr

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

=C2=A0 =C2=A0 utl_file.write(file,cr_r= ecord)

=C2=A0end loop

end

I see the log of the running the next output :=C2=A0=

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: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 ?


--001a114b40309c7182055808a597--