Re: Weird insert issue

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
>