Message-ID: <3AD2227F.E14D38E0@intrinsity.com>
Date: Mon, 09 Apr 2001 15:58:39 -0500
From: John Coers <coers@intrinsity.com>
Reply-To: coers@intrinsity.com
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
Subject: libpq PQexec call of COPY
References: <3ACB8E7F.C12A50CA@intrinsity.com> <9ag5q6$tuk$1@news.tht.net>
	<3AD1D6B6.1617F153@intrinsity.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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;i<15000;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.