How can I send email from PostgreSQL trigger?

by

See the excellent-as-usual depesz article, and pg-message-queue.

Sending email directly from the database may not be a great idea. What if DNS resolution is slow and everything hangs for 30 seconds then times out? What if your mail server is having a wobbly and takes 5 minutes to accept messages? You’ll get database sessions hung up in your trigger until you’re at max_connections and suddenly you can’t do anything but wait or start manually cancelling transactions.

What I’d recommend is having your trigger NOTIFY a LISTENing helper script that remains permanently running and connected to the DB (but not in a transaction).

All your trigger has to do is INSERT a row into a queue table and send a NOTIFY. Your script gets the NOTIFY message because it has registered to LISTEN for it, examines the queue table, and does the rest.

You can write the helper program in whatever language is convenient; I usually use Python with psycopg2.

That script can send the email based on information it finds in the database. You don’t have to do all the ugly text formatting in PL/PgSQL, you can substitute things into a template in a more powerful scripting language instead, and just fetch the variable data from the database when a NOTIFY comes in.

With this approach your helper can send each message and only then remove the info from the queue table. That way if there are transient problems with your mail system that causes sending to fail, you haven’t lost the info and can continue to attempt to send it until you succeed.

If you really must do this in the database, see PgMail.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. How to update selected rows with values from a CSV file in Postgres?
  5. Optimise PostgreSQL for fast testing
  6. Transpose latest rows per user to columns
  7. How to delete duplicate rows without unique identifier
  8. Best way to check for “empty or null value”
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. SQL Between clause with strings columns
  11. Insert an image in postgresql database
  12. NOT NULL constraint over a set of columns
  13. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  14. How do I alter the position of a column in a PostgreSQL database table?
  15. Query that ignore the spaces
  16. How to find a table having a specific column in postgresql
  17. Continuing a transaction after primary key violation error
  18. INSERT COMMAND :: ERROR: column “value” does not exist
  19. Use email address as primary key?
  20. Bulk/batch update/upsert in PostgreSQL
  21. incomplete information from query on pg_views [duplicate]
  22. SQL query to find record with ID not in another table
  23. How to show row numbers in PostgreSQL query?
  24. Postgres Error: More than one row returned by a subquery used as an expression
  25. Grant privileges for a particular database in PostgreSQL
  26. Upsert with a transaction
  27. PostgreSQL how to create a copy of a database or schema?
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment