Re: functions, transactions, key violations

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> However, my point regarding the example in the docs still holds. Why
> is the exception block necessary? Doesn't wrapping the statements in a
> function ensure the unique_violation couldn't occur?

Well, the point of that example is to deal correctly with the case where
two sessions try to insert the same key concurrently. The second to
reach the index insertion point will see the conflicting index entry
already there, and it will block until it sees the other transaction
commit or abort, and if commit it will then throw a unique exception.

Are you certain that concurrent executions of purchase() for the same
item_id can never happen in your system? Because that certainly seems
the most likely explanation from here.

regards, tom lane