On 17/07/19, Luca Ferrari (fluca1978@xxxxxxxxx) wrote: > On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika <Dirk.Mika@xxxxxxxxxxxxx> wrote: > > That is basically still my main question. How do I start a > > background job (e.g. a function) which waits by polling or LISTEN / > > NOTIFY for records in a table to be processed. > > You will have a trigger that, once new tuples are created (or older > update and so on) issues a NOTIFY. > Somewhere (within PostgreSQL or outside it) there will be a process > that issued a LISTEN and is locked until a notify comes in. Then it > does process whatever you need to do. We make extensive use of postgresql 'contacting' an external process, but our use case involves many databases in a cluster rather than many schemas. Also we don't have to deal with cancelling the external process. We chose this architecture to avoid many postgresql connections for LISTEN/NOTIFY. We use a pgmemcache interface trigger to update memcached with a notification of the database holding items to be flushed. A python looping process running under supervisord checks memcache for items to be dealt with, and does so in a serial manner. For the use case mentioned a per-schema process may be required or a sub-process/thread created, which could check perhaps for memcache to signal cancellation of processing for the schema. I guess one might then have thread locking/cancellation issues to resolve. Rory
