PostgreSQL中数据库触发器的执行上下文 [英] execution context of database trigger in PostgreSQL

问题描述

我想使用触发器来实施审核日志，该触发器在创建，更改和删除的数据上触发以存储一些值。这些触发器应该能够使用进行更改并由Web应用程序管理的用户ID。我对提供这些数据有一些想法，但是我似乎并不完全了解触发器的执行上下文。我已经阅读了PostgreSQL文档触发器行为概述和其他，但是我的问题似乎没有得到解决。

I want to implement an audit log using triggers which gets fired on created, changed and deleted data to store some values. Those triggers should be able to use user ids which made the changes and which are managed by the web application. I have some ideas on providing this data, but I don't seem to fully understand what the execution context of a trigger is. I've read through the PostgreSQL docs Overview of Trigger Behavior and others but my question doesn't seem to be answered.

我想知道的是一个正在运行的事务的客户端会话与触发器执行之间的交互以及两者的寿命以及它们如何相互依赖。据我了解，触发器是在数据库中独立于客户端会话执行的，而客户端会话会创建导致触发器执行的事件。那是对的吗？这意味着触发器及其处理不会影响客户请求的性能，客户可以随时关闭会话。如果两者都是独立的，那么如何触发触发器通知客户回滚事务，这在逻辑上意味着根本没有数据被更改？还是在提交事务后由于触发器ONyl独立运行而执行了触发器onyl？

What I want to know is the interaction between a client session with one running transaction and the trigger execution and the lifetime of both and how they depend on each other. From my understanding triggers are executed within the database independently from the client session which created the event which lead to trigger execution. Is that correct? That would mean triggers and their processing wouldn't impact performance of the client request and the client can close the session at any time. If both are independent, how would a trigger get notified about a client rolling back a transaction, which would logically mean that no data got changed at all? Or are triggers onyl executed after committing a transaction because they run independently?

还是在客户端会话中异步执行了触发器，从而创建了导致触发器执行的事件？这意味着如果客户端出于任何原因关闭其会话，触发器也将中止。他们的更改直接与客户交易绑定，也可以回滚。

Or are triggers executed async within the client session which created the events which lead to trigger execution? This would mean that if the client closes it's session for any reason, the trigger would abort, too. Their changes are directly bound to the clients transaction and can be rolled back, too.

我需要了解行为，以了解我想在另一个问题。

I need to understand the behavior to know what I would like to do in another question.

感谢您的输入！

推荐答案

据我了解，触发器是在数据库
中独立于创建该事件的客户端会话执行的， b $ b导致触发执行。那是对的吗？这将意味着触发
，其处理不会影响客户端请求
的性能，并且客户端可以随时关闭会话。

From my understanding triggers are executed within the database independently from the client session which created the event which lead to trigger execution. Is that correct? That would mean triggers and their processing wouldn't impact performance of the client request and the client can close the session at any time

不，它们完全取决于客户端会话，这是与会话本身相关的事务的一部分。
请参见创建触发器（9.1 ）：

No they totally depend on the client session, as part of the transaction which itself is tied to the session. See this excerpt from CREATE TRIGGER (9.1):

它们可以在引起
触发事件的语句末尾或在包含交易；在
后一种情况下，它们被称为递延

来自您的其他问题看来您使用的是8.4，它没有延迟的触发器，因此更加简单。触发器始终在语句的结尾（触发事件）运行，这意味着服务器在将执行确认发送给客户端之前。

From your other question it appears you're using 8.4, which doesn't have deferred triggers, so it's even simpler. Triggers run always at the end of the statement (the triggering event), which means before the acknowledgment of execution is sent by the server to the client.

紧随其后的COMMIT将是一条新指令，并且无法在触发器完成之前执行。

A COMMIT immediately following would be a new instruction, and could not be executed before the trigger is finished.

这篇关于PostgreSQL中数据库触发器的执行上下文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！