根据每个数据库记录时间戳发送通知电子邮件 [英] Sending notification emails based on each db record timestamp

问题描述

我有一个事件"(会议，生日聚会等)记录的数据库，每个事件都有一个 endTime 字段.活动结束后，我想为每个过去的活动发送2-3封电子邮件.

I have a db of "event" (a conference, birthday party etc.) records, and each event has an endTime field. After an event ends, I'd like to send out 2-3 emails for each past event.

现在，我每小时执行一次cron作业，以查找在过去一个小时内结束的所有事件，因此它看起来像:

Right now, I run an hourly cron job to find all events that has ended in the past hour, so it looks something like:

• 在2:01 pm:查找所有在1-2pm之间结束的事件，发送电子邮件
• 下午3:01:查找所有在2-3pm之间结束的事件，发送电子邮件
• 下午4:01:查找所有在3-4pm之间结束的事件，发送电子邮件
• 下午5:01:...

但是，如果说3:01 pm工作由于某种原因失败(Heroku使我的休息api崩溃，电子邮件服务提供商崩溃等)，则在2-3pm之间结束的事件将不会收到电子邮件.

However, say when the 3:01pm job fails for some reason (Heroku crashes my rest api, email service provider goes down etc.), the events ending between 2-3pm won't get emails.

目前，如果电子邮件发送成功，则我的数据库记录未标记为 notified .我应该这样做，以便将cron脚本更改为在当前时间之前在 notified = false 之前查找所有记录"(捕获所有以前未发送的记录)吗?然后，您会为成功发送的每种电子邮件类型设置一个标志吗?

At the moment, my db records are not marked as notified if the emails were sent successfully. Should I do that so I can change the cron script to "find all records before current time where notified=false" (which catches all previously unsent records)? Would you then set a flag for each type of email you send out successfully?

还是有一种更聪明的方法来避免设置标志?

Or is there a cleverer way that avoids setting flags?

(这个问题的标题和标签很麻烦，欢迎提出建议/编辑！)

(It was hell coming up with the title and tags for this question — suggestions/edits welcome!)

推荐答案

notified 标志的问题在于，它需要一个索引来使 WHERE notifyed = FALSE ¹ 高效，但TRUE值仍保留在索引中，尽管再也不会在查询中使用.

The problem with notified flag is that it requires an index just to make WHERE notified = FALSE¹ efficient, yet TRUE values stay in the index despite never being used in a query ever again.

随着时间的流逝，索引中将有一个小的FALSE部分(这很有用)和一个巨大的TRUE部分(这只会浪费空间).如果您的DBMS支持，请使用某种部分索引从索引中删除TRUE:

In time, you'll have a small FALSE portion in the index (which is useful) and a huge TRUE portion (which just wastes space). If your DBMS supports it, use a partial index of some sort to remove the TRUE from the index:

• Oracle 没有索引NULL ，因此用它代替TRUE.
• 在MS SQL Server下使用过滤索引，例如: CREATE INDEX ...通知的地方= 0 .
• 在PostgreSQL下使用部分索引.

• Oracle doesn't index NULLs, so use that instead of TRUE.
• Use filtered index under MS SQL Server, for example: CREATE INDEX ... WHERE notified = 0 .
• Use partial index under PostgreSQL.

或者，创建一个仅具有一行和一个字段的额外表:LAST_SENT ² ，并在成功发送一堆通知时更新该字段.当然，这可以防止乱序发送通知，并且您将需要谨慎处理部分故障，因为您不能只是跳过不成功的通知.

Alternatively, create an extra table with exactly one row and one field: LAST_SENT², and update that field whenever a bunch of notifications have been succesfully sent. This of course precludes sending notifications out of order and you'll need to handle partial failures carefully, since you can't just skip the unsuccessful notifications.

¹ 用适合您的数据库的值替换FALSE.

¹ Replace FALSE with whatever is appropriate for your database.

² 如果表使用自动递增的整数作为主键，请考虑将其用于LAST_SENT而不是时间戳，以避免出现时间戳可能不唯一的极端情况.

² If your table uses an auto-incremented integer as primary key, consider using that for LAST_SENT instead of timestamp, to avoid edge cases where timestamps may not be unique.

这篇关于根据每个数据库记录时间戳发送通知电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！