Is there a way to force a new private transaction in a FUNCTION? That seems like it would be a good solution here because I could simply do the insert in the RAISE within its own private transaction. Then on the next iteration of the loop (as long as I don't have repeatable reads) it should be picked up. And there should only be a quick recoverable deadlock.
> I must be doing something wrong because both of these approaches are giving > me deadlock exceptions.
Deadlocks are to be expected if the INSERTs are batched within a single transaction and there are several sessions doing this in parallel.
Given that there's an unique constraint on hometowns(name), if this sequence happens (not even considering the "users" table to simplify):
Session #1: begin; Session #2: begin; Session #1: insert into hometowns(name) values('City1'); Session #2: insert into hometowns(name) values('City2'); Session #1: insert into hometowns(name) values('City2'); => Session #1 is put to wait until Session #2 commits or rollbacks Session #2: insert into hometowns(name) values('City1'); => Session #2 should wait for Session #1 which is already waiting for Session #2: that's a deadlock
It does not mean that the code meant to insert one user and the town without race condition is incorrect by itself. The unique_violation handler is not called in this scenario because the candidate row is not yet committed by the other session. This would work in an OLTP scenario when each "user" is commited after processing it.
Avoiding deadlocks between parallel batches is a different problem than avoiding race conditions. If you have the above issue, I don't think you may solve it by tweaking the code of an individual process. It needs to be rethought at the level above, the one that initiates this in parallel and dictates the commit strategy. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org