Re: [HACKERS] libpq PQexec call of COPY

[Peter Eisentraut]

John Coers writes:

 Hi,

 My generic problem is performance when copying very large amounts of data to a db 
from multiple clients.

 I am writing a C program on Linux Redhat6.2 that accesses a 7.0.3 database using 
libpq.  I
 would like to be able to do a printf through STDOUT (or another file pointer) TO the 
database via a
 PQexec call of COPY.   Something like this would be ideal:

 PQexec(conn, "COPY moncoverage from STDOUT");

 I have been unable to figure out if this is possible.  I understand that I can do 
this via stdin,
 but I don't want the user to make the entry, I want the executable to do it and 
still enjoy the
 performance of the COPY command.

Read the libpq chapter in the Programmer's Guide and look into
src/bin/psql/copy.c for information and examples of using COPY through
libpq.  Yes, it's possible, but you need to use special API calls.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] libpq PQexec call of COPY

[John Coers]

Hi,

My generic problem is performance when copying very large amounts of data to a db from 
multiple clients.

I am writing a C program on Linux Redhat6.2 that accesses a 7.0.3 database using 
libpq.  I
would like to be able to do a printf through STDOUT (or another file pointer) TO the 
database via a 
PQexec call of COPY.   Something like this would be ideal:

PQexec(conn, "COPY moncoverage from STDOUT");

I have been unable to figure out if this is possible.  I understand that I can do this 
via stdin, 
but I don't want the user to make the entry, I want the executable to do it and still 
enjoy the 
performance of the COPY command.

I also understand that I can open a pipe to psql and do it through there like this:

FILE *ofp = popen("psql -a -c 'COPY tablename from stdin' -d dbname -h host","w");
 
for(i=0;i15000;i++)
   fprintf(ofp,"%s\n",row[i]);
 
fprintf(ofp,"\\.\n");
pclose(ofp);


However, performance is extremely important to me in this application.  I already have 
a connection
open to the db in the C executable to do 10 inserts, so it I would like to go ahead 
and use that
connection to perform my PQexec(conn,'COPY...') to reduce connection overhead.  Also, 
I am assuming that
the PQexec call makes a more efficient connection that the popen to psql and COPY 
scheme.  During trial
runs my server maintains 34 postmaster processes -- there has to be a better way to do 
this.

Any help would be greatly appreciated.  Please let me know if this is the wrong place 
to post this.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly