From: Zdravko Balorda <zdravko.balorda@siix.com>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ignore unique violation OR check row exists
Date: Wed, 04 Jan 2012 13:33:55 +0100
Message-ID: <4F044733.6020007@siix.com> (raw)
In-Reply-To: <CAEV0TzCGTt4R-Ca+v1bwXXhLQEfU5v-m7bGebkRucSj8S9t8YQ@mail.gmail.com>
References: <1325624070446-5117916.post@n5.nabble.com>
<20120104090838.GA6039@tux>
<4F042291.6040903@siix.com>
<CAEV0TzCGTt4R-Ca+v1bwXXhLQEfU5v-m7bGebkRucSj8S9t8YQ@mail.gmail.com>
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
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-sql@postgresql.org
Cc: zdravko.balorda@siix.com, sgendler@ideasculptor.com
Subject: Re: ignore unique violation OR check row exists
In-Reply-To: <4F044733.6020007@siix.com>
* 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