We ran into the need to use COPY, but our application is also in Java.
We wrote a JNI bridge to a C++ routine that uses the libpq library to do
the COPY. The coding is a little bit weird, but not too complicated -
the biggest pain in the neck is probably getting it into your build
system.

Here's the Java tutorial on JNI:
http://java.sun.com/docs/books/tutor...pts/index.html

Hope that helps!

- DAP

>-----Original Message-----
>From: pgsql-performance-owner@postgresql.org
>[mailtogsql-performance-owner@postgresql.org] On Behalf Of
>Christopher Kings-Lynne
>Sent: Monday, May 02, 2005 11:11 AM
>To: tim@se.linux.org
>Cc: pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] batch inserts are "slow"
>
>> conn.setAutoCommit(false);
>> pst = conn.prepareStatement("INSERT INTO tmp (...) VALUES
>(?,?)"); for
>> (int i = 0; i < len; i++) {
>> pst.setInt(0, 2);
>> pst.setString(1, "xxx");
>> pst.addBatch();
>> }
>> pst.executeBatch();
>> conn.commit();
>>
>> This snip takes 1.3 secs in postgresql. How can I lower that?
>
>You're batching them as one transaction, and using a prepared
>query both of which are good. I guess the next step for a
>great performance improvement is to use the COPY command.
>However, you'd have to find out how to access that via Java.
>
>I have a nasty suspicion that the release JDBC driver doesn't
>support it and you may have to apply a patch.
>
>Ask on pgsql-jdbc@postgresql.org perhaps.
>
>Chris
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to
>majordomo@postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi, all,

David Parker wrote:
> We ran into the need to use COPY, but our application is also in Java.
> We wrote a JNI bridge to a C++ routine that uses the libpq library to do
> the COPY. The coding is a little bit weird, but not too complicated -
> the biggest pain in the neck is probably getting it into your build
> system.

There are several hacks floating around that add COPY capabilities to
the pgjdbc driver. As they all are rather simple hacks, they have not
been included in the cvs yet, but they tend to work fine.

Markus


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

People,

> There are several hacks floating around that add COPY capabilities to
> the pgjdbc driver. As they all are rather simple hacks, they have not
> been included in the cvs yet, but they tend to work fine.

FWIW, Dave Cramer just added beta COPY capability to JDBC. Contact him on
the JDBC list for details; I think he needs testers.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Tue, 3 May 2005, Josh Berkus wrote:

> > There are several hacks floating around that add COPY capabilities to
> > the pgjdbc driver. As they all are rather simple hacks, they have not
> > been included in the cvs yet, but they tend to work fine.
>
> FWIW, Dave Cramer just added beta COPY capability to JDBC. Contact him on
> the JDBC list for details; I think he needs testers.
>

I believe Dave has remerged a patch for COPY I posted over a year ago, but
he has not yet published it. I would guess it has the same bugs as the
original (transaction + error handling) and will meet the same objections
that kept the original patch out of the driver in the first place (we want
a friendlier API than just a data stream).

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Testing list access

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Kris is correct,

This code was not added or even submitted to CVS. The purpose of this
was to work
out the bugs with people who are actually using copy.

The api is a separate issue however. There's no reason that copy can't
support more
than one api.

Dave

Kris Jurka wrote:

>On Tue, 3 May 2005, Josh Berkus wrote:
>
>
>
>>>There are several hacks floating around that add COPY capabilities to
>>>the pgjdbc driver. As they all are rather simple hacks, they have not
>>>been included in the cvs yet, but they tend to work fine.
>>>
>>>
>>FWIW, Dave Cramer just added beta COPY capability to JDBC. Contact him on
>>the JDBC list for details; I think he needs testers.
>>
>>
>>
>
>I believe Dave has remerged a patch for COPY I posted over a year ago, but
>he has not yet published it. I would guess it has the same bugs as the
>original (transaction + error handling) and will meet the same objections
>that kept the original patch out of the driver in the first place (we want
>a friendlier API than just a data stream).
>
>Kris Jurka
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly