From: Kris Jurka <books@ejurka.com>
To: Mischa Sandberg <mischa.sandberg@telus.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: COPY vs INSERT
Date: Thu, 5 May 2005 04:15:40 -0500 (EST)
Message-ID: <Pine.BSO.4.56.0505050406080.14495@leary.csoft.net> (raw)
In-Reply-To: <1115268106.4279a40a7481a@webmail.telus.net>
References: <OF93C70832.2AC188CE-ON85256FF6.0056B587-85256FF6.005774C2@us.ibm.com>
<21154.1115136741@sss.pgh.pa.us>
<1115146397.4277c89dc37b4@webmail.telus.net>
<1115246490.1709.233393619@webmail.messagingengine.com>
<1115248292.427956a468a55@webmail.telus.net>
<Pine.BSO.4.56.0505041817480.2683@leary.csoft.net>
<1115268106.4279a40a7481a@webmail.telus.net>
On Wed, 4 May 2005, Mischa Sandberg wrote:
> Quoting Kris Jurka <books@ejurka.com>:
>
> > Not true. A client may send any number of Bind/Execute messages on
> > a prepared statement before a Sync message.
> Hunh. Interesting optimization in the JDBC driver. I gather it is
> sending a string of (;)-separated inserts.
No, it uses the V3 protocol and a prepared statement and uses
Bind/Execute, as I mentioned.
> Sounds like efficient-but-risky stuff we did for ODBC drivers at Simba
> ... gets interesting when one of the insert statements in the middle
> fails.
When running inside a transaction (as you really want to do anyway when
bulk loading) it is well defined, it is a little odd for auto commit mode
though. In autocommit mode the transaction boundary is at the Sync
message, not the individual Execute messages, so you will get some
rollback on error. The JDBC spec is poorly defined in this area, so we
can get away with this.
> Good to know. Hope that the batch size is parametric, given that
> you can have inserts with rather large strings bound to 'text' columns
> in PG --- harder to identify BLOBs when talking to PG, than when talking
> to MSSQL/Oracle/Sybase.
The batch size is not a parameter and I don't think it needs to be. The
issue of filling both sides of network buffers and deadlocking only needs
to be avoided on one side. The response to an insert request is small and
not dependent on the size of the data sent, so we can send as much as we
want as long as the server doesn't send much back to us.
Kris Jurka
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: books@ejurka.com, mischa.sandberg@telus.net
Subject: Re: COPY vs INSERT
In-Reply-To: <Pine.BSO.4.56.0505050406080.14495@leary.csoft.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox