Message-ID: <469E68E2.9080206@ifi.uio.no>
Date: Wed, 18 Jul 2007 21:24:18 +0200
From: Thomas Finneid <tfinneid@student.matnat.uio.no>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
To: Kalle Hallivuori <kato@iki.fi>
CC: pgsql-performance@postgresql.org
Subject: Re: improvement suggestions for performance design
References: <40362.134.32.140.234.1183637748.squirrel@webmail.uio.no>	
	<468CEB32.30109@enterprisedb.com>	
	<40891.134.32.140.234.1183642557.squirrel@webmail.uio.no>	
	<468D3AF7.4070505@enterprisedb.com> <468D6F68.7090903@ifi.uio.no>	
	<c637d8bb0707060430x1656a796y728c88e9e2103ea5@mail.gmail.com>	
	<4690B725.5070303@ifi.uio.no>
	<c637d8bb0707080925u4cd0871dmced4458d19b10c1@mail.gmail.com>
In-Reply-To: <c637d8bb0707080925u4cd0871dmced4458d19b10c1@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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