Re: implementing asynchronous notifications PLEASE CONFIRM MY - Mailing list pgsql-jdbc

Thanks for your answer !!!

       I look more deeply with solution #1 but the big problem seem to
be the lack of flexibility with the notify mechanism...  In the trigger
I would send something like

NOTIFY "TABLEX:UPDATE:111";

To say table row with id 111 on table X have been updated ...But I I
need to explicitly LISTEN on a Channel (or identifier) .. How can I get
this notification on the client.

Is there something I dont understand?  Have you implemented it this way ?

Thanks for your help... I really need to find an answer to this problem :-/

/David

Andres Olarte wrote:

>I've been using #1 in development and testing, and I think we'll go
>live in about a month.  I have a number of desktop applications
>registering notifications, and getting updates this way, and all tests
>have gone nicely.
>
>#2 is really unelegant, and won't scale well.  #3 is not yet possible
>but could be using tons of code in stored procedures.  I would say #1
>is the way to go.
>On 8/21/05, David Gagnon <dgagnon@siunik.com> 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 !
>>
>> /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@postgresql.org)
>>
>>
>>
>>
>>
>>
>
>
>