public inbox for [email protected]
help / color / mirror / Atom feedUsing COPY command in pqlib
3+ messages / 2 participants
[nested] [flat]
* Using COPY command in pqlib
@ 2019-07-18 13:57 Lukáš Sobotka <[email protected]>
2019-07-18 22:26 ` Re: Using COPY command in pqlib Tom Lane <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Lukáš Sobotka @ 2019-07-18 13:57 UTC (permalink / raw)
To: pgsql-interfaces
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
Attachments:
[text/x-csrc] main.c (566B, ../../CAJif3k+Q_nksEkS3SwpaButxxTcOkjgG91_sLLcc=986-2VZAg@mail.gmail.com/3-main.c)
download | inline:
for (;;) {
// collecting data until buffer is filled
PGresult* res;
// wait until connection is ready
while ((res = PQgetResult(conn)) != 0) {
const ExecStatusType s = PQresultStatus(res);
// errors handling
PQclear(res);
usleep(1000);
}
// established COPY
res = PQexec(conn, query);
status = PQresultStatus(res);
if (status == PGRES_COPY_IN) {
// send data
const int copy_result = PQputCopyData(conn, buffer, size);
if (res == 1) {
const int copy_end_result = PQputCopyEnd(conn, NULL);
// errors handling
}
}
PQclear(res);
}
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using COPY command in pqlib
2019-07-18 13:57 Using COPY command in pqlib Lukáš Sobotka <[email protected]>
@ 2019-07-18 22:26 ` Tom Lane <[email protected]>
2019-07-19 10:34 ` Re: Using COPY command in pqlib Lukáš Sobotka <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2019-07-18 22:26 UTC (permalink / raw)
To: Lukáš Sobotka <[email protected]>; +Cc: pgsql-interfaces
=?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <[email protected]> writes:
> 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?
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 nonblocking
mode, this may only indicate that the termination message was
successfully queued. (In nonblocking mode, to be certain that the data
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using COPY command in pqlib
2019-07-18 13:57 Using COPY command in pqlib Lukáš Sobotka <[email protected]>
2019-07-18 22:26 ` Re: Using COPY command in pqlib Tom Lane <[email protected]>
@ 2019-07-19 10:34 ` Lukáš Sobotka <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Lukáš Sobotka @ 2019-07-19 10:34 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-interfaces
Hi Tom,
I see we are not using copy in correct way.
Many thanks for your time.
Lukas
pá 19. 7. 2019 v 0:26 odesílatel Tom Lane <[email protected]> napsal:
> =?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <[email protected]> writes:
> > 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?
>
> 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 nonblocking
> mode, this may only indicate that the termination message was
> successfully queued. (In nonblocking mode, to be certain that the data
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-07-19 10:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-18 13:57 Using COPY command in pqlib Lukáš Sobotka <[email protected]>
2019-07-18 22:26 ` Tom Lane <[email protected]>
2019-07-19 10:34 ` Lukáš Sobotka <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox