Postgres trigger to open http connection

Question

I need to ping some HTTP service every time an insert occurs in a Postgres table using a trigger and an HTTP GET or POST?

Is there any simple way to achieve that with a standard PostgreSQL installation?

In case there isn't, is there any way to do it with additional libraries?

Answer 1

You can do this with PL/perlu or PL/pythonu . However, I strongly recommend that you don't do it this way. DNS problems, problems with the server, etc will cause your PostgreSQL backends to stall, severely disrupting database performance and possibly causing you to exhaust max_connections.

Instead, have a trigger send a NOTIFY when the change occurs, and have the trigger insert details into a log table. Have a client application LISTEN for the notification, read the records inserted by the trigger into the log table, and make any appropriate HTTP requests.

The client should get the requests from the log table one-by-one using SELECT ... FOR UPDATE and DELETE it when the request is made successfully.

See this answer about sending email from within the DB for some more detail.