执行事务完成后的触发器

Question

在PostgreSQL中，DEFERRED触发器是在事务完成之前(内部)执行还是仅在事务完成后执行? 文档表示：

DEFERRABLE NOT DEFERRABLE

这控制约束是否可以延迟。不可延迟的约束条件将在每个命令之后立即检查。可延迟的约束条件可以被推迟到事务结束时（使用SET CONSTRAINTS命令）。

它没有指定它是否仍然在事务内或外。我的个人经验是它在事务内部，但我需要它在事务外部！

DEFERRED（或INITIALLY DEFERRED）触发器是否在事务内执行？如果是，那么如何将它们的执行推迟到事务完成时？

为了给你一个提示，我正在使用pg_notify和RabbitMQ（PostgreSQL LISTEN Exchange）发送消息。我在外部应用程序中处理这些消息。现在我有一个触发器，通过将记录的id包含在消息中，通知外部应用程序新插入的记录。但是，在非确定性的情况下，偶尔会出现这样的情况：当我尝试根据手头的id选择记录时，无法找到记录。那是因为事务还没有完成，记录实际上还没有添加到表中。如果我只能将触发器的执行推迟到事务完成后，一切都会解决。

为了获得更好的答案，让我更接近真实世界地解释一下情况。实际情况比我之前解释的要复杂一些。如果有人感兴趣，可以在此处找到源代码。由于我不想深入挖掘的原因，我必须从另一个数据库发送通知，因此通知实际上是这样发送的：

PERFORM * FROM dblink('hq','SELECT pg_notify(''' || channel || ''', ''' || payload || ''')');

我相信这会让整个情况更加复杂。

- Mehran

---

也许标题应该是类似于“如何在事务提交后发送通知”。 - Erwin Brandstetter

@Bohemian 不要选择轮询方式。这是一个糟糕的设计，只适用于新增数据。我还需要考虑更新和删除数据的情况。当然，你可以将更新和删除转化为添加操作（保留日志表），但我也希望能够让多个程序在同一个数据库上运行（以保持数据的最新状态）。长话短说，永远不要选择轮询方式，它从来都不是解决方案。 - Mehran

@mehran，关于更新的问题，你可以通过触发器在更新时将ID插入到队列表中。再次提到“never”，我曾经在澳大利亚最大和最赚钱的网站之一上实现了完全相同的功能（使用触发器在更新、删除和插入时将数据插入到“队列”表中），并且它运行得非常完美，性能非常高，并且实现起来非常简单，就是这样 :p - Bohemian

@Bohemian，您的解决方案将是我最后的选择，希望永远不会到那一步。我在这里尝试做的事情非常依赖于整个系统尽可能轻量级。感谢您的建议，我会记在心里的。 - Mehran

@Mehran，轮询永远是必须的。你曾经接触过的所有系统，你认为它们在推进，实际上都是在幕后进行轮询和排队，这样你就不必看到它。 - Kirk Roybal

Answer 1

触发器（包括所有延迟触发器）在事务内部触发。

但这不是问题所在，因为通知无论如何都是在事务之间传递的。 关于NOTIFY的手册： 《通知（NOTIFY）》与SQL事务有一些重要的交互方式。首先，如果在事务内部执行了一个《通知（NOTIFY）》，则除非提交该事务，否则不会传递通知事件。这是合适的，因为如果事务被中止，则其中所有命令均无效，包括《通知（NOTIFY）》。但是，如果期望立即传递通知事件，这可能会让人不安。其次，如果正在监听某个会话，并在事务内收到通知信号，则直到完成该事务（提交或中止）后才将通知事件传递给连接的客户端。同样，推理是，如果在稍后中止的事务中传递了通知，则希望以某种方式撤消通知 - 但是一旦服务器将其发送到客户端，就无法“收回”通知。 因此，《通知（NOTIFY）》事件只在事务之间传递。由此产生的结果是，使用《通知（NOTIFY）》进行实时信令的应用程序应尝试保持事务短小。

pg_notify() 是 SQL 的 NOTIFY 命令的方便包装函数。

如果在接收通知后找不到某些行，则必须有不同的原因！去找出它。可能的候选者：

• 并发事务干扰
• 触发器执行更多或与您认为的不同的操作。
• 各种编程错误。

无论如何，像手册建议的那样，保持发送通知的事务短暂。

dblink

更新: 在 Postgres 11 或更高版本中，PROCEDURE 或 DO 语句中的事务控制使此过程变得更加简单。只需进行 COMMIT; 就可以（也）发送等待的通知。

---

原始回答（主要适用于Postgres 10或更早版本）：

PERFORM * FROM dblink('hq','SELECT pg_notify(''' || channel || ''', ''' || payload || ''')');

应该使用format()对其进行重写，以简化并使语法更安全。

PRERFORM dblink('hq', format('NOTIFY %I, %L', channel, payload));

"dblink"在这里是一个革命性的工具，因为它在另一个数据库中打开了一个单独的事务。有时会用于伪造“自主事务”。

• Postgres支持嵌套或自主事务吗？

• 如何在PostgreSQL中进行大规模非阻塞更新？

dblink()等待远程命令完成。所以远程事务很可能会先提交。手册：

该函数返回查询产生的行。

如果您可以从同一事务发送通知，那将是一个干净的解决方案。

dblink的解决方法

如果通知必须从不同的事务中发送，则可以使用dblink_send_query()进行解决：

dblink_send_query将查询异步地发送到要执行的查询，即不会立即等待结果。

DO  -- or plpgsql function
$$
BEGIN
   -- do stuff

   PERFORM dblink_connect   ('hq',   'your_connstr_or_foreign_server_here');
   PERFORM dblink_send_query('con1', format('SELECT pg_sleep(3); NOTIFY %I, %L ', 'Channel', 'payload'));
   PERFORM dblink_disconnect('con1');
END
$$;

如果您在事务结束之前正确执行此操作，则本地事务将获得 3 秒（pg_sleep(3)）的先行时间来提交。选择适当的秒数。

这种方法存在固有的不确定性，因为如果出现任何问题，您将得不到错误消息。对于一个安全的解决方案，您需要一个不同的设计。但是，成功发送命令后，它仍然失败的机会极小。错过成功通知的机会似乎更高，但这已经内置在您当前的解决方案中了。

安全的替代方案

更安全的替代方案是写入队列表并像@Bohemian's answer中讨论的那样进行轮询。这个相关的答案演示了如何安全地轮询：

• Postgres UPDATE … LIMIT 1

- Erwin Brandstetter

---

谢谢你的回答。说实话，我的场景并不完全真实。我会更新问题以匹配真正的问题。 - Mehran

@Mehran：我还有一个更好的想法。考虑一下更新。顺便说一句，那个不太可能的时间巧合纯属巧合。你恰好在我完成更新的一分钟之前接受了我的答案。 :) - Erwin Brandstetter

我已经测试了 pg_sleep，但有点不同。我定义了一个存储过程，基本上就是你在这里写的。但它使整个情况变得更糟糕！如果我面对的问题少于10％的时间，使用 pg_sleep 就会达到100％！我不知道为什么，最终我实施了轮询系统（出于几个原因），所以很难让我再尝试这个解决方案。但还是谢谢。 - Mehran

@Mehran：你使用了dblink_send_query吗？那是解决方法中的重要部分。我测试过了，它对我有效：通知在我的调用事务完成后几秒钟才到来。无论如何，我认为队列解决方案可能是更好的设计。 - Erwin Brandstetter

你说得对。我用普通的 db_link 进行了测试。不幸的是，我没有测试集来尝试使用 db_link_query。我完全同意你和 Bohemian 的观点，在这里轮询是更好的设计（而不是一般情况下）。非常感谢你的时间和努力。 - Mehran

Answer 2

我将这篇文章作为回答，假设你试图解决的实际问题是延迟执行外部进程直到事务完成（而不是你正在尝试使用触发器 Kung Fu 解决的 X-Y“问题”）。

让数据库告诉应用程序要做某件事是一种错误的模式。这是错误的原因：

1. 如果应用程序没有收到消息，例如因为它关闭了、网络故障等等，就没有后备措施。即使应用程序回复确认（它不能），也无法解决此问题（见下一点）
2. 如果应用程序收到消息但未能完成工作（由于许多原因），则没有明智的重试工作方式

相比之下，使用数据库作为持久队列，并让应用程序轮询它以获取工作，并在完成工作时从队列中取出工作，不具有上述任何问题。

有很多实现方法。我喜欢的方法是让某个进程（通常是插入、更新和删除触发器）将数据放入“队列”表中。另一个进程轮询该表以获取要执行的工作，并在完成工作时从表中删除。

它还增加了其他一些好处：

• 生产和消费工作是解耦的，这意味着您可以安全地关闭和重启应用程序（必须定期进行，例如部署）-当应用程序关闭时，队列表将快乐地增长，并且在应用程序重新启动时排空。甚至可以用全新的应用程序替换该应用程序
• 如果出于任何原因，您想要启动某些项的处理，只需手动将行插入队列表即可。我自己使用这种技术来启动需要通过一次放置在队列上进行初始化的数据库中的所有项的处理。重要的是，我不需要对每行进行敷衍的更新以触发触发器
• 回答你的问题，可以通过将时间戳列添加到队列表中，并使轮询查询仅选择早于（例如）1秒的行来引入轻微延迟，从而给数据库完成其事务的时间

您无法超负荷使用应用程序。应用程序只会读取它可以处理的工作量。如果您的队列正在增长，则需要更快的应用程序或更多应用程序。如果有多个使用者在操作，则并发问题可以通过（例如）向队列表添加“token”列来解决。

由数据库表支持的队列是商业级基于队列的平台实现持久队列的基础，因此该模式经过了充分的测试、使用和理解。

让数据库做它最擅长的事情，也是它唯一做得好的事情：管理数据。不要试图将数据库服务器变成应用服务器。

- Bohemian

---

非常感谢您分享您的经验，这真的很有帮助。尽管我正在考虑您的解决方案用于我的项目，但不幸的是它不能被接受作为这个特定问题的答案。无论如何，再次感谢。 - Mehran

@mehan 任何答案都可以被接受。如果这个答案是“最佳”的解决方案，那么请接受它。如果另一个答案更好，请接受它。这是一个主观的问题。 - Bohemian