Hi there! Today I realised that my knowledge concerning how postgres handles concurrency is not very good, and its even worse when it comes to using that knowledge in real-life.
Let me give you an example. I have this table create table userpositions ( userID int, positionID int, unique (userID,positionID)); For a given userID there can be many positionIDs. There are then two operations performed on this table, the first is "select positionID from userpositions where userID=..." to get all positions for a user, and the second is to replace all positions for the user with new positions. For this I was thinking about running it in a transaction begin; delete from userpositions where userID=...; insert into userpositions (userID,positionID) values ....; commit; But will this work? I don't want select to return empty results, I don't want two inserts running causing a unique violation. Experimenting with it tells me yes, it will work, but how should I reason to "convinse" my self that it will work? Quoting the docs: "The partial transaction isolation provided by Read Committed mode is adequate for many applications, and this mode is fast and simple to use; however, it is not sufficient for all cases. Applications that do complex queries and updates might require a more rigorously consistent view of the database than Read Committed mode provides." How do I know I'm not creating one of those complex queries? Or to put it differntly, how do you identify a complex query with potential issues? Any special techniques to analyze? /J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
