2015-06-28 6:37 GMT+02:00 Larry Meadors <larry.mead...@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. >
sure - it is expected behave http://www.postgresql.org/docs/9.4/static/transaction-iso.html 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 >
