Re: How to run a task continuously in the background - Mailing list pgsql-general

So, as far as I understand, you want asynchronously processing data

with a process that can be started manually and/or periodically.

The process should be started manually and then run until it is stopped by a stop_job() function call. In Oracle there is a package which contains the functions engine.start_job() and engine.stop_job(). :-)

I only mentioned “periodic” because the current Oracle implementation polls to see if there is something to process and goes to sleep for a second when there is no data to process.

I'm probably unable to see what is the goal, but I would go for a

combined solution:

1) a trigger that notifies an external process

<https://www.postgresql.org/docs/current/sql-notify.html>

2) the process runs when notified (by the trigger) or when started

manually or when started by pg_cron (one per minute).

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.

Of course the process is "internal", so something like a stored

procedure (at least as entry point).

The problem with such solution is about race conditions (what if you

manually start something that is already running?), but I guess you

had this problem on the oracle side too.

In the Oracle world, there is a maximum of one job that takes care of processing.

Dirk