Re: implementing asynchronous notifications PLEASE CONFIRM MY

>> 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(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
>
>> 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)
>>
>>
>>
>>
>>
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq