From: | Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | larry(dot)meadors(at)gmail(dot)com, "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Weird insert issue |
Date: | 2015-06-28 04:52:31 |
Message-ID: | CAEYLb_Vji+q9rr6u=W-WFCasZV2GrUoBtzMhRAppMSBaZ4MOFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
Regards,
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2015-06-28 04:56:42 | Re: Weird insert issue |
Previous Message | Pavel Stehule | 2015-06-28 04:47:42 | Re: Weird insert issue |