Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.

I have enabled the autocommit feature of psycopg2, and removed all the
transactions in source code, also changed the sql statement to the
following:

insert into ACCOUNT(HOME)
select "v1" as HOME
where not exists (select 1 from ACCOUNT where HOME = "v1")

Surprisingly, I still got the unique constraint violation error, but much
fewer than before ( only 3 during the last 3 days).
Any suggestions? Could it be a bug in psycopg2 or postgresql?

Thanks
-Yao

On Wed, Jan 4, 2012 at 1:11 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Jan 3, 2012 at 1:42 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> > On 3 Jan 2012, at 5:20, 邓尧 wrote:
> >
> >> Hi,
> >>
> >> I'm new to pgsql, I need the do something like the "INSERT IGNORE" in
> mysql. After some searching I got a solution, which is adding a "do instead
> nothing" rule to the corresponding table, but it fails sometimes.
> >
> > Yeah, if a concurrent transaction tries to create the same record, one
> of the transactions is going to find that it already exists on transaction
> commit. An INSERT-rule is not going to protect you against that.
>
> It will if you lock the table first in the same transaction...note
> this will greatly hurt concurrency and you have to watch for
> deadlocks.
>
> INSERT...SELECT..WHERE is going to be vastly superior to a rule based
> approach obviously.
>
> merlin
>