DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=krkKRoiJva6ZCjOfdf/E9AZYTijalTgYKcXa1dgpK4pEK0rBsTOG05DmlbmDFQyU+8Y73rGvzwcO8zQR/NfZ7SgiltY5UgVMOp6EnK1Rr7C+27CnV2h9z8FaEHOoHJTndgwa+99iAUyNiyiFOs4tL3tA0Q78o5/B6HiB2+rPd24=
	;
From: "David Johnston" <polobo@yahoo.com>
To: "'Evan Rempel'" <erempel@uvic.ca>, <pgsql-general@postgresql.org>
References: <4F8B6AA1.1040709@zvelo.com>
	<20120416065717.70a31a6361ac5d7bdad416e8@frank.uvena.de>
	<4F8BD91C.6070405@zvelo.com> <4F8BDAB2.7010209@frank.uvena.de>
	<4F8C248E.7070307@zvelo.com> <4F8CC11A.5020003@d2.com>
	<4F8CDB95.3040407@zvelo.com>
	<A76B25F2823E954C9E45E32FA49D70EC03DACFB6@mail.corp.perceptron.com>
	<4F8E04D9.6010402@zvelo.com>
	<F1226B1821911E4BB69462DAF68098536E32333755@EMC6.uvic.ca>,
	<20120531093728.GA25109@depesz.com>
	<F1226B1821911E4BB69462DAF68098536E3233376B@EMC6.uvic.ca>
In-Reply-To: <F1226B1821911E4BB69462DAF68098536E3233376B@EMC6.uvic.ca>
Subject: Re: is there a select for update insert if not exist type command?
Date: Sat, 9 Jun 2012 20:10:38 -0400
Message-ID: <001a01cd469d$791b6260$6b522720$@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQEPw5tNIB+Ua5xMABLnyHgAr6EY7wGxDrknAbD5Sl4BynBR2wIE75v4AUbzAk4BRrC+6QNcpL4rATHyCXkCExH5LgJvOYWYAW2IMFCXy8VH0A==
Content-Language: en-us

> -----Original Message-----
> 
> Both clients A and B becomes aware to do a task
> 

Ideally you would have this aware-ness manifested as an INSERT into some
kind of job table.  The clients can issue the "SELECT FOR UPDATE" + "UPDATE"
commands to indicate that they are going to be responsible for said task.
You seem to combining "something needs to be done" with "I am able to do
that something".  You may not have a choice depending on your situation but
it is something to think about - how can I just focus on implementing the
"something needs to be done" part.

If you want to avoid the errors appearing in the logs or client you could
just wrap the INSERT command into a function and trap the duplicate key
exception.

It is hard to give suggestions when you are as vague as "becomes aware to do
a task".  Ideally even if you have multiple clients monitoring for "aware
state" only one client should ever actually realize said awareness for a
given task.  In effect you want to serialize the monitoring routine at this
level, insert the "something needs to be done" record, then serialize (for
update) the "I am able to do that something" action.

David J.