MIME-Version: 1.0
From: =?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <sobotka.luk@gmail.com>
Date: Thu, 18 Jul 2019 15:57:55 +0200
Message-ID: 
 <CAJif3k+Q_nksEkS3SwpaButxxTcOkjgG91_sLLcc=986-2VZAg@mail.gmail.com>
Subject: Using COPY command in pqlib
To: pgsql-interfaces@postgresql.org
Content-Type: multipart/mixed; boundary="000000000000b0b57a058df500dc"
Precedence: bulk

--000000000000b0b57a058df500dc
Content-Type: multipart/alternative; boundary="000000000000b0b578058df500da"

--000000000000b0b578058df500da
Content-Type: text/plain; charset="UTF-8"

Hi guys,

I would like to ask you on correct way how to use pqlib for copying data.
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 possible.
Copying can run several times per minute. The postgresql related part of
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 after
client gets PQexec.

What am I missing? Is there some other command which we should use?

We are using PostgreSQL 9.6 (PostgreSQL 9.6.10 on x86_64-pc-linux-gnu,
compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit).

Best regards,
Lukas

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

<div dir=3D"ltr">Hi guys,<br><br>I would like to ask you on correct way how=
 to use pqlib for copying data. We tried to do it according documentation b=
ut some problems occurred.<br><br>We have a multi-threaded application, all=
 threads hold own non-blocking connection into the remote database. Each th=
read is collecting data and when a buffer is filled they copy data to remot=
e DB as quick as possible. Copying can run several times per minute. The po=
stgresql related part of thread main loop is briefly shown in attached file=
 main.c.<br><br>Most of the time copying works well but big delay sometimes=
 occurred. I captured pcap (see <a href=3D"https://ibb.co/2MhmSTZ">https://=
ibb.co/2MhmSTZ</a>) and the delay is between getting PGRES_COPY_IN from PQr=
esultStatus function and pushing data. But I am sure that commands PQputCop=
yData and PQputCopyEnd are called immediately after client gets PQexec.<br>=
<br>What am I missing? Is there some other command which we should use?<br>=
<br>We are using PostgreSQL 9.6 (PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, =
compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit).<br><br>Be=
st regards,<br>Lukas<br></div>

--000000000000b0b578058df500da--

--000000000000b0b57a058df500dc
Content-Type: text/x-csrc; charset="US-ASCII"; name="main.c"
Content-Disposition: attachment; filename="main.c"
Content-Transfer-Encoding: base64
Content-ID: <f_jy8qncs30>
X-Attachment-Id: f_jy8qncs30

Zm9yICg7OykgewoKCS8vIGNvbGxlY3RpbmcgZGF0YSB1bnRpbCBidWZmZXIgaXMgZmlsbGVkCgoJ
UEdyZXN1bHQqIHJlczsKCgkvLyB3YWl0IHVudGlsIGNvbm5lY3Rpb24gaXMgcmVhZHkKCXdoaWxl
ICgocmVzID0gUFFnZXRSZXN1bHQoY29ubikpICE9IDApIHsKCQljb25zdCBFeGVjU3RhdHVzVHlw
ZSBzID0gUFFyZXN1bHRTdGF0dXMocmVzKTsKCQkvLyBlcnJvcnMgaGFuZGxpbmcKCQlQUWNsZWFy
KHJlcyk7CgkJdXNsZWVwKDEwMDApOwoJfQoKCS8vIGVzdGFibGlzaGVkIENPUFkKCXJlcyA9IFBR
ZXhlYyhjb25uLCBxdWVyeSk7CglzdGF0dXMgPSBQUXJlc3VsdFN0YXR1cyhyZXMpOwoJaWYgKHN0
YXR1cyA9PSBQR1JFU19DT1BZX0lOKSB7CgoJCS8vIHNlbmQgZGF0YQoJCWNvbnN0IGludCBjb3B5
X3Jlc3VsdCA9IFBRcHV0Q29weURhdGEoY29ubiwgYnVmZmVyLCBzaXplKTsKCQlpZiAocmVzID09
IDEpIHsKCQkJY29uc3QgaW50IGNvcHlfZW5kX3Jlc3VsdCA9IFBRcHV0Q29weUVuZChjb25uLCBO
VUxMKTsKCQkJLy8gZXJyb3JzIGhhbmRsaW5nCgkJfQoJfQoKCVBRY2xlYXIocmVzKTsKfQo=
--000000000000b0b57a058df500dc--