Search code examples
sqlpostgresqldbms-job

How to run block of code in Postgres asynchronously (like a job)?

Can we execute a block of code outside of current session? In other words, delegate the execution to another working thread, something like a dbms_job.submit in Oracle.

I found only solutions involving external tools like Cron, but do not see any options to do it using PostgreSQL DBMS itself.

Example of simple long running block that I would like to run without blocking my current session:

DO
$do$
begin
   FOR i IN 1..1000000
   loop
      // some long running inserts
      insert into my_table(x) values (i);
      commit;
   end loop;
end $do$;
Solution

  • You can only have a single statement at a time running in a single PostgreSQL database session. The solution is to start a second session: then one session can execute the script, and you can concurrently do other work in the other session.