Re: Asynchronous Trigger?

Cory Tucker wrote:
> Is it possible to have the execution of a trigger (or any function) not block the
> completion of the statement they are associated with?

Constraint triggers can be deferred to the end of the transaction, but I
am certain that is not what you are looking for.

> A pattern I had hoped to implement was to do a quick update of rows that signaled
> they needed attention, and then an async per-row trigger would come and do the
> maintenance (in this case, make an expensive materialized view).
>
> Any suggestions welcome.

This seems like a really bad idea - even if it happens "in the background", would
you want an expensive materialized view to be updated whenever a row is modified?
If you modify three rows, that would happen three times in a row!

One solution would be to set a flag somewhere that marks the materialized view
dirty, and a regular cron-job that checks the flag and rebuilds the view.
With the asynchronous solution you had in mind, the materialized view would not
be up to date anyway.

Other than that, if your materialized view definition is simple enough, you might
be able to use a regular table and a trigger that updates the table to reflect
the modifications caused by the insert, update or delete.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com