public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lukáš Sobotka <[email protected]>
To: [email protected]
Subject: Using COPY command in pqlib
Date: Thu, 18 Jul 2019 15:57:55 +0200
Message-ID: <CAJif3k+Q_nksEkS3SwpaButxxTcOkjgG91_sLLcc=986-2VZAg@mail.gmail.com> (raw)

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);
}

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Using COPY command in pqlib
  In-Reply-To: <CAJif3k+Q_nksEkS3SwpaButxxTcOkjgG91_sLLcc=986-2VZAg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox