Re: implementing asynchronous notifications PLEASE CONFIRM MY

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 !

/David

Oliver Jowett wrote:

>David Gagnon wrote:
>
>
>
>> I just read this thread. Wich seems really interesting. Is there an
>>example on how to use Async Notifies? I look at it a couple of months
>>ago and found it not sufficient for my needs. I need to implement a
>>notification mechanism to clear data from caches in my application on
>>data update.
>>
>>Is this change allow me to do that .. ? That will be so nice :-)
>>
>>If yes it's that possible to get a small example on how to use it.
>>
>>
>
>Basically, you can now call PGConnection.getNotifications() and get
>results without having to submit a dummy query first.
>
>You will still need an application-level loop that periodically calls
>it, though. Also, it's not guaranteed to give you notifications: you
>must be not in a transaction (this is server-side behaviour), and you
>must be using a Socket implementation that implements available() (SSL
>connections may not do this).
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>