> In my opinion the easiest approach is to just go ahead and roll back
> the transaction when you get a dup-key error, and try again from the
> top. So:
I tried something slightly different:
* LOCK the table in SHARE ROW EXCLUSIVE mode, this would allow me to use the data in the users table when i'm trying to update it (there are parts where users are just queried, not queried & added). Since this lock collides with itself, two processes trying to update the users table wont be able to do so at the same time.
* SELECT (search for the user)
* IF NOT FOUND ... INSERT
This logic is inside a function -user_select_or_create (varchar(256)-, but i had some problems:
* I couldn't use BEGIN TRANSACTION nor COMMIT/ROLLBACK. (I'm not a pg guru, but, could it be because the function itself is considered a transaction?).
* If i use LOCK TABLE users IN SHARE ROW EXCLUSIVE MODE in two psql session it works (one session gets the lock, the other waits). But inside the function it seems to be ignored (i had the dup key problem).
* I thought it could be the auto-commit thing, so i tried this: "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;" inside the function, when i do so i get this: "ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query". There isnt anything between the "BEGIN" and the "SET TRANS...", i dont know which query is it talking about (i call this function by doing SELECT user_select_or_create('john@doe.com'), could it be *this* query?).
> try UPDATE; if succeed then done
> else try INSERT; if succeed then done
> else ROLLBACK, start again
> In practice rollbacks are going to be pretty infrequent, so this is
> not inefficient --- certainly no worse than any other solution.
Can errors on INSERT commands be ignored?. I mean, would the transaction be aborted in that case?.