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

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/&gt;

<div text="#000000" bgcolor="#FFFFFF">
Try to look at PGQ from SkyTools.<br />
<br />
<div>On 2018-03-30 01:29, Cory Tucker wrote:<br />
</div>
<blockquote type="cite" cite="mid:CAG_=8kAdbc_DoJWy11xh5J8b+v-ZWSt3Ty-1Ffdn4scgq3wK6Q@mail.gmail.com">
<div dir="ltr">
<div>Is it possible to have the execution of a trigger (or any
function) not block the completion of the statement they are
associated with?</div>
<div><br />
</div>
<div>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).</div>
<div><br />
</div>
<div>Any suggestions welcome.</div>
<div><br />
</div>
<div>thanks!</div>
<div>--Cory</div>
</div>
</blockquote>
<br />
</div>

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