Re: how do i avoid multiple sessions from inserting the

If you create a PGSQL function using PLPG SQL you could do the same sort
of thing.

Difference being you return -1 as an indicator of failure for
duplicates.

CREATE OR REPLACE fn_add_email( VARCHAR(128) ) RETURNS INTEGER AS'
DECLARE
new_email ALIAS FOR $1;
result INTEGER := -1;
rec_chk_exist RECORD;
BEGIN
SELECT INTO rec_chk_exist *
FROM users WHERE email = new_email;

IF NOT FOUND THEN
INSERT INTO users( email ) VALUES ( new_email );

--IF SEQUENCE GENERATED ID
result := SELECT last_value
FROM sequence_users;

--otherwise
result := SELECT( id ) FROM users WHERE email =
new_mail;
END IF;

RETURN result;
END;' language 'plpgsql';

On Tue, 2003-02-25 at 05:13, Kolus Maximiliano wrote:
> 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
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley(dot)willan(at)deeperdesign(dot)co(dot)nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.