Message-ID: <459BBC41.1070804@archonet.com>
Date: Wed, 03 Jan 2007 14:22:57 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: ramachandra.bhaskaram@wipro.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL NOTIFY Help] sending NOTIFY to the external program
References: <47230CE166B64744B0120173E4C2161E0103E6A1@BLR-EC-MBX01.wipro.com>
In-Reply-To: <47230CE166B64744B0120173E4C2161E0103E6A1@BLR-EC-MBX01.wipro.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

ramachandra.bhaskaram@wipro.com wrote:
> Yes, our application is supposed to know *immediately* that a change in
> the database has occurred since, based on this modified data it is doing
> lot of other operations and also, the db transactions are heavy(expected
> to be arround 300 tps(transactions per sec)).
> 
> I agree that this can be done using some untrusted languages, written to
> a file and then the application can look into the file for the modified
> date.  But, as far as performance is concerned it will take time for the
> application to get the response. The user on the application side might
> not be kept in waiting state until this transaction happens.

Needn't take time, if the O.S. you're on can notify you of file writes, 
but in any case I'd look at communicating over a socket (unix or tcp/ip) 
if you want immediate notification. It's simple enough in perl or python 
(or ruby, or any of those).

Oh, and you'll need to cope with the case:
  1. Transaction containing the update is rolled back
  2. Notification has already left the database

-- 
   Richard Huxton
   Archonet Ltd