0048.yml

version: 48
description: Add an index on state in the workers table
migrationScript: |-
  begin
    create index workers_state_idx on workers (state);
  end
downgradeScript: |-
  begin
    drop index workers_state_idx;
  end
methods:
  get_workers_without_provider_data:
    description: |-
      Get existing workers (without their `provider_data`) filtered by the optional arguments,
      ordered by `worker_pool_id`, `worker_group`, and  `worker_id`.
      If the pagination arguments are both NULL, all rows are returned.
      Otherwise, page_size rows are returned at offset page_offset.
    mode: read
    serviceName: worker_manager
    args: worker_pool_id_in text, worker_group_in text, worker_id_in text, state_in text, page_size_in integer, page_offset_in integer
    returns: table(worker_pool_id text, worker_group text, worker_id text, provider_id text, created timestamptz, expires timestamptz, state text, capacity integer, last_modified timestamptz, last_checked timestamptz)
    body: |-
      begin
        return query
        select
          workers.worker_pool_id,
          workers.worker_group,
          workers.worker_id,
          workers.provider_id,
          workers.created,
          workers.expires,
          workers.state,
          workers.capacity,
          workers.last_modified,
          workers.last_checked
        from workers
        where
          (workers.worker_pool_id = worker_pool_id_in or worker_pool_id_in is null) and
          (workers.worker_group = worker_group_in or worker_group_in is null) and
          (workers.worker_id = worker_id_in or worker_id_in is null) and
          (workers.state = state_in or state_in is null)
        order by worker_pool_id, worker_group, worker_id
        limit get_page_limit(page_size_in)
        offset get_page_offset(page_offset_in);
      end