Postgresql - detect changes and call webservice

Question

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.

Nanny

Apache Camel also offers a PostgresSQL transport. You can create a route using the JavaDSL. In combination with NOTIFY, this might work. – Solberg

Related question Notifying postgres changes to java application – Solberg

Answer 1

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.

Stegodon

Java clients can do client-side-only polling for notifications if they don't use SSL. See jdbc.postgresql.org/documentation/publicapi/org/postgresql/… . 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. – Rainarainah

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. – Rainarainah

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. – Rainarainah

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?? – Nanny

@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. – Stegodon

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; – Nanny

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! – Nanny

@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. – Stegodon

Java Code for implementing LISTEN asynchronously is available at https://mcmap.net/q/276106/-notifying-postgres-changes-to-java-application – Solberg