From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
Cc: | Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to run a task continuously in the background |
Date: | 2019-07-17 11:34:41 |
Message-ID: | 20190717113441.GB15220@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 17/07/19, Luca Ferrari (fluca1978(at)gmail(dot)com) wrote:
> On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Volkan Unsal | 2019-07-17 14:30:40 | Removing a key from jsonb is sloooow |
Previous Message | James (王旭) | 2019-07-17 10:59:35 | Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value? |