44

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.

Improve this question
2

1 Answer 1

Reset to default
27

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.

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

9 Comments

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.
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.
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.
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, 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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.