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

>
>