Re: 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(at)postgresql(dot)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:

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

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(at)siunik(dot)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(at)postgresql(dot)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

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

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(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
>>
>>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(at)siunik(dot)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(at)siunik(dot)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(at)postgresql(dot)org)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

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.