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@doe.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@doe.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@doe.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.maximiliano@bcr.com.ar>
> Bolsa De Comercio Rosario - Argentina
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.
