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/i.../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.

You can use COPY as is on the server side without the patch, but then
you need to get the data as CSV or TSV files onto the database server
machine, and use db superuser privileges to import it. My patch just
adds the ability to feed data from client with normal user privileges.

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/i.../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.executeCo py(QueryExecutorImpl.java:706)
at org.postgresql.copy.CopyManager.copyIntoDB(CopyMan ager.java:50)
at org.postgresql.copy.CopyManager.copyIntoDB(CopyMan ager.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


---------------------------(end of broadcast)---------------------------
TIP 1: 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

Hi all!

2007/7/18, Thomas Finneid <tfinneid@student.matnat.uio.no>:
> 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).

Happy to hear there's interest toward this solution.

> 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.

Definitely agreed, those are my requirements as well. We can discuss
bug fixing among ourselves; new versions I'll announce on pgsql-jdbc
list.

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate