Re: how do i avoid multiple sessions from inserting the

> 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(at)doe(dot)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?.