PostgreSQL: How to make updates readable from other transactions while first transaction not finished

Question

I do simple schedule service.

One table with jobs

CREATE TABLE system_jobs (
  id bigserial,
  job_time timestamp without time zone,
  job_function text,
  run_on text,
  CONSTRAINT system_jobs_pri PRIMARY KEY (id)
)

Multiple JAVA daemons select all rows where job_time < now() and execute job_function (pass their id as argument)

job_function sample

CREATE OR REPLACE FUNCTION public.sjob_test(in_id bigint)
  RETURNS text AS
$BODY$DECLARE
    utc timestamp without time zone;
BEGIN
    utc := timezone('UTC', now());

    -- This changes not avail from other transactions
    UPDATE system_jobs SET run_on='hello' WHERE id = in_id;

    PERFORM pl_delay(60); -- Delay 1 minute

    UPDATE system_jobs SET job_time = now() + interval '10 seconds', run_on = '' WHERE id = in_id;

    RETURN 'done';
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Inside function before 60 seconds delay I update run_on field and reset it after delay.

I expect run_on contains 'hello' while delay (60 sec) and will available for reading from other transactions, but it is not.

My task - prevent execute same job_function by different JAVA daemons simultaneous. I want check run_on before execute.

I read many docs and blogs about transaction levels, but I don't understand how can I use it in practice.

How can I configure my function or table or external process to allow other transaction see this changes?

Answer 1

PostgreSQL doesn't support dirty reads. See PostgreSQL documentation:

PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

But looks like there is a workaround, called autonomous transactions, than might help you. There are at least two ways to implement it. See more info here and here.

Using these autonomous transactions, you can commit the change of run_on inside your function, so other transactions will be able to read it.

Answer 2

Only one way to do this - via dblink. Something like:

PERFORM dblink('your server config', 'UPDATE ...');