Thread: Asynchronous Trigger?
Is it possible to have the execution of a trigger (or any function) not block the completion of the statement they are associated with?
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.
thanks!
--Cory
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
On Thu, 2018-03-29 at 22:29 +0000, 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? > 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). This is a good use of a Notification. A trigger can queue a notification, which can be received by a listening process - which can then determine and do whatever is necessary. See the "LISTEN" documentation. -- Adam Tauno Williams <mailto:awilliam@whitemice.org> GPG D95ED383 OpenGroupware Developer <http://www.opengroupware.us/>
On Thu, Mar 29, 2018 at 5:29 PM, Cory Tucker <cory.tucker@gmail.com> 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? > > 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. Generally the idea is to (in the trigger) invoke some low risk quick action such as inserting a record in to a 'stuff to do' table. Then, some other process comes around and does the heavy lifting. merlin
As suggested, note in ToDo table, also maybe look at LISTEN and NOTIFY and have a job runner process LISTENing (and cleaning up the queue, or, marking an item as in progress if you've multiple workers) The work queue table is to help maintain state...if noone is LISTENing then the table acts as backup so when a worker fires up it can start working on whatever's already there and empty that. On Fri, Apr 27, 2018 at 4:46 AM, Olleg Samoylov <splarv@ya.ru> wrote: > Try to look at PGQ from SkyTools. > > On 2018-03-30 01:29, 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? > > 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. > > thanks! > --Cory > > -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler
On Fri, 2018-04-27 at 11:25 -0600, Michael Loftis wrote: > As suggested, note in ToDo table, also maybe look at LISTEN and > NOTIFY and have a job runner process LISTENing We use a simple python process to listen with a PostgreSQL cursor for NOTIFY events, and push them into RabbitMQ (message broker). From there you can do a lot of asynchronous work; it will store messages, optionally with expiration, you have point-to-point or broadcast, etc... It is a very durable, scalable, and flexible way to built up an event driven system. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awilliam@whitemice.org GPG#D95ED383 Web: http://www.marp.org