postgresql - java - wake up application when something in database happens

Question

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!

Answer 1

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.