批量更新数据库邮件记录异常：邮件重复发送且状态未更新

后台任务发送邮件后，Mailhog收件箱中出现重复发送的情况，且数据库中邮件记录的IsSent字段始终为false，EmailSentAt字段也未被设置。手动执行数据库函数udf_UpdateEmailView能正常更新这些字段，但通过代码中的BulkUpdateEmailStatusAsync方法调用却完全无效。目前只能在Docker环境运行程序，无法本地调试，只能通过排查猜测问题。

相关代码 #

手动执行的SQL语句 #

SELECT public.udf_UpdateEmailView(
    p_is_sent => TRUE,
    p_email_sent_at => NOW(),
    p_email_ids => ARRAY[1, 2]
);

仓储层方法：BulkUpdateEmailStatusAsync #

public async Task BulkUpdateEmailStatusAsync(List<int> emailIds, bool isSent, CancellationToken cancellationToken)
{
    var sql = @"
        SELECT public.udf_UpdateEmailView(
            p_is_sent => @p_is_sent,
            p_email_sent_at => @p_email_sent_at,
            p_email_ids => @p_email_ids
        );
    ";

    var emailSentAt = isSent ? (object)DateTimeOffset.UtcNow : DBNull.Value;

    await DbContext.Database.ExecuteSqlRawAsync(
        sql,
        new NpgsqlParameter("@p_is_sent", NpgsqlTypes.NpgsqlDbType.Boolean) { Value = isSent },
        new NpgsqlParameter("@p_email_sent_at", NpgsqlTypes.NpgsqlDbType.TimestampTz) { Value = emailSentAt }, //I set this to true. Still nothing
        new NpgsqlParameter("@p_email_ids", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer) { Value = emailIds.ToArray() }
    );
}

后台任务：EmailSendingJob的ExecuteAsync方法 #

public async Task ExecuteAsync(CancellationToken cancellationToken)
{
const int batchSize = 50;
const int maxEmptyFetchCount = 5;

int emptyFetchCount = 0;

try
{
    while (true)
    {
        var pendingEmails = await _emailRepository.GetPendingEmailsAsync(batchSize, cancellationToken);

        if (!pendingEmails.Any())
        {
            emptyFetchCount++;

            if (emptyFetchCount >= maxEmptyFetchCount)
            {
                break;
            }
            continue;
        }

        emptyFetchCount = 0;

        var groupedEmails = pendingEmails
            .GroupBy(email => new { email.Subject, email.Body })
            .ToList();

        foreach (var group in groupedEmails)
        {
            var recipients = group
                .SelectMany(email => email.To.Split(';', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries))
                .Distinct()
                .ToArray();

            var validRecipients = new List<string>();
            foreach (var recipient in recipients)
            {
                if (string.IsNullOrEmpty(recipient))
                {
                    continue;
                }

                try
                {
                    var mailAddress = new System.Net.Mail.MailAddress(recipient);
                    validRecipients.Add(mailAddress.Address);
                }
                catch (FormatException)
                {
                }
            }

            if (!validRecipients.Any())
            {
                continue;
            }

            try
            {
                var successfullySentEmails = await _emailSenderService.SendEmailAsync(validRecipients.ToArray(), group.Key.Subject, group.Key.Body);

                if (successfullySentEmails.Any())
                {
                    var emailIdsToUpdate = group
                        .Where(email => successfullySentEmails.Contains(email.To))
                        .Select(email => email.Id)
                        .ToList();

                    await _emailRepository.BulkUpdateEmailStatusAsync(emailIdsToUpdate, true, cancellationToken);
                }
            }
            catch (Exception ex)
            {
            }
        }
    }
}

补充说明 #

添加while(true)是为了批量处理，当批量大小为50且有100封邮件时，确保所有邮件都能被发送。

问题排查与解决方案 #

1. 收件人匹配逻辑错误 #

后台任务中，successfullySentEmails返回的是单个标准化邮箱地址，而email.To是分号分隔的字符串，导致successfullySentEmails.Contains(email.To)永远不成立，根本没生成需要更新的邮件ID列表。修改匹配逻辑：

var emailIdsToUpdate = group
    .Where(email => 
        email.To.Split(';', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries)
               .Any(recipient => successfullySentEmails.Contains(recipient))
    )
    .Select(email => email.Id)
    .ToList();

2. 参数匹配异常 #

PostgreSQL函数参数名大小写可能敏感，代码中用@p_is_sent的命名方式可能不匹配函数定义。可以改用位置参数规避命名问题：

SELECT public.udf_UpdateEmailView($1, $2, $3);

然后按isSent、emailSentAt、emailIds的顺序传入参数。

3. 事务未提交 #

如果上下文事务未自动提交，更新操作会被回滚。在仓储方法中显式控制事务：

using var transaction = await DbContext.Database.BeginTransactionAsync(cancellationToken);
try
{
    await DbContext.Database.ExecuteSqlRawAsync(sql, 
        new NpgsqlParameter(NpgsqlTypes.NpgsqlDbType.Boolean) { Value = isSent },
        new NpgsqlParameter(NpgsqlTypes.NpgsqlDbType.TimestampTz) { Value = emailSentAt },
        new NpgsqlParameter(NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer) { Value = emailIds.ToArray() }
    );
    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

4. 异常被吞掉 #

任务中捕获异常但无任何输出，导致更新失败的错误无法被发现。添加日志输出（Docker中可通过docker logs查看）：

catch (Exception ex)
{
    Console.WriteLine($"邮件处理失败：{ex.Message}\n{ex.StackTrace}");
}

5. 时间类型转换问题 #

将DateTimeOffset.UtcNow改为DateTime.UtcNow，确保与PostgreSQL的TimestampTz类型完全匹配：

var emailSentAt = isSent ? (object)DateTime.UtcNow : DBNull.Value;

内容的提问来源于stack exchange，提问作者Guga Todua