40

I have a PostgreSQL database. What I want to do is, detect any changes (insert, update) that happen in the database and then call a webservice. How could I do this?

Thanks in advance for any help.

Henning
  • 16,063
  • 3
  • 51
  • 65
diego10
  • 533
  • 2
  • 11
  • 18
  • Apache Camel also offers a PostgresSQL [transport](http://camel.apache.org/transport.html). You can create a route using the [JavaDSL](http://camel.apache.org/java-dsl.html). In combination with NOTIFY, this might work. – koppor Sep 14 '16 at 12:09
  • Related question [Notifying postgres changes to java application](http://stackoverflow.com/q/18126178/873282) – koppor Sep 14 '16 at 12:11

1 Answers1

26

You should be able to use triggers and the listen/notify functionality in PostgreSQL to achieve something like this:

  1. A set of insert/update/delete triggers create a notification event whenever anything changes in your table, using the created/changed/deleted ID as the payload.

  2. A background process checks for notifications periodically (here's an example using Java/JDBC), and then loads the changed record from the database to do the web service call.

This is not in any way a real-time push-type system, but you have to poll the database for notification events to trigger the webservice call. It will do the trick, though.

Henning
  • 16,063
  • 3
  • 51
  • 65
  • 1
    Java clients can do client-side-only polling for notifications if they don't use SSL. See http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGConnection.html#getNotifications() . If you're using SSL then PgJDBC can't receive notifications asynchronously and you have to poll by sending an empty query or a `SELECT 1;`. If you're using client-side polling you can poll quite quickly and it's pretty much real-time. This works in most language's database drivers, not just PgJDBC. – Craig Ringer Mar 19 '13 at 23:50
  • 1
    BTW, using `LISTEN` and `NOTIFY` is certainly the right approach. It's possible to use a `plpythonu`, `plperlu`, etc trigger to make web services calls from directly inside the database but (a) it's not transactional and (b) it'll do horrible things to your database's performance, especially if the web service is slow or becomes unresponsive. – Craig Ringer Mar 19 '13 at 23:53
  • 3
    Rather than using the ID as the payload, though, I'd recommend logging the creates, inserts and deletes to a history table and just sending a payload-less notify that tells the client that something has been added to the history table. Much more robust and faster too. – Craig Ringer Mar 19 '13 at 23:54
  • i have installed PostgreSQL 8.4. I'm trying to use notify/listen with a trigger notifying and php listening. But when i insert something on the table, it doesn't recognize pg_notify, that i use inside the function enabled by the trigger. is it a version problem or am i doing something wrong?? – diego10 Mar 20 '13 at 11:36
  • @diego10, notifications are available in version 8, too. It would be helpful if you could post your trigger definition and error message. One thing from the top of my head that could be wrong with your trigger is using `SELECT pg_notify('channel', 'payload')` instead of `PERFORM pg_notify('channel', 'payload')` -- PostgreSQL does not allow you to silently discard the result of a SELECT. – Henning Mar 20 '13 at 12:09
  • my function is: CREATE OR REPLACE FUNCTION notify() RETURNS trigger AS $BODY$ BEGIN PERFORM pg_notify('message', 'test'); RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION notify() OWNER TO postgres; – diego10 Mar 20 '13 at 12:18
  • and the error: SQL error: ERROR: function pg_notify(unknown, unknown) does not exist LINE 1: SELECT pg_notify('message', 'test') ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT pg_notify('message', 'test') CONTEXT: PL/pgSQL function "notify" line 2 at PERFORM In statement: INSERT INTO "public"."nums" ("id") VALUES ('10') thanks for the help! – diego10 Mar 20 '13 at 12:21
  • 1
    @diego10, ouch. It looks like pg_notify was not available in 8.4. You'll have to use `NOTIFY message` directly, which does not allow a variable channel name. Also, support for a payload was only added in 9.0, so you'll have to do without. This makes @Craig's suggestion of using a history table plus notifications your best bet. – Henning Mar 20 '13 at 12:49
  • Java Code for implementing LISTEN asynchronously is available at http://stackoverflow.com/a/39446972/873282 – koppor Sep 14 '16 at 12:10