PostgreSQL database trigger execution context

Question

I want to implement an audit trail using triggers that run on created, modified, and deleted data to store some values. These triggers must be able to use the identifiers of the users who made changes and who are controlled by the web application. I have some ideas for providing this data, but I don't seem to fully understand what the context of the trigger execution is. I read the PostgreSQL docs. Trigger behavior overview and others, but my question didn't seem to answer.

What I want to know is the interaction between the client session with one running transaction and the execution of the trigger and the lifetime of both and how they depend on each other. From my understanding, triggers are executed in the database regardless of the client session that generated the event that triggers the launch. It's right? This means that the triggers and their processing will not affect the performance of the client request, and the client can close the session at any time. If they are both independent, how will the trigger receive a notification that the client is rolling back from the transaction, which logically means that the data has not changed at all? Or onyl triggers executed after a transaction because they work independently?

Or did the triggers execute async in the client session that generated the events that led to the launch being launched? This would mean that if the client closes the session for any reason, the trigger will also abort. Their changes are directly related to customer transactions and can be thrown back.

I need to understand the behavior in order to find out what I would like to do in another question .

Thanks for your input!

Answer 1

From my understanding, triggers are executed in the database regardless of the client session that generated the event that caused the execution to run. It's right? This would mean triggers and their processing would not affect the performance of the client request and the client can close the session at any time.

No, they are completely dependent on the client session, as part of a transaction that is itself bound to the session. See this excerpt from CREATE TRIGGER (9.1):

They can be triggered either at the end of the instruction that triggers the triggering event or at the end of the contained transaction; in the latter case, they are considered deferred

From your other question , it looks like you are using 8.4, which has no pending triggers, so it is even simpler. Triggers are always triggered at the end of the instruction (trigger event), which means that the execution acknowledgment is sent by the server to the client.

A COMMIT following it will be a new instruction and cannot be executed until the trigger completes.