Message-ID: <4F044733.6020007@siix.com>
Date: Wed, 04 Jan 2012 13:33:55 +0100
From: Zdravko Balorda <zdravko.balorda@siix.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20110906)
MIME-Version: 1.0
To: Samuel Gendler <sgendler@ideasculptor.com>
CC: pgsql-sql@postgresql.org
Subject: Re: ignore unique violation OR check row exists
References: <1325624070446-5117916.post@n5.nabble.com>
	<20120104090838.GA6039@tux>	<4F042291.6040903@siix.com>
	<CAEV0TzCGTt4R-Ca+v1bwXXhLQEfU5v-m7bGebkRucSj8S9t8YQ@mail.gmail.com>
In-Reply-To: 
 <CAEV0TzCGTt4R-Ca+v1bwXXhLQEfU5v-m7bGebkRucSj8S9t8YQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Samuel Gendler wrote:
> 
> 
> On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda 
> <zdravko.balorda@siix.com <mailto:zdravko.balorda@siix.com>> wrote:
>     Take it out of transaction. Why is there a transaction in the first
>     place?
>     If transaction is needed, ok, but take these inserts out and
>     everything will
>     work as it should. Ignoring UNIQUE VIOLATION or any other error
>     defeats the very
>     purpose of transaction. That's why you can't ignore it.
> 
> 
> Unfortunately, bulk inserts are much slower when they don't occur in a 
> transaction.  Try inserting 1 million rows with auto commit enabled vs 1 
> million rows in 1 transaction, or even 10 or 100 transactions. The 
> difference is enormous.  The bulk insert into an unconstrained table and 
> then pulling just the new rows over into the destination table in a 
> single transaction is definitely the most effective way to do this.
> 

I do a lot of bulk inserts. What helps is dropping indexes before insert
and recreating it after. Probably you need to better organize data to
avoid having primary keys on a table with a lots of data.

Zdravko