postgresqltriggerstransactionsnotificationsdeferred-execution

Executing a trigger AFTER the completion of a transaction

In PostgreSQL, are DEFERRED triggers executed before (within) the completion of the transaction or just after it?

The documentation says:

DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command).

It doesn't specify if it is still inside the transaction or out. My personal experience says that it is inside the transaction and I need it to be outside!

Are DEFERRED (or INITIALLY DEFERRED) triggers executed inside of the transaction? And if they are, how can I postpone their execution to the time when the transaction is completed?

To give you a hint what I'm after, I'm using pg_notify and RabbitMQ (PostgreSQL LISTEN Exchange) to send out messages. I process such messages in an external application. Right now I have a trigger which notifies the external app of the newly inserted records by including the record's id in the message. But in a non-deterministic way, once in a while, when I try to select a record by its id at hand, the record can not be found. That's because the transaction is not complete yet and the record is not actually added to the table. If I can only postpone the execution of the trigger for after the completion of the transaction, everything will work out.

In order to get better answers let me explain the situation even closer to the real world. The actual scenario is a little more complicated than what I explained before. The source code can be found here if anyone's interested. Becuase of reasons that I'm not gonna dig into, I have to send the notification from another database so the notification is actually sent like:

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

Which I'm sure makes the whole situation much more complicated.

Solution

  • Triggers (including all sorts of deferred triggers) fire inside the transaction.

    But that is not the problem here, because notifications are delivered between transactions anyway.

    The manual on NOTIFY:

    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. But it can be disconcerting if one is expecting the notification events to be delivered immediately. Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot "take back" a notification once it has sent it to the client. So notification events are only delivered between transactions. The upshot of this is that applications using NOTIFY for real-time signaling should try to keep their transactions short.

    Bold emphasis mine.

    pg_notify() is just a convenient wrapper function for the SQL NOTIFY command.

    If some rows cannot be found after a notification has been received, there must be a different cause! Go find it. Likely candidates:

    Either way, like the manual suggests, keep transactions that send notifications short.

    dblink

    Update: Transaction control in a PROCEDURE or DO statement in Postgres 11 or later makes this a lot simpler. Just COMMIT; to (also) send waiting notifications.

    Original answer (mostly for Postgres 10 or older):

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

    ... which should be rewritten with format() to simplify and make the syntax secure:

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

    dblink is a game-changer here, because it opens a separate transaction in the other database. This is sometimes used to fake autonomous transaction.

    dblink() waits for the remote command to finish. So the remote transaction will most probably commit first. The manual:

    The function returns the row(s) produced by the query.

    If you can send notification from the same transaction instead, that would be a clean solution.

    Workaround for dblink

    If notifications have to be sent from a different transaction, there is a workaround with dblink_send_query():

    dblink_send_query sends a query to be executed asynchronously, that is, without immediately waiting for the result.

    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
$$;

    If you do this right before the end of the transaction, your local transaction gets 3 seconds (pg_sleep(3)) head start to commit. Chose an appropriate number of seconds.

    There is an inherent uncertainty to this approach, since you get no error message if anything goes wrong. For a secure solution you need a different design. After successfully sending the command, chances for it to still fail are extremely slim, though. The chance that successful notifications are missed seem much higher, but that's built into your current solution already.

    Safe alternative

    A safer alternative would be to write to a queue table and poll it like discussed in @Bohemian's answer. This related answer demonstrates how to poll safely: