Which other solution do I have for this problem?
NOTIFY to tell your app that things have changed.
You can send the
NOTIFY from a trigger that also records changes in a queue table.
You'll need a PgJDBC connection that has sent a
LISTEN for the event(s) you're using. It must poll the database by sending periodic empty queries (
"") if you're using SSL; if you are not using SSL this can be avoided by use of the async notification checks. You'll need to unwrap the
Connection object from your connection pool to be able to cast the underlying connection to a
PgConnection to use listen/notify with. See related answer
The producer/consumer bit will be harder. To have multiple crash-safe concurrent consumers in PostgreSQL you need to use advisory locking with
pg_try_advisory_lock(...). If you don't need concurrent consumers then it's easy, you just
SELECT ... LIMIT 1 FOR UPDATE a row at a time.
Hopefully 9.4 will include an easier method of skipping locked rows with
FOR UPDATE, as there's work in development for it.