Re: Weird insert issue

Thanks for the clarification guys! That was not the behavior I was
expecting (as you can tell), so I learned something new today. :)

In my case I don't want an update (there are only the 2 fields, so it's
just insert or delete), so I'll fire the insert as it is (that'll get the
cases where it's not a concurrent update failure) and catch the failure to
verify that the data exists - if it does, I'll ignore the failure; if not,
i'll throw an exception.

Larry

On Sat, Jun 27, 2015 at 10:57 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 2015-06-28 6:52 GMT+02:00 Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>:
>
>> On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>> > you can protect it against this issue with locking - in this case you
>> can
>> > try "for update" clause
>> >
>> > http://www.postgresql.org/docs/9.4/static/explicit-locking.html
>> >
>> > insert into Favorite (patronId, titleId)
>> > select 123, 234
>> > where not exists (
>> > select 1 from Favorite where patronId = 123 and titleId = 234 for
>> update
>> > )
>>
>> That won't work reliably either -- a SELECT ... FOR UPDATE will still
>> use an MVCC snapshot. The looping + subxact pattern must be used [1]
>> if a duplicate violation isn't acceptable. ON CONFLICT DO UPDATE
>> should be preferred once 9.5 is released.
>>
>> [1]
>> http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
>>
>
> yes, you have true - cannot to lock, what doesn't exists in pg
>
> Regards
>
> Pavel
>
>
>
>
>> --
>> Regards,
>> Peter Geoghegan
>>
>
>