0

I'm trying to find the best way to get notified by the database when there are insert, delete or update commands in PostgreSQL.

My goal is to handle changes in the database as soon as they happen.

How can I do that?

S-Man
  • 22,521
  • 7
  • 40
  • 63
Fernando
  • 1,477
  • 2
  • 14
  • 33
  • The advice from [this thread](https://stackoverflow.com/questions/38457309/how-to-use-libpqxx-to-receive-notifications-from-the-postgresql-database) might give you some clue. Also, you may want to check [Postgre docs](https://www.postgresql.org/docs/9.1/static/libpq-notify.html) on asynchronous notifications - although, yeah, their API and samples are in C. – Vasiliy Galkin Jun 29 '17 at 12:24

1 Answers1

4

I've found a good way of doing that in Qt.

First, you need to create rules to notify you that updates had happened:

CREATE RULE table_notification_insert AS ON INSERT TO public.table DO NOTIFY table_inserted;
CREATE RULE table_notification_update AS ON UPDATE TO public.table DO NOTIFY table_updated;
CREATE RULE table_notification_delete AS ON DELETE TO public.table DO NOTIFY table_deleted;

Then, you can use Qt to receive each notification ("table_inserted", "table_updated", "table_deleted") as follows:

QSqlDatabase::database().driver()->subscribeToNotification("table_inserted");
QObject::connect(QSqlDatabase::database().driver(), SIGNAL(notification(const QString&)), /*handlerObjectPointer*/, SLOT(handleNotificationFunction(const QString&)));

Here is where I found part of the answer: forum.qt.io

Fernando
  • 1,477
  • 2
  • 14
  • 33
  • 1
    Personally I'd rather use a trigger for this than a rule, and am kind of surprised it works, but ... cool. – Craig Ringer Jun 15 '17 at 01:53
  • How would you use a trigger? – Fernando Jun 16 '17 at 13:25
  • @Fernando: I think @Craig just meant that he'd rather put the `NOTIFY` statement in a [trigger function](https://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE) than a rule. The rule system has a couple of weird edge cases ([`VOLATILE` functions may be re-executed](https://www.postgresql.org/docs/current/static/rules-update.html#RULES-UPDATE), and [`NOTIFY` rules are fired even when nothing is modified](https://www.postgresql.org/docs/current/static/sql-createrule.html#AEN81660)). The best advice is usually to avoid rules altogether. – Nick Barnes Jun 29 '17 at 09:19