Thread: Error on PQputline()
The contents of the error message are:
conn->errorMessage.data 0x00312440 "pqFlush() -- couldn't send data:
errno=0
No error A non-blocking socket operation could not be completed
immediately.
for this:
if (PQputline(conn, pszBCPdata[i++]) == EOF) printf("Error inserting data on row %d\n",
i-1);
What is the correct recovery action? Do I send the same buffer again?
"Dann Corbit" <DCorbit@connx.com> writes:
> The contents of the error message are:
> conn->errorMessage.data 0x00312440 "pqFlush() -- couldn't send data:
> errno=0
> No error A non-blocking socket operation could not be completed
> immediately.
You're running libpq with the nonblocking mode selected?
> What is the correct recovery action?
Redesign libpq's nonblock mode :-(. It's a mess; a quick hack that
doesn't even try to cover all cases, and is unreliable in the ones it
does cover. You can find my previous rants on the subject in the
archives from a couple years back (around Jan '00 I believe). IMHO
we should never have accepted that patch at all.
Short of that, don't use the COPY code with nonblock.
regards, tom lane
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, May 17, 2002 4:10 PM
> To: Dann Corbit
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Error on PQputline()
>
>
> "Dann Corbit" <DCorbit@connx.com> writes:
> > The contents of the error message are:
> > conn->errorMessage.data 0x00312440 "pqFlush() --
> couldn't send data:
> > errno=0
> > No error A non-blocking socket operation could not be completed
> > immediately.
>
> You're running libpq with the nonblocking mode selected?
Actually no. It should be the default mode for a connection made by
PQconnectdb(). That's what made the error so puzzling.
> > What is the correct recovery action?
>
> Redesign libpq's nonblock mode :-(. It's a mess; a quick hack that
> doesn't even try to cover all cases, and is unreliable in the ones it
> does cover. You can find my previous rants on the subject in the
> archives from a couple years back (around Jan '00 I believe). IMHO
> we should never have accepted that patch at all.
>
> Short of that, don't use the COPY code with nonblock.
I am trying to figure out if it is faster to bulk copy from a file on
the server or using an API from the client. It boils down to this:
"Would it be faster to write a file to disk and read it again on the
local host for the server or to send the calls via libpq client
messages?"
It could be that the TCP/IP overhead exceeds the overhead of writing the
file to disk and reading it again.
I have a data statement (in test.h) that consists of 1.6 million rows of
data to spin into the database.
Here is the complete program:
#include <windows.h>
#include <stdlib.h>
#include <time.h>
#include "libpq-fe.h"
#include "glob.h" /* member variables in the objects */
#include "test.h"
int init_comm(void)
{ WORD wVersionRequested; WSADATA wsaData; int err;
wVersionRequested = MAKEWORD(2, 2);
err = WSAStartup(wVersionRequested, &wsaData); if (err != 0) { /* Tell the user that we could not find a
usable*/ /* WinSock DLL. */ return 0; } return 1;
}
void ProcessTuples(void);
int ExecuteImmediate(char *command, Qtype q_t)
{ int problem = 0;
#ifdef _DEBUG printf("%s\n", command);
#endif result = PQexec(conn, command); switch (rc = PQresultStatus(result)) {
/* We should never actually call this. Left in for debugging...
*/ /* All tuple processing is handled low-level to pass data back
to * CONNX */
case PGRES_TUPLES_OK: /* Data set successfully created */
#ifdef _DEBUG printf("#rows affected %s\n", PQcmdTuples(result));
#endif ProcessTuples(); break; case PGRES_EMPTY_QUERY: /* Empty query supplied -- do nothing...
*/ case PGRES_COMMAND_OK: /* Query succeeds, but returns no
results */ /* If we did a select, we should (at least) have a result set of * empty tuples. */ if
(q_t== QUERY_TYPE_SELECT) problem = 1; break; case PGRES_BAD_RESPONSE: case PGRES_NONFATAL_ERROR:
casePGRES_FATAL_ERROR: { problem = 1; } } if (q_t == QUERY_TYPE_INSERT) { InsertedOID =
PQoidValue(result);
#ifdef _DEBUG printf("OID of inserted row is %lu\n", (unsigned long)
InsertedOID);
#endif } PQclear(result); return problem;
}
void HandleProblem(void)
{ const char *m1 = PQresStatus(rc); const char *m2 = PQresultErrorMessage(result);
#ifdef __cplusplus String err = m1; err = err + m2; throw Mcnew CPOSTGRESQLException(conn, rc,
(LPCSTR)err,
szSQLState);
#endif
#ifdef _DEBUG printf("status is %s\n", m1); printf("result message: %s\n", m2);
#endif
}
void BeginTrans(void)
{ int problem; problem = ExecuteImmediate("BEGIN work", QUERY_TYPE_TRANSACT); if (problem)
HandleProblem();
}
void CommitTrans(void)
{ int problem;
problem = ExecuteImmediate("COMMIT work", QUERY_TYPE_TRANSACT); if (problem) HandleProblem();
}
void RollbackTrans(void)
{ int problem;
problem = ExecuteImmediate("ROLLBACK work", QUERY_TYPE_TRANSACT); if (problem) HandleProblem();
}
void ProcessTuples()
{ nrows = PQntuples(result); nfields = PQnfields(result);
#ifdef _DEBUG printf("number of rows returned = %d\n", nrows); printf("number of fields returned = %d\n", nfields);
#endif for (r = 0; r < nrows; r++) { for (n = 0; n < nfields; n++) printf(" %s = %s(%d),",
PQfname(result, n), PQgetvalue(result, r, n), PQgetlength(result, r, n));
printf("\n"); }
}
static long cursor_number = 0;
int main(void)
{ int problem; int i = 0;
struct tm *newtime; time_t aclock;
if (init_comm()) { conn = PQconnectdb("dbname=connxdatasync host=dannfast"); if (PQstatus(conn) ==
CONNECTION_OK){ char insert_sql[256]; printf("connection made\n"); } else {
printf("connection failed\n"); return EXIT_FAILURE; }
puts("DROP TABLE cnx_ds_sis_bill_detl_tb started"); problem = ExecuteImmediate("DROP TABLE
cnx_ds_sis_bill_detl_tb",
QUERY_TYPE_OTHER); if (problem) HandleProblem();
puts("DROP TABLE cnx_ds_sis_bill_detl_tb finished"); puts("CREATE TABLE cnx_ds_sis_bill_detl_tb started");
problem = ExecuteImmediate("CREATE TABLE cnx_ds_sis_bill_detl_tb
( extr_stu_id char(10), term_cyt char(5), subcode char(5), tran_seq
int2, crc int8)", QUERY_TYPE_OTHER); if (problem) HandleProblem();
puts("CREATE TABLE cnx_ds_sis_bill_detl_tb finished"); puts("going to start bulk copy...");
time(&aclock); newtime = localtime(&aclock); puts(asctime(newtime)); result = PQexec(conn,
"COPYcnx_ds_sis_bill_detl_tb FROM STDIN
DELIMITERS '|'"); problem = 0; switch (rc = PQresultStatus(result)) { case PGRES_BAD_RESPONSE:
casePGRES_NONFATAL_ERROR: case PGRES_FATAL_ERROR: { problem = 1; } }
if (problem) HandleProblem();
puts("done with initialization...");
while (pszBCPdata[i]) { if (PQputline(conn, pszBCPdata[i++]) == EOF) printf("Error inserting
dataon row %d\n",
i-1); }
PQputline(conn, "\\.\n");
PQendcopy(conn);
puts("finished with bulk copy...");
time(&aclock); newtime = localtime(&aclock); puts(asctime(newtime));
return EXIT_SUCCESS; } puts("initialization of winsock failed."); return EXIT_FAILURE;
}
"Dann Corbit" <DCorbit@connx.com> writes:
>> You're running libpq with the nonblocking mode selected?
> Actually no. It should be the default mode for a connection made by
> PQconnectdb(). That's what made the error so puzzling.
I'm confused too. For starters, I cannot find that error message
string about 'A non-blocking socket operation could not be completed
immediately' anywhere. Got any idea what's producing that? Exactly
which version of libpq are you using, anyway?
> "Would it be faster to write a file to disk and read it again on the
> local host for the server or to send the calls via libpq client
> messages?"
Good question. I'd recommend the messaging approach since it eliminates
lots of headaches about file access privileges and so forth. But on
some platforms the overhead could be high.
regards, tom lane
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, May 17, 2002 4:38 PM
> To: Dann Corbit
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Error on PQputline()
>
>
> "Dann Corbit" <DCorbit@connx.com> writes:
> >> You're running libpq with the nonblocking mode selected?
>
> > Actually no. It should be the default mode for a connection made by
> > PQconnectdb(). That's what made the error so puzzling.
>
> I'm confused too. For starters, I cannot find that error message
> string about 'A non-blocking socket operation could not be completed
> immediately' anywhere. Got any idea what's producing that? Exactly
> which version of libpq are you using, anyway?
7.1.3. Sorry for running on fossil PostgreSQL.
/* ---------------------------------------------------------------------
*/
/* pqFlush: send any data waiting in the output buffer*/
int
pqFlush(PGconn *conn)
{char *ptr = conn->outBuffer;int len = conn->outCount;
if (conn->sock < 0){ printfPQExpBuffer(&conn->errorMessage, "pqFlush() --
connection not open\n"); return EOF;}
/* * don't try to send zero data, allows us to use this function
without * too much worry about overhead */if (len == 0) return (0);
/* while there's still data to send */while (len > 0){ /* Prevent being SIGPIPEd if backend has closed the
connection. */
#ifndef WIN32 pqsigfunc oldsighandler = pqsignal(SIGPIPE,
SIG_IGN);
#endif
int sent;
#ifdef USE_SSL if (conn->ssl) sent = SSL_write(conn->ssl, ptr, len); else
#endif sent = send(conn->sock, ptr, len, 0);
#ifndef WIN32 pqsignal(SIGPIPE, oldsighandler);
#endif
if (sent < 0) {
/* * Anything except EAGAIN or EWOULDBLOCK is
trouble. If it's * EPIPE or ECONNRESET, assume we've lost the
backend * connection permanently. */ switch (errno) {
#ifdef EAGAIN case EAGAIN: break;
#endif
#if defined(EWOULDBLOCK) && (!defined(EAGAIN) || (EWOULDBLOCK !=
EAGAIN)) case EWOULDBLOCK: break;
#endif case EINTR: continue;
case EPIPE:
#ifdef ECONNRESET case ECONNRESET:
#endif
printfPQExpBuffer(&conn->errorMessage,
"pqFlush() -- backend closed the channel unexpectedly.\n"
"\tThis probably means the backend terminated abnormally" " before or while
processing the request.\n");
/* * We used to close the socket
here, but that's a bad * idea since there might be
unread data waiting * (typically, a NOTICE message
from the backend * telling us it's committing
hara-kiri...). Leave * the socket open until
pqReadData finds no more data * can be read. */ return EOF;
/*
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvv
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!
*/ default:
printfPQExpBuffer(&conn->errorMessage, "pqFlush() -- couldn't send
data: errno=%d\n%s\n",
errno, strerror(errno)); /* We don't assume it's a fatal
error... */ return EOF;
/*
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!
*/ } } else { ptr += sent; len -= sent; }
if (len > 0) { /* We didn't send it all, wait till we can send
more */
/* * if the socket is in non-blocking mode we may
need to abort * here */
#ifdef USE_SSL /* can't do anything for our SSL users yet */ if (conn->ssl == NULL) {
#endif if (pqIsnonblocking(conn)) { /* shift the contents of the
buffer */ memmove(conn->outBuffer, ptr,
len); conn->outCount = len; return EOF; }
#ifdef USE_SSL }
#endif
if (pqWait(FALSE, TRUE, conn)) return EOF; }}
conn->outCount = 0;
if (conn->Pfdebug) fflush(conn->Pfdebug);
return 0;
}
> > "Would it be faster to write a file to disk and read it again on the
> > local host for the server or to send the calls via libpq client
> > messages?"
>
> Good question. I'd recommend the messaging approach since it
> eliminates
> lots of headaches about file access privileges and so forth. But on
> some platforms the overhead could be high.
>
> regards, tom lane
>
"Dann Corbit" <DCorbit@connx.com> writes:
>> I'm confused too. For starters, I cannot find that error message
>> string about 'A non-blocking socket operation could not be completed
>> immediately' anywhere. Got any idea what's producing that? Exactly
>> which version of libpq are you using, anyway?
> 7.1.3. Sorry for running on fossil PostgreSQL.
No such string in 7.1.3 either.
> printfPQExpBuffer(&conn->errorMessage,
> "pqFlush() -- couldn't send
> data: errno=%d\n%s\n",
> errno, strerror(errno));
> /* We don't assume it's a fatal
> error... */
> return EOF;
> /*
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> ^^^^^^^
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> !!!!!!!
> */
Unless your strerror is really weird, that message is only going to have
produced "pqFlush() -- couldn't send data: errno=0\nNo error\n".
The bit about a non-blocking socket could not have come from strerror
AFAICS; it hasn't got enough context to know that.
regards, tom lane