50 lines
1.1 KiB
PL/PgSQL
50 lines
1.1 KiB
PL/PgSQL
-- CL3.5: background auto-sync lock primitives
|
|
|
|
create table if not exists public.job_locks (
|
|
job_name text primary key,
|
|
locked_at timestamptz not null default now(),
|
|
locked_by text,
|
|
expires_at timestamptz not null
|
|
);
|
|
|
|
create or replace function public.acquire_job_lock(
|
|
p_job_name text,
|
|
p_locked_by text,
|
|
p_ttl_seconds integer default 240
|
|
)
|
|
returns boolean
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_acquired boolean := false;
|
|
begin
|
|
insert into public.job_locks (job_name, locked_by, locked_at, expires_at)
|
|
values (
|
|
p_job_name,
|
|
p_locked_by,
|
|
now(),
|
|
now() + make_interval(secs => p_ttl_seconds)
|
|
)
|
|
on conflict (job_name)
|
|
do update
|
|
set locked_by = excluded.locked_by,
|
|
locked_at = excluded.locked_at,
|
|
expires_at = excluded.expires_at
|
|
where public.job_locks.expires_at < now();
|
|
|
|
get diagnostics v_acquired = row_count;
|
|
return v_acquired;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.release_job_lock(p_job_name text)
|
|
returns void
|
|
language sql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
delete from public.job_locks where job_name = p_job_name;
|
|
$$;
|