Re: improvement suggestions for performance design - Mailing list pgsql-performance

Kalle Hallivuori wrote:
> Hi.
>
> 2007/7/8, Thomas Finneid <tfinneid@student.matnat.uio.no>:
>>
>> Kalle Hallivuori wrote:
>>
>>  > COPY is plentitudes faster than INSERT:
>>  > http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>>  >
>>  > If you can't just push the data straight into the final table with
>>  > COPY, push it into a temporary table that you go through with the
>>  > database procedure.
>>  >
>>  > Shameless plug: If you use Java and miss COPY functionality in the
>>  > driver, it's available at
>>  >
>>  > http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
>>  >
>>  > I was able to practically nullify time spent inserting with that.
>>
>> Interresting, I will definately have a look at it.
>> What is the maturity level of the code at this point? and what is
>> potentially missing to bring it up to production quality? (stability is
>> of the utmost importance in my usage scenario.)
>
> It's my third implementation, based on earlier work by Kris Jurka, a
> maintainer of the JDBC driver. (It is really quite short so it's easy
> to keep it clear.) I consider it mature enough to have accommodated it
> as part of an upcoming large application, but I'd really like to hear
> others' opinions. Documentation I should add one of these days, maybe
> even rewrite the javadoc.

Hi I have tested your COPY patch (actually I tested
postgresql-jdbc-8.2-505-copy-20070716.jdbc3.jar) and it is really fast,
actually just as fast as serverside COPY (boths tests was performed on
local machine).

This means I am interrested in using it in my project, but I have some
concerns that needs to be adressed, (and I am prepared to help in any
way I can). The following are the concerns I have

- While testing I got some errors, which needs to be fixed (detailed below)
- The patch must be of production grade quality
- I would like the patch to be part of the official pg JDBC driver.


The error I got the most is :

This command runs a single run, single thread and generates 10000 rows
of data

tofi@duplo:~/svn/pores$ java -server -Xms20m -Xmx256m -cp
/usr/java/jdk1.5.0_06/jre/lib/rt.jar:.:src/:test/:conf/:lib/postgresql-jdbc-8.2-505-copy-20070716.jdbc3.jar
wg.daemon.Main -m SINGLE_WRITE -t 1 -r 1 -c 10000 -p CPBulk
Initialising connection...
Performing insert...
Build bulk data time: 0s 211ms
toString() bulk data time: 0s 4ms
time: 0s 205ms
org.postgresql.util.PSQLException: Unexpected command completion while
copying: COPY
         at
org.postgresql.core.v3.QueryExecutorImpl.executeCopy(QueryExecutorImpl.java:706)
         at org.postgresql.copy.CopyManager.copyIntoDB(CopyManager.java:50)
         at org.postgresql.copy.CopyManager.copyIntoDB(CopyManager.java:37)
         at wg.storage.pores1.CPBulk.addAttributes(CPBulk.java:72)
         at wg.daemon.Daemon.run(Daemon.java:57)
tofi@duplo:~/svn/pores$ ls -al lib/


regards

thomas