Re: implementing asynchronous notifications PLEASE CONFIRM MY - Mailing list pgsql-jdbc
From | David Gagnon |
---|---|
Subject | Re: implementing asynchronous notifications PLEASE CONFIRM MY |
Date | |
Msg-id | 430F5D25.8030609@siunik.com Whole thread Raw |
In response to | Re: implementing asynchronous notifications (Andras Kadinger <bandit@surfnonstop.com>) |
List | pgsql-jdbc |
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@siunik.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@siunik.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@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) >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >>> >> >> > > >
pgsql-jdbc by date: