Thread: Weird insert issue

Weird insert issue

From
Larry Meadors
Date:
I'm running this SQL statement:

insert into Favorite (patronId, titleId)
select 123, 234
where not exists (
  select 1 from Favorite where patronId = 123 and titleId = 234
)

It normally runs perfectly, but will rarely fail and I just can't see
any way that it could. :-|

The exception I get is that the unique key (patronid+titleid) was violated.

Is it possible that the statement is getting run twice and that the
timing is such that the first one succeeds and the second tries to do
the insert and fails because the select part of the SQL ran before the
first insert completed? I'd expected that each of the two would be
single operations, but this error is making me rethink that.

Any thoughts?

Larry


Re: Weird insert issue

From
Pavel Stehule
Date:


2015-06-28 6:37 GMT+02:00 Larry Meadors <larry.meadors@gmail.com>:
I'm running this SQL statement:

insert into Favorite (patronId, titleId)
select 123, 234
where not exists (
  select 1 from Favorite where patronId = 123 and titleId = 234
)

It normally runs perfectly, but will rarely fail and I just can't see
any way that it could. :-|

The exception I get is that the unique key (patronid+titleid) was violated.

Is it possible that the statement is getting run twice and that the
timing is such that the first one succeeds and the second tries to do
the insert and fails because the select part of the SQL ran before the
first insert completed? I'd expected that each of the two would be
single operations, but this error is making me rethink that.

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
)

Regards

Pavel
 

Any thoughts?

Larry


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Weird insert issue

From
Peter Geoghegan
Date:
On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule <pavel.stehule@gmail.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


Re: Weird insert issue

From
Pavel Stehule
Date:


2015-06-28 6:52 GMT+02:00 Peter Geoghegan <peter.geoghegan86@gmail.com>:
On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule <pavel.stehule@gmail.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

Re: Weird insert issue

From
Larry Meadors
Date:
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.stehule@gmail.com> wrote:
2015-06-28 6:52 GMT+02:00 Peter Geoghegan <peter.geoghegan86@gmail.com>:
On Sat, Jun 27, 2015 at 9:47 PM, Pavel Stehule <pavel.stehule@gmail.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