Re: implementing asynchronous notifications PLEASE CONFIRM MY
От | David Gagnon |
---|---|
Тема | Re: implementing asynchronous notifications PLEASE CONFIRM MY |
Дата | |
Msg-id | 430F5ABC.3070505@siunik.com обсуждение исходный текст |
Ответ на | Re: implementing asynchronous notifications PLEASE CONFIRM (Kris Jurka <books@ejurka.com>) |
Ответы |
Re: implementing asynchronous notifications PLEASE CONFIRM
(Kris Jurka <books@ejurka.com>)
|
Список | pgsql-jdbc |
>> 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. > > > Right, you'd have to say: LISTEN "TABLEX:UPDATE:111". Generally > LISTEN/NOTIFY is used at a much coarser grain because of this > restriction as well as the possibility of multiple notifies (for the > same target) being combined and the client only getting one notification. > > Instead of listening for a very specific action, the listening code is > triggered to go figure out what happened. In your situation perhaps > flushing the entire cache for that event is best instead of trying to > make a very specific alteration. > > Kris Jurka > > Flushing the cache is what I want to avoid since you lose the advantage of having cache! I know TimesTen (an In Memory Database) can inform the client of data change. I tought of another solution .. but again I have no Idea what is the performance cost or if it's feseable at all. Perl (don't know perl at all :-(() allows global variables http://www.postgresql.org/docs/8.0/static/plperl-global.html Another solution would be to (PERL maybe ...) .. so another solution would be to right to popolulate a list So to make it clear it either: 1- Put trigger on update for each table 2- In trigger put storeNotificationInArray() a Perl function that keeps the string "TABLEX:UPDATE:111"; 3- On the client: Polling the DB each 5 sec. and call getArrayNotificationFrorArray 5- Update my cache with the data fetched The avantages of this solution is that no space on disk is used.. .But is that performant ? Is perl allow to do this ? Thanks for your help /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: > > 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 > >> 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) >> >> >> >> >> > > > > ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: David GagnonДата:
Сообщение: Re: implementing asynchronous notifications PLEASE CONFIRM MY
Следующее
От: Kris JurkaДата:
Сообщение: Re: implementing asynchronous notifications PLEASE CONFIRM