Atomar SQL Statement

From: "Weber, Johann (ISS Kassel)" <jweber(at)iss(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Atomar SQL Statement
Date: 2006-07-07 11:55:02
Message-ID: B8ADDBDA8696F546BDAC69B469D7F0512E06F3@kasmaiexcp01.iss.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Guys,

I want to assure that a SQL statement is atomar. It trys to check if an
email address is already found in a table, if yes, it returns the id
field of the entry. If not found, it inserts the entry with the
previously generated increment id and returns - again - the id.

My concern: in a multi threaded environment, can a second thread
interrupt this statement and eventually insert the same email address in
the table with a different id? Or is this statement atomar?

Any help would be highely appreciated.

CODE:

_id := nextval('email_id_increment');

INSERT INTO email_adr (email_id, email, lastupdate)

SELECT _id, 'sender(at)example(dot)com', now()::timestamp

WHERE NOT EXISTS (SELECT * FROM email_adr WHERE
email='sender(at)example(dot)com');

- Johann

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Glaesemann 2006-07-07 12:06:43 Re: Update from join
Previous Message Richard Huxton 2006-07-07 11:54:42 Re: Alternative to serial primary key