title: How to force COMMIT inside function so other sessions can see updated row?
body: <p>In a Postgres 12 database, I have multiple queries (<code>SELECT</code>, <code>UPDATE</code>, ...) in a function that all together take about 20 minutes to complete.
  I have a check at the top that does an <code>UPDATE</code> if <code>status</code> is not running:</p>
  <pre><code>create or replace function aaa.fnc_work() returns varchar as 
  $body$
      begin
          if (select count(*) from aaa.monitor where id='invoicing' and status='running')=0 then
             return 'running';
          else
             update aaa.monitor set status='running' where id='invoicing';
          end if;
          --- rest of code ---
          --finally
          update aaa.monitor set status='idle' where id='invoicing';
          return '';
      exception when others then
           return SQLERRM::varchar;
      end
  $body$
  language plpgsql;
  </code></pre>
  <p>The idea is to prevent other users from executing the <code>--- rest of code ---</code> until <code>status</code> is idle.</p>
  <p>However, it seems the updated status is not seen by others (calling the same function) who also go ahead and start executing <code>--- rest of code ---</code>. How do I force a commit after:</p>
  <blockquote>
  <p>update aaa.monitor set status='running' <code>where id='invoicing'</code>;</p>
  </blockquote>
  <p>So that all other user sessions can see the updated <code>status</code> and exit accordingly.</p>
  <p>Do I need a transaction?</p>