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)