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

[邓尧 <torshie@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

On Tue, Jan 3, 2012 at 1:42 AM, Alban Hertroys <haramrae@xxxxxxxxx> 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