4

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 ?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • 2
    I was hoping someone would know a simpler solution. I've done this approach in the past, but the problem with it is that you really-really need to make sure that your daemon always works, because there is no error message if no one received the notification. The other problem with the approach is that it's a lot of work which is especially painful if the thing you want to run is one line of code.
    – soger
    Commented Dec 7, 2018 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.