62 lines
1.9 KiB
PL/PgSQL
62 lines
1.9 KiB
PL/PgSQL
-- CL6: auto-sync worker scheduling fields + per-instance lock helpers
|
|
|
|
alter table public.plesk_instances
|
|
add column if not exists auto_sync_enabled boolean not null default false,
|
|
add column if not exists auto_sync_frequency_minutes integer not null default 60,
|
|
add column if not exists last_auto_sync_at timestamptz,
|
|
add column if not exists next_auto_sync_at timestamptz,
|
|
add column if not exists auto_sync_lock_until timestamptz,
|
|
add column if not exists auto_sync_lock_token text;
|
|
|
|
alter table public.plesk_instances
|
|
drop constraint if exists plesk_instances_auto_sync_frequency_minutes_check;
|
|
|
|
alter table public.plesk_instances
|
|
add constraint plesk_instances_auto_sync_frequency_minutes_check
|
|
check (auto_sync_frequency_minutes between 5 and 10080);
|
|
|
|
create index if not exists idx_plesk_instances_auto_sync_due
|
|
on public.plesk_instances (auto_sync_enabled, next_auto_sync_at);
|
|
|
|
create or replace function public.acquire_instance_auto_sync_lock(
|
|
p_instance_id uuid,
|
|
p_lock_token text,
|
|
p_ttl_seconds integer default 900
|
|
)
|
|
returns boolean
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_acquired boolean := false;
|
|
begin
|
|
update public.plesk_instances
|
|
set
|
|
auto_sync_lock_until = now() + make_interval(secs => p_ttl_seconds),
|
|
auto_sync_lock_token = p_lock_token
|
|
where id = p_instance_id
|
|
and auto_sync_enabled = true
|
|
and (next_auto_sync_at is null or next_auto_sync_at <= now())
|
|
and (auto_sync_lock_until is null or auto_sync_lock_until < now());
|
|
|
|
get diagnostics v_acquired = row_count;
|
|
return v_acquired;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.release_instance_auto_sync_lock(
|
|
p_instance_id uuid,
|
|
p_lock_token text
|
|
)
|
|
returns void
|
|
language sql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
update public.plesk_instances
|
|
set auto_sync_lock_until = null,
|
|
auto_sync_lock_token = null
|
|
where id = p_instance_id
|
|
and auto_sync_lock_token = p_lock_token;
|
|
$$; |