7

I have written a Java application which should be started or woken up when 'something' happens in the database. For example an insert into the user table should trigger the sending of the usual welcome, password, ... mails.

What is the best common practice to do this? I can write my application such that it executes the following query let's say every second:

select mail from user where mail_sent = false

But this is polling, and I would like to avoid it. Is there a way to start or wake-up my Java application (push) initiated by a change in the database?

Cheers!

Improve this question
1
  • Important info: I only want one single instance of the Java application. So if two users are added one after each other, I don't want to have a second instance being started. That is, the actions are processed sequentially by the single instance. Commented Nov 10, 2011 at 10:53

4 Answers 4

Reset to default
3

Triggers in PostgreSQL can be written in a host of languages, amongst which is PL/Java. You could set a trigger on the tables that require this monitoring for the relevant actions (insert, delete, update...) and have the trigger take care of the notification. It might require some form of inter-process communication, but if both trigger and client are written in Java that shouldn't prove too difficult.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Take a look at NOTIFY

Improve this answer

3 Comments

Very useful. One drawback seems that we still have to POLL according to jdbc.postgresql.org/documentation/83/listennotify.html: "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. " What is meant here with 'backend'? Are we querying the database every x seconds or accessing the in-memory JDBC backend?
Yes, with backend they mean the database. And yes, the listener will occasionally poll the database to see if it got a notification. If you want actual asynchronous events, you could look at Firebird.
According to the docs of pgsql, they are asynchronous. It is only the JDBC driver which has the limitation of not supporting the asynchronous updates. That's probably a TO DO on their roadmap, I guess.
1

Try this..It is easy to understand and will help u a lot

https://wiki.postgresql.org/wiki/PgNotificationPoller

Improve this answer

Comments

0

I don't think it is possible to start an application. However when you have an application running, that application can subscribe to listen for events. You can use triggers to post (notify) events for the certain actions.

When the application is notified of the event it can respond with certain actions. See the postgresql site for an example.

Improve this answer

1 Comment

The helper implementation is very useful when listening to multiple events at several places in your code. Thanks for that!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.