Postgresql – Fire trigger after the complete transaction commit

postgresqltrigger

I try to call an external script with a trigger when I INSERT on my table.

This script have to get some data, especially the last row added (which one fired the trigger)

According to the documentation, CONSTRAINT TRIGGER and DEFERRABLE are executed inside the transaction so my external script can't get the last row added.

How can I fire my trigger after the transaction completely commited ?

Best Answer

You can get a behaviour similar to what you're asking by using the LISTEN / NOTIFY mechanism of PostgreSQL. You can use the payload of the NOTIFY to pass values to your external process.

From the documentation:

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

[...]

NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.

(emphasis mine)

The difference between this and a trigger is: the external script will not be started by the database. It needs to be a process that is programmed using a language that allows for connections to the database and some kind of callback system, and that can be started, and be listening to all notifications from the database. When a notification (message) is received, it needs to act accordingly. Your process will behave somehow like a daemon or service. I know you can do this using a program written in C, C++ or C#, but I'm not sure it can be done with a shell script.

See also Asynchronous Notification.

Related Question