Re: how do i avoid multiple sessions from inserting the same row?

Why not something like that ?

1) SELECT id FROM users WHERE email = 'blah'

2) IF NULL -> INSERT INTO users values (....'blah' ) WHERE NOT EXISTS ....
-> SELECT id FROM users WHERE email = 'blah'

I think that only one process should do the insert but all will return the
id...

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kolus Maximiliano
Sent: Monday, February 24, 2003 5:13 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] how do i avoid multiple sessions from inserting the same
row?

Hello,

I'm programming a little system that has an 'users' table and i've
met a concurrency problems: users will be added to this table upon the
reception of emails from them (for those who want to know, it's like
http://www.ordb.org). So, if john(at)doe(dot)com sends an email to an special
address he wil be added to the users table.

The problem i have is that some users have automated systems that
shoot a lot of emails at once, so i have multiple processes trying to check
if john(at)doe(dot)com exists and add him if he doesnt. The process for this is:

1) SELECT id FROM users WHERE email='blah';
2) If the previous select returns NULL, the user will be added and it's id
will be returned.
3) If the previous select returns the id, it will be returned.

What happened?. Well, two processes believed that john(at)doe(dot)com
didn't exist, both tried to add him and one of them got a beautyfull
duplicated key error.

I need to avoid this, i looked at pg's table and row locking
techniques. I dont know fi SELECT ... FOR UPDATE would work because i would
be selecting a row that doesnt exist yet. LOCK TABLE ... FOR ACCESS
EXCLUSIVE MODE would work, but it seems to be a little extreme for me.

Any ideas or tips?. TIA.

--
Maximiliano A. Kolus
Network Administrator
<kolus(dot)maximiliano(at)bcr(dot)com(dot)ar>
Bolsa De Comercio Rosario - Argentina