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@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.