2015-06-28 6:37 GMT+02:00 Larry Meadors <larry.meadors@xxxxxxxxx>:
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
)
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
