0

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$;
diziaq
  • 6,881
  • 16
  • 54
  • 96
  • Have you tried pgagent? https://www.pgadmin.org/docs/pgadmin4/5.0/pgagent_jobs.html – richyen Jul 01 '21 at 15:30
  • @richyen I am not sure how to access it. In pgAdmin "Help/Quick search" nothing is found for "pgAgent". In Application Stack Builder I also don't see an option to install "pgAgent". The docs don't give enough information about the installation. – diziaq Jul 01 '21 at 15:43
  • Are you using enterprisedb postgres, or regular, community postgres? – richyen Jul 01 '21 at 16:05

2 Answers2

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    That's totally correct. But I would like to start a long-running statement, close my session, and later return back and check the results. And as a bonus it will protect from connection loss in a middle of the work in case of bad network. – diziaq Jul 01 '21 at 17:36
  • That's a reasonable requirement. Start your script with `nohup psql -f script.sql &`, or whatever is the equivalent on your operating system. – Laurenz Albe Jul 01 '21 at 18:57
1

You should have a look to this new PostgreSQL extension pg_dbms_job, it implements all features provided by Oracle DBMS_JOB with the same procedures like dbms_job.submit().

Gilles Darold
  • 11
  • 2