How to email each new record?

Asked

Viewed 33 times

1

I’m using Postgres 9.6 in a project and one of the requirements is that the system send an email to each new client.

1 - Trigger is a good way to do?

2 - When it comes to performance. What would be the impact in sending these emails by Trigger?

3 - Or this is a job for a service in the background ?

postgresql

  • First: Do you have a backend for entering the data into the database? I do not know if there is how to do by postgree, but as email usually takes a few seconds to send (if it is SMTP), I would avoid doing this.

  • For that is my doubt the question precisely in the delay in sending would delay the CRUD process

  • 1

    Fabrício, depending on the situation, it is necessary to analyze if Trigger could fire a command line. Then maybe you could use Sendmail.

  • When I ask you, what are you wearing?

  • @Wallacemaxters is a very useful idea

  • Take a look in that stuff

  • @Wallacemaxters "What are you using? "?

  • OS = Operating system.

  • Let’s go continue this discussion in chat.

Show 4 more comments

2 answers

2

Hello!

I wouldn’t do it using the database, I would do it using the programming language. Most current languages support the execution of asynchronous tasks, which helps in case of delay in sending.

Here in this link tells why it is not a good idea to send emails using the database: https://stackoverflow.com/a/12003516/887882

Sending email directly from the database may not be a good idea. And if DNS resolution is slow and everything is stopped at 30 seconds and expire? What if your email server has trouble and take 5 minutes to accept messages? You will get database sessions suspended on your trigger until you are on max_connections and suddenly you can’t do anything but wait or start manually cancelling transactions.

  • most current languages support the execution of asynchronous tasks... I’ll cry, php has no :\

  • 1

    Since PHP does not have it, you can create a small PHP file that does this and run in the background.

  • And I’m not so sure, but there are some PHP libraries that allow multithread.

  • I was only joking, Elisângela. I currently use Laravel (php framework) to create Queues (queues) in the database and run them on another plane.

1


As the previous answer said, you should think about the question of performance. And by directly answering your question about "Or is this a job for a background service?" I would say it would be advisable to do so, yes.

Think of the problems that could be occasioned if you enter yourself 10 records and suddenly 3 of these email sending processes crash. Or even think of a scenario, if you were using a g-mail SMTP, for example, and this request took about 15 seconds to be solved (as happened to me in some cases). This would definitely affect the performance of your database.

I would think of another question: it is not the responsibility of my database to send an email. So I would do this yes via a command line or, as already suggested, using an application backend to do this.

As I suggested in my comment, I might consider using a TRIGGER to fire a command running in Background, as the link below:

In my humble opinion: I think this kind of thing should be done in the application layer.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.