libpq PQexec call of COPY

From: John Coers <coers(at)intrinsity(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: libpq PQexec call of COPY
Date: 2001-04-09 20:58:39
Message-ID: 3AD2227F.E14D38E0@intrinsity.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-hackers

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.

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Mark Butler 2001-04-10 02:23:03 Online web page needs correction
Previous Message Bruce Momjian 2001-04-09 19:27:34 Re: IPC configuration in the FAQ

Browse pgsql-hackers by date

  From Date Subject
Next Message Homayoun Yousefi'zadeh 2001-04-09 22:33:19 JDBC and Perl compiling problems w/ postgresql-7.1rc4
Previous Message Nathan Myers 2001-04-09 20:30:26 Re: Truncation of char, varchar types