Implementing Database Listener

Question

I'm developing a Java desktop application with Swing as GUI. The app contains services that query from database every seconds to make the interface synced from database. We all know that with this approach, performance is the enemy.

What I want to achieve is that every changes made from database, altered thru psql (Postgres command-line) for example, my app should be notified to update the UI. In this way, performance may be optimized.

Thanks!

What have you done so far? When you say 'database' you say the data in you db or the structure of your db ? — AndreDuarte
– AndreDuarte, Commented Oct 7, 2014 at 16:28
Your requirements are very vague. Do you want to catch any changes, including those caused by external things like psql or are changes done through some central server application? — radai
– radai, Commented Oct 7, 2014 at 16:29

Craig Ringer · Accepted Answer · 2014-10-08 01:49:28Z

3

As @a_horse_with_no_name points out, PostgreSQL supports asynchronous notification channels for just this purpose.

You should create a trigger, probably in plpgsql, on the table(s) you wish to monitor. This trigger fires a NOTIFY when the table changes, optionally including the changed data its self.

The application LISTENs on the notification channel(s) and processes any asynchronous notifications it receives.

Note that it's valid to send an empty query, and you should do that instead of SELECT 1. e.g.:

stmt.execute("");

IIRC even that's optional if you're not using SSL, there's a way to poll purely client side. I don't remember what it is, though, so that's not real helpful.

You can determine exactly what changed by using a trigger-maintained change list table, or by using payloads on your notifications. Or you can simply re-read the whole table if it's small.

edited Oct 8, 2014 at 1:49

answered Oct 8, 2014 at 1:40

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

Erieze Lagera Over a year ago

That's what I needed. Thanks! Creating trigger under a specified table is a good thing in Postgres. I now realized that this can save lots of time in development and performance maybe. Querying every second silently causes flood in the connection pool. But technically, I still don't know if triggers have drawbacks. Maybe I'll just check if there's any.

Craig Ringer Over a year ago

@EriezeLagera A trigger that just sends a NOTIFY is pretty lightweight. It's a bit more expensive if you maintain a change history table, but still not drastic.

Collectives™ on Stack Overflow

Implementing Database Listener

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related