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?

Community · Accepted Answer · 2017-05-23 12:29:21Z

5

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.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Dec 14, 2012 at 0:26

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Postgres trigger to open http connection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related