Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.
| От | 邓尧 |
|---|---|
| Тема | Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added. |
| Дата | |
| Msg-id | CAOb3iuj9Dg9sGF1hozGGcEdfYa=2BAfPvx9F1Dgm1i1MA02jjw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added. (Merlin Moncure <mmoncure@gmail.com>) |
| Ответы |
Re: Duplicated entries are not ignored even if a "do
instead nothing" rule is added.
|
| Список | pgsql-general |
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:It will if you lock the table first in the same transaction...note
> 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
В списке pgsql-general по дате отправления: