Re: implementing asynchronous notifications PLEASE CONFIRM MY

Hi ,

   Thanks for your answer, see my answer below..

> David Gagnon wrote:
>
>> Hi all,
>>
>>   I have a java web application connected to a postgresql DB (of
>> course :-)).  I create a bunch of cache in my web Application and I
>> need postgresql to inform the application of row update and delete.
>> I think this is a common behavior when we cache data in the
>> application and use stored procedure to update data.
>> I saw this thread (See below) a while ago but I'm not sure a this is
>> the best way to solve my problem.  I think there is 3 possibilities
>> to solve this problem:
>>
>> #1:  Having rules/trigger on update and delete that create
>> notification.  On the java server I need a thread to read the
>> notification and update the caches in the system accordingly.
>>
>> #2: Having rules/trigger on update and delete that write a line into
>> a table. On the java server I need a thread to read the table and
>> update the caches in the system accordingly.
>>
>> #3: Having rules/trigger on update and delete that call a CALLBACK
>> function that goes directly to the server... and update the cache
>> directly.
>>
>> #4: Any other idea ?
>>
>>
>> For #1: Is that reliable? 100% full prove.  I must not loose
>> notifications... because my cache wont be in synch
>>
>> For #2:  Seem the best way to do it...  Is there a way to do it to
>> reduce performance impact ?
>>
>> For #3:  Don't think it's implemented yet ... am I wrong?
>>
>>
>> Thanks for your help pointing me the best implementation to solve my
>> problem
>>
>> Best Regards !
>>
>>
>
> However I do not know your exact requirement, if I were to solve such
> a problem, I would not try to put any overhead on the DB server; after
> all, the purpose of caching is to give a brake to the DBServer.
>
> In fact, the easiest way to have the cache in sync with the DB is to
> have a kind of Facade , a unique class, with static methods for
> reading/writing data for both the cache and the DB.
>
> Using a single class for reading/writing , you won't need the DB to
> notify  you of changes since all call go through your facade, that
> means that you know when things are being changed and can take action.
>
> However, your requirements my be different.
>
>
> Regards. \

I already have a facade I my application.  The problem comes when some
data intensive stored procedure modify table....  If I modify a client
table, an account receivable table ... I need to inform the
application.  So no choice I need to inform my application.  If I use a
kind of polling from the application I may have invalid data in my
application.. The big problem is that when a user launch a stored
procedure.. chance are that he will consult the other data right after
the completion of the stored procedure.  If data have not been updated
in the cache I will display invalid data :-(

I think that it's a common problem when you have a web application that
use a caching mechanism and use stored procedure.  Without an apdapted
notification framework you are force use some alternative solution that
probably have important performance impact.  I think the best solution
will be to have the LISTEN/NOTIFY support an arbitrary string.  I saw
this have been put in the TODO list.  Is there any plan to put that soon
?  Sorry I don't have the knowledge to do it my self :-(

Regards
/David


>
>