Re: Using COPY command in pqlib

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> =?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <sobotka(dot)luk(at)gmail(dot)com> 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
>