Обсуждение: implementing asynchronous notifications
Greetings everyone, A Java beginner here, seeking the insight of more experienced. " A key limitation of the JDBC driver is that it cannot receive asynchronous notifications and must poll the backend to check if any notifications were issued." - http://jdbc.postgresql.org/documentation/80/listennotify.html I am looking forward to have that limitation lifted. When we are idle and not in a transaction, the backend actually sends us notifications right away; in this case there is no need for us to do polling with empty queries. Under unix you can get the fd from libpq, select() on it, and then process notifications by PQConsumeInput() and retrieve them with PQnotifies(). - see last paragraph at http://www.postgresql.org/docs/current/static/libpq-notify.html Does Java provide working infrastucture to implement this reliably? What's needed is a reliable way to detect that bytes have arrived on our connection socket. I am hoping java.net.Socket.getInputStream.available() could be used. Or even better the new select()-like functionality provided by java.nio since 1.4. Do these actually work? If java.nio does, would the impact of choosing java.nio, and thereby excluding pre-1.4 platforms, an acceptable way of conduct? Or is there infrastructure in the jdbc.postgresql.org project in place to allow us to preserve backwards-compatibility by simply omitting this feature from pre-1.4 builds? Thank you in advance, Best Regards, Andras
Andras Kadinger wrote: > If java.nio does, would the impact of choosing java.nio, and thereby > excluding pre-1.4 platforms, an acceptable way of conduct? I don't think so.. I'm pretty sure there are users that are running the driver under 1.2 (*bsd perhaps?) > Or is there > infrastructure in the jdbc.postgresql.org project in place to allow us to > preserve backwards-compatibility by simply omitting this feature from > pre-1.4 builds? It'll be tricky to do this from a single codebase as the NIO changes would need to reach right down to the underlying protocol stream.. it seems hard to localize the changes. -O
On Sun, 10 Apr 2005, Oliver Jowett wrote: > It'll be tricky to do this from a single codebase as the NIO changes > would need to reach right down to the underlying protocol stream.. it > seems hard to localize the changes. Okay, then I implemented this using Socket.getInputStream().available() calls. Proof of concept attached. PGConnection.getNotifies() now processes peding Async Notifies first - no need to send polling queries to the server anymore. The result is zero network traffic in the idle case. Any comments? I am unclear as to how to handle possible protocol errors (e.g. when what we end up reading from the connection is not an 'A'sync Notify). Theoretically, in a working connection this should not happen though.
Вложения
On Mon, 11 Apr 2005, Andras Kadinger wrote: > I am unclear as to how to handle possible protocol errors (e.g. when what > we end up reading from the connection is not an 'A'sync Notify). > Theoretically, in a working connection this should not happen though. Yes, it could: reading the PostgreSQL protocol documentation, it says "frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle". So I now added code to process Error 'N'otifications as well.
Вложения
Andras Kadinger wrote: > On Mon, 11 Apr 2005, Andras Kadinger wrote: > >>I am unclear as to how to handle possible protocol errors (e.g. when what >>we end up reading from the connection is not an 'A'sync Notify). >>Theoretically, in a working connection this should not happen though. > > Yes, it could: reading the PostgreSQL protocol documentation, it says > "frontends should always be prepared to accept and display NoticeResponse > messages, even when the connection is nominally idle". > > So I now added code to process Error 'N'otifications as well. You also need to handle errors ('E'). Try shutting down a postmaster (-m fast) while idle connections are around -- they'll get spontaneous FATAL errors. > + try { > + executor.processNotifies(); > + } catch (SQLException e) {}; Don't eat the exceptions, let them propagate. (ugh, getNotifications() does not throw SQLException. We should probably change that..) > + while (protoConnection.getTransactionState() == ProtocolConnection.TRANSACTION_IDLE && pgStream.getSocket().getInputStream().available()>0){ Can you move that reference following into a method on PGStream? (hasMessagePending() or something) The test on transaction state is a bit misleading since the connection's transaction state should never change inside the loop. Perhaps making that a separate test would be clearer. I'm not sure if available() is guaranteed to work on a socket stream everywhere (it works fine here, though), but I suppose that at worst you get the existing behaviour where you need to send a query. Otherwise, seems fine! -O
On Mon, 11 Apr 2005, Oliver Jowett wrote: > Andras Kadinger wrote: > > On Mon, 11 Apr 2005, Andras Kadinger wrote: > > > >>I am unclear as to how to handle possible protocol errors (e.g. when what > >>we end up reading from the connection is not an 'A'sync Notify). > >>Theoretically, in a working connection this should not happen though. > > > > Yes, it could: reading the PostgreSQL protocol documentation, it says > > "frontends should always be prepared to accept and display NoticeResponse > > messages, even when the connection is nominally idle". > > > > So I now added code to process Error 'N'otifications as well. > > You also need to handle errors ('E'). Try shutting down a postmaster (-m > fast) while idle connections are around -- they'll get spontaneous FATAL > errors. Are you certain? The protocol documentations specifically mentions this case, saying it would send a NoticeResponse: "It is possible for NoticeResponse messages to be generated due to outside activity; for example, if the database administrator commands a "fast" database shutdown, the backend will send a NoticeResponse indicating this fact before closing the connection. Accordingly, frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle." - http://www.postgresql.org/docs/8.0/static/protocol-flow.html#PROTOCOL-ASYNC Still, I took your word on this now, and added code to handle 'E's. > > + try { > > + executor.processNotifies(); > > + } catch (SQLException e) {}; > > Don't eat the exceptions, let them propagate. The meaning behind the words "Proof of concept". :) > (ugh, getNotifications() does not throw SQLException. We should probably > change that..) Agreed. I just wasn't sure changing public interfaces was the most polite way of introducing myself. :) Fixed now. > > + while (protoConnection.getTransactionState() == ProtocolConnection.TRANSACTION_IDLE && pgStream.getSocket().getInputStream().available()>0){ > > Can you move that reference following into a method on PGStream? > (hasMessagePending() or something) Sure! Good idea! Done! > The test on transaction state is a bit misleading since the connection's > transaction state should never change inside the loop. Perhaps making > that a separate test would be clearer. Done! > I'm not sure if available() is guaranteed to work on a socket stream > everywhere (it works fine here, though), but I suppose that at worst you > get the existing behaviour where you need to send a query. Same here (the rationale behind my first post). I have read somewhere, available() might not work with SSLSockets (haven't looked behind that). > Otherwise, seems fine! Thank you! :) Any further comments/improvements? If none, then hereby I'd like to submit this for inclusion. Andras
Вложения
Andras Kadinger wrote: > On Mon, 11 Apr 2005, Oliver Jowett wrote: > >>You also need to handle errors ('E'). Try shutting down a postmaster (-m >>fast) while idle connections are around -- they'll get spontaneous FATAL >>errors. > > > Are you certain? The protocol documentations specifically mentions this > case, saying it would send a NoticeResponse: > > "It is possible for NoticeResponse messages to be generated due to outside > activity; for example, if the database administrator commands a "fast" > database shutdown, the backend will send a NoticeResponse indicating this > fact before closing the connection. Accordingly, frontends should always > be prepared to accept and display NoticeResponse messages, even when the > connection is nominally idle." - > http://www.postgresql.org/docs/8.0/static/protocol-flow.html#PROTOCOL-ASYNC > > Still, I took your word on this now, and added code to handle 'E's. This is what I get from a pg_ctl stop -m fast on 8.0.0: recvfrom(3, "E\0\0\0mSFATAL\0C57P01\0Mterminating connection due to administrator command\0Fpostgres.c\0L2042\0RProcessInterrupts\0\0", 8192, 0, NULL, NULL) = 110 I guess the docs are out of sync.. -O
Andras Kadinger wrote: > PGConnection.getNotifies() now processes peding Async Notifies first - no > need to send polling queries to the server anymore. The result is zero > network traffic in the idle case. I've applied your latest patch (with some cosmetic editing and a testcase) to CVS HEAD. Thanks for implementing this! -O
Hi, 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. Thanks! /David Oliver Jowett wrote: >Andras Kadinger wrote: > > > >>PGConnection.getNotifies() now processes peding Async Notifies first - no >>need to send polling queries to the server anymore. The result is zero >>network traffic in the idle case. >> >> > >I've applied your latest patch (with some cosmetic editing and a >testcase) to CVS HEAD. Thanks for implementing 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) > > >
Hi all, I use the following code when accessing Postgreql. I suspect it may have a weakness if an exception (Not SQLException) is thrown in the try/catch block. I got a NullPointerException today so with the following code there is no rollback on the trasaction but the ResultSet/Connection/Statement will be closed (in finally block). Is that oki? I will probably change the catch (SQLException e) for a catch (Exception e). I just wanted to be sure since it's not clear in the javadoc that closing a uncomitted resultSet is the samething as issuing a rollback prior to closing the ResultSet. Thanks for your help!! It's really appreciated /David try { dbCon = ConnectionFactory.getConnection(); dbCon.startTransaction(); ... Throws NullPointerException... ... dbCon.commitTransaction(); } catch (SQLException e) { log.error("Problem with the db : " + e.getMessage(), e); try { dbCon.rollbackTransaction(); } catch (SQLException e1) { log.error("Unable to rollback : " + e1.getMessage(), e); } ExceptionAdaptor.instance().getMappedException(e, "Unable to add : " + e.getMessage(), true, ExceptionAdaptor.ACTION_INSERT); } finally { if (dbCon != null) dbCon.closeAll(); }
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
David Gagnon wrote: > I just wanted to be sure since it's not clear in the > javadoc that closing a uncomitted resultSet is the samething as issuing > a rollback prior to closing the ResultSet. Closing ResultSet objects has no effect on transactions. Closing Connection objects does. The PostgreSQL driver implements this behaviour: if autocommit is false, changes in the current transaction are committed only when you call Connection.commit() or Connection.setAutoCommit(). So closing the connection does cause a rollback, since that connection is then dead and can't be committed. -O
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:
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)
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) >> >> >> >> >> >> > > >
On Thu, 25 Aug 2005, David Gagnon wrote: > 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
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) >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >>> >> >> > > >
>> 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
On Fri, 26 Aug 2005, David Gagnon wrote: > 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 > These globabl variables are not truly global, they are per connection, so you can't store something in one connection, issue a NOTIFY and pick it up from the LISTENing connection. You would need to put these actions into a real table to make it available to more than one connection. Kris Jurka
Hi , Thanks for your answer, see my answer below.. > David Gagnon 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 ! >> >> > > However I do not know your exact requirement, if I were to solve such > a problem, I would not try to put any overhead on the DB server; after > all, the purpose of caching is to give a brake to the DBServer. > > In fact, the easiest way to have the cache in sync with the DB is to > have a kind of Facade , a unique class, with static methods for > reading/writing data for both the cache and the DB. > > Using a single class for reading/writing , you won't need the DB to > notify you of changes since all call go through your facade, that > means that you know when things are being changed and can take action. > > However, your requirements my be different. > > > Regards. \ I already have a facade I my application. The problem comes when some data intensive stored procedure modify table.... If I modify a client table, an account receivable table ... I need to inform the application. So no choice I need to inform my application. If I use a kind of polling from the application I may have invalid data in my application.. The big problem is that when a user launch a stored procedure.. chance are that he will consult the other data right after the completion of the stored procedure. If data have not been updated in the cache I will display invalid data :-( I think that it's a common problem when you have a web application that use a caching mechanism and use stored procedure. Without an apdapted notification framework you are force use some alternative solution that probably have important performance impact. I think the best solution will be to have the LISTEN/NOTIFY support an arbitrary string. I saw this have been put in the TODO list. Is there any plan to put that soon ? Sorry I don't have the knowledge to do it my self :-( Regards /David > >
Arcadius A. wrote: > David Gagnon wrote: > >> Hi , >> >> Thanks for your answer, see my answer below.. >> >>> David Gagnon wrote: >>> >>>> Hi all, >>> >>> > [...] > >>> >>> However I do not know your exact requirement, if I were to solve >>> such a problem, I would not try to put any overhead on the DB >>> server; after all, the purpose of caching is to give a brake to the >>> DBServer. >>> >>> In fact, the easiest way to have the cache in sync with the DB is to >>> have a kind of Facade , a unique class, with static methods for >>> reading/writing data for both the cache and the DB. >>> >>> Using a single class for reading/writing , you won't need the DB to >>> notify you of changes since all call go through your facade, that >>> means that you know when things are being changed and can take action. >>> >>> However, your requirements my be different. >>> >>> >>> Regards. \ >> >> >> > [...] > >> The big problem is that when a user launch a stored procedure.. >> chance are that he will consult the other data right after the >> completion of the stored procedure. If data have not been updated in >> the cache I will display invalid data :-( >> > If the user is calling the SP from the same Java app , then , what I > said before is still valid. > The method which calls the SP should clear/invalidate the cache . > So, the next time the user tries to view data, as the cache is > empty/invalidated , new stuff should be loaded from the DB. The business logic in the stored procedure are to much complex to know what gone be changed... at least I cannot be sure ... And that would be really complicated and cumbersome tring to guess... and frankly impossible... It's not an easy problem since the business logic is quite complicated .. .So lot of stored procedure and lot of SP that calls others SP. Thanks for your help Regards /David
David Gagnon wrote: > Hi , > > Thanks for your answer, see my answer below.. > >> David Gagnon wrote: >> >>> Hi all, >> [...] >> >> However I do not know your exact requirement, if I were to solve such >> a problem, I would not try to put any overhead on the DB server; >> after all, the purpose of caching is to give a brake to the DBServer. >> >> In fact, the easiest way to have the cache in sync with the DB is to >> have a kind of Facade , a unique class, with static methods for >> reading/writing data for both the cache and the DB. >> >> Using a single class for reading/writing , you won't need the DB to >> notify you of changes since all call go through your facade, that >> means that you know when things are being changed and can take action. >> >> However, your requirements my be different. >> >> >> Regards. \ > > [...] > The big problem is that when a user launch a stored procedure.. chance > are that he will consult the other data right after the completion of > the stored procedure. If data have not been updated in the cache I > will display invalid data :-( > If the user is calling the SP from the same Java app , then , what I said before is still valid. The method which calls the SP should clear/invalidate the cache . So, the next time the user tries to view data, as the cache is empty/invalidated , new stuff should be loaded from the DB. Regards. Arcadius.