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 4308AF3A.2070400@siunik.com
Whole thread Raw
In response to Re: implementing asynchronous notifications  (Oliver Jowett <oliver@opencloud.com>)
List pgsql-jdbc
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:

Previous
From: Oliver Jowett
Date:
Subject: Re: Bad value for type date
Next
From: Petr Dittrich
Date:
Subject: Czech translation