Postgres : Post statement (or insert) asynchronous, non-blocking processing.

Question

I'm wondering if it is possible, that after a collection of rows is inserted, to initiate an operation that is executed asynchronously, is non-blocking, and doesn't need to inform the originator of the request - of the result.

I am working with large amounts of events and I can guarantee that the post-insert logic will not fail -- I just want to have a single insert thread in my event-sources, and I want this thread to keep flying without blocking, and without being responsible for any post-delivery book-keeping.

I can tell you that I would potentially have a 100 of these jobs executing concurrently and each job might operate on 5 tables with anywhere between 200-1000 inserts on each of these tables.

A hint in the right direction should be enough.

Answer 1

Since you are actually just simply batch processing a large amount of rows, why not let a cron job post process the rows when you're done with them?

Just mark them as non-processed (or redirect them to a processing table with a trigger) and process them all at once when you're done.

Answer 2

LISTEN/NOTIFY might be what you need, have a listener on a separate connection run a LISTEN notifyname (it can use a nonblocking socket and use poll, or however your application is working), and your code run *NOTIFY notifynam*e after it is processed, but you will need some way for your LISTENing thread to know what record was completed, perhaps with a log table of some kind. Also worth noting is that libpq supports asynchronous mode (which is supported, at least, by DBD::Pg, presumably other Pg drivers too).