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