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

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,
whichis 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
alreadyexists 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