Re: implementing asynchronous notifications PLEASE CONFIRM MY

No because i will just flush the data in the cache .. and the data will
be reloaded only when the use will ask for it.

Per example, for my client I have a chache, each client has in id that
correspond to the RRNUM column in the DB. If I receive a notification like:
client::david::update (i.e.:TABLE::ID::ACTION)

I will go to the client cache and delete client david (if present). I
don't need to access the database for each notification .. When the
front-end will ask for client david .. it will be reloaded from the
database (instead of the cache).

Thanks for you help!

/David

Andres Olarte wrote:

>If you use your second aproach, on number two, you will still be doing a
>SELECT * FROM mytable WHERE id=$1;
>
>Assuming that $1 is the id of the row that changed. But if you have
>two rows updated, then you have to make two SELECT queries. This is
>plus the SELECT on the notificationTable. Also, how are you going to
>know which of the items on notificationTable have you already
>processed? You need to issue a DELETE query. For me this is a BIG
>problem, as it doesn't scale to more than one client. This gives a
>total "n"+2 queries, where "n" is the number of updated rows. While
>on #1, you have "m" queries, where "m" is the number of updated
>tables. In any case "m" while be equal or smaller to "n". Of course
>an index on the timestamp column is a must.
>
>The thing is that if there are several identical identifications, you
>might only get one, or some or all. If you got one per row, and the
>same row was updated several times very, your program might end up
>doing and redoing the query. I don't think that's a good idea. Any
>ways, these are my view points, and how I implemented on my code. Good
>luck the aproach you choose.
>
>
>
>On 8/25/05, David Gagnon <dgagnon(at)siunik(dot)com> wrote:
>
>
>>I have this column
>>What is bad with the current notification system is that you need to
>>send a request each time you receive a notification... and scan the
>>whole table for change (with the timestamp).
>>I actually have 90 tables and can expect 1 change per couples of seconds
>>... It's a lot of SELECT .. unless I index the timestamp field of each
>>table.
>>
>>So to make it clear it either:
>>1- Put trigger on update for each table
>>2- In trigger put NOTIFY TABLEX .. Do this for each table
>>3- On the client LISTEN TABLEX.. Do this for each table
>>4- On the client: on notification for TABLEX ISSUE a query on the TABLEX
>>with the last delta SELECT * FROM TABLEX WHERE delta > $1
>>5- Update my cache with the data fetched
>>
>>The other solution .. maybe not that clean is, like I explained in my
>>first :
>>1- Put trigger on update for each table
>>2- In trigger put INSERT INTO notificationTable value(TABLEX, id1, id2, id3)
>>3- On the client have a Thread that scan the table each 5 seconds for
>>row and update the data accordingly
>>
>>I don't know wich one is the best. That would have been so nice to be
>>eable to put a string in the notify !!! NOTIFY UPDATETABLE
>>TableX:id:update
>>
>>Is that make sense ?
>>Thanks!
>>
>>#David
>>
>>
>>Andres Olarte wrote:
>>
>>
>>
>>>Use an extra timestamp column, let's call it 'delta'
>>>
>>>It should default to now(). Then on every update use a per row
>>>trigger to update this column to now(). You can also issue your
>>>notification from this trigger if you want. Then when you receive the
>>>notification, use a query like:
>>>
>>>SELECT * FROM mytable WHERE delta > $1
>>>
>>>Where $1 is the largest delta that you have previously selected. Make
>>>sure that you update this in your program logic as needed. The main
>>>drawback here is that if you delete something, you have to select the
>>>whole table. However, at least in my app, I don't allow deleting from
>>>any table.
>>>
>>>On 8/25/05, David Gagnon <dgagnon(at)siunik(dot)com> wrote:
>>>
>>>
>>>
>>>
>>>>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(at)siunik(dot)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(at)postgresql(dot)org)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>