create or replace
function public.packages_catchup() returns integer as $$
declare
    vResult integer; vCurrentDate date;
    vAdvisoryLockID integer := 1984;
    vMinDate date := '2017-07-18'; /* select min(created_at) from packages; */
begin
    /* In order to hold lock as less as possible we
       use advisory locks instead of common locks, because
       command locks are always held until end of transaction */
    perform pg_advisory_lock(vAdvisoryLockID);

    select coalesce(max(day) + interval '1 day', vMinDate)
      from public.package_catchup_log
      into vCurrentDate;

    insert into public.package_catchup_log (day)
    values (vCurrentDate);

    perform pg_advisory_unlock(vAdvisoryLockID);

    insert into public.packages_new
    select * from public.packages p
    where created_at >= vCurrentDate and
          created_at < vCurrentDate + interval '1 day' and
          not exists (
            select *
              from public.packages_new pn
             where pn.id = p.id and
                   created_at >= vCurrentDate and
                   created_at < vCurrentDate + interval '1 day'
          );

    get diagnostics vResult := ROW_COUNT;

    raise warning 'public.packages_catchup(): % rows have been moved', vResult;

    update public.package_catchup_log
       set rows_count = vResult,
           dt_end = clock_timestamp()
     where day = vCurrentDate;

    return vResult;
end;
$$ language plpgsql;

(42498) 2018-12-02 13:45:07 UTC [15447] STATEMENT:  /*NO LOAD BALANCE*/ SELECT now(), public.packages_catchup();
(42496) 2018-12-02 13:45:07 UTC [15446] LOG:  duration: 109388.232 ms  statement: /*NO LOAD BALANCE*/ SELECT now(), public.packages_catchup();
(48792) 2018-12-02 13:45:33 UTC [16494] LOG:  process 16494 still waiting for ShareLock on transaction 996384294 after 1000.083 ms
(48792) 2018-12-02 13:45:33 UTC [16494] DETAIL:  Process holding the lock: 16493. Wait queue: 16494.
(48792) 2018-12-02 13:45:33 UTC [16494] CONTEXT:  while inserting index tuple (5860744,4) in relation "packages_new_pkey"
	SQL statement "insert into public.packages_new
	    select * from public.packages p
	    where created_at >= vCurrentDate and
	          created_at < vCurrentDate + interval '1 day' and
	          not exists (
	            select *
	              from public.packages_new pn
	             where pn.id = p.id and
	                   created_at >= vCurrentDate and
	                   created_at < vCurrentDate + interval '1 day'
	          )"
	PL/pgSQL function packages_catchup() line 21 at SQL statement