MIME-Version: 1.0
References: 
 <CAJif3k+Q_nksEkS3SwpaButxxTcOkjgG91_sLLcc=986-2VZAg@mail.gmail.com>
 <19069.1563488801@sss.pgh.pa.us>
In-Reply-To: <19069.1563488801@sss.pgh.pa.us>
From: =?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <sobotka.luk@gmail.com>
Date: Fri, 19 Jul 2019 12:34:19 +0200
Message-ID: 
 <CAJif3kJdz6yx+31DSnD1eq_cNF0tccNBG2-FfSo4Y7rQv3L3QQ@mail.gmail.com>
Subject: Re: Using COPY command in pqlib
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-interfaces@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000731032058e064652"
Precedence: bulk

--000000000000731032058e064652
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tom,

I see we are not using copy in correct way.

Many thanks for your time.
Lukas

p=C3=A1 19. 7. 2019 v 0:26 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> nap=
sal:

> =3D?UTF-8?B?THVrw6HFoSBTb2JvdGth?=3D <sobotka.luk@gmail.com> writes:
> > I would like to ask you on correct way how to use pqlib for copying dat=
a.
> > We tried to do it according documentation but some problems occurred.
>
> > We have a multi-threaded application, all threads hold own non-blocking
> > connection into the remote database. Each thread is collecting data and
> > when a buffer is filled they copy data to remote DB as quick as possibl=
e.
> > Copying can run several times per minute. The postgresql related part o=
f
> > thread main loop is briefly shown in attached file main.c.
>
> > Most of the time copying works well but big delay sometimes occurred. I
> > captured pcap (see https://ibb.co/2MhmSTZ) and the delay is between
> getting
> > PGRES_COPY_IN from PQresultStatus function and pushing data. But I am
> sure
> > that commands PQputCopyData and PQputCopyEnd are called immediately aft=
er
> > client gets PQexec.
>
> > What am I missing? Is there some other command which we should use?
>
> Well, you didn't show us any of the relevant parts of your code.
> There is extra stuff you need to deal with if you're running the
> connection in nonblock mode.
>
> --- what are you doing when PQputCopyData returns zero?
>
> The documentation says you should wait for write-ready and try again.
> (You could just try again immediately, I suppose, but that would make it
> a busy-wait loop which doesn't seem like a great idea.)
>
> --- what are you doing after PQputCopyEnd?
>
> The documentation says
>
>     The result is 1 if the termination message was sent; or in nonblockin=
g
>     mode, this may only indicate that the termination message was
>     successfully queued. (In nonblocking mode, to be certain that the dat=
a
>     has been sent, you should next wait for write-ready and call PQflush,
>     repeating until it returns zero.) Zero indicates that the function
>     could not queue the termination message because of full buffers; this
>     will only happen in nonblocking mode. (In this case, wait for
>     write-ready and try the PQputCopyEnd call again.) If a hard error
>     occurs, -1 is returned; you can use PQerrorMessage to retrieve
>     details.
>
>                         regards, tom lane
>

--000000000000731032058e064652
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Tom,<br><br>I see we are not using copy in correct way.=
<br><br>Many thanks for your time.<br>Lukas<br></div><br><div class=3D"gmai=
l_quote"><div dir=3D"ltr" class=3D"gmail_attr">p=C3=A1 19. 7. 2019 v=C2=A00=
:26 odes=C3=ADlatel Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@s=
ss.pgh.pa.us</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">=3D?UTF-8?B?THVrw6HFoSBTb2JvdGth?=3D &lt;<a href=3D"mailto:sob=
otka.luk@gmail.com" target=3D"_blank">sobotka.luk@gmail.com</a>&gt; writes:=
<br>
&gt; I would like to ask you on correct way how to use pqlib for copying da=
ta.<br>
&gt; We tried to do it according documentation but some problems occurred.<=
br>
<br>
&gt; We have a multi-threaded application, all threads hold own non-blockin=
g<br>
&gt; connection into the remote database. Each thread is collecting data an=
d<br>
&gt; when a buffer is filled they copy data to remote DB as quick as possib=
le.<br>
&gt; Copying can run several times per minute. The postgresql related part =
of<br>
&gt; thread main loop is briefly shown in attached file main.c.<br>
<br>
&gt; Most of the time copying works well but big delay sometimes occurred. =
I<br>
&gt; captured pcap (see <a href=3D"https://ibb.co/2MhmSTZ" rel=3D"noreferre=
r" target=3D"_blank">https://ibb.co/2MhmSTZ</a>) and the delay is between g=
etting<br>
&gt; PGRES_COPY_IN from PQresultStatus function and pushing data. But I am =
sure<br>
&gt; that commands PQputCopyData and PQputCopyEnd are called immediately af=
ter<br>
&gt; client gets PQexec.<br>
<br>
&gt; What am I missing? Is there some other command which we should use?<br=
>
<br>
Well, you didn&#39;t show us any of the relevant parts of your code.<br>
There is extra stuff you need to deal with if you&#39;re running the<br>
connection in nonblock mode.<br>
<br>
--- what are you doing when PQputCopyData returns zero?<br>
<br>
The documentation says you should wait for write-ready and try again.<br>
(You could just try again immediately, I suppose, but that would make it<br=
>
a busy-wait loop which doesn&#39;t seem like a great idea.)<br>
<br>
--- what are you doing after PQputCopyEnd?<br>
<br>
The documentation says<br>
<br>
=C2=A0 =C2=A0 The result is 1 if the termination message was sent; or in no=
nblocking<br>
=C2=A0 =C2=A0 mode, this may only indicate that the termination message was=
<br>
=C2=A0 =C2=A0 successfully queued. (In nonblocking mode, to be certain that=
 the data<br>
=C2=A0 =C2=A0 has been sent, you should next wait for write-ready and call =
PQflush,<br>
=C2=A0 =C2=A0 repeating until it returns zero.) Zero indicates that the fun=
ction<br>
=C2=A0 =C2=A0 could not queue the termination message because of full buffe=
rs; this<br>
=C2=A0 =C2=A0 will only happen in nonblocking mode. (In this case, wait for=
<br>
=C2=A0 =C2=A0 write-ready and try the PQputCopyEnd call again.) If a hard e=
rror<br>
=C2=A0 =C2=A0 occurs, -1 is returned; you can use PQerrorMessage to retriev=
e<br>
=C2=A0 =C2=A0 details.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000731032058e064652--