Re: How to run a task continuously in the background

On 19/07/19, Peter J. Holzer (hjp-pgsql(at)hjp(dot)at) wrote:
> On 2019-07-17 12:34:41 +0100, Rory Campbell-Lange wrote:
> > 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.
>
> Memcached is a cache. It will delete old items if storage is full (or if
> they expire). Is this not a problem in your case or did you make sure
> that this cannot happen?

We have a few hundred databases in a cluster, and the notifications are
simply the database name and the value "1" when the database queue
requires processing. With only the database name as key memory use
memcached is bounded. There is a potential race condition between the
queue processor processing the queue before setting the value to "0" and
more queue items landing, but that is not an issue for our use case.

Rory