how to store an array of values into a variable?

Rule of thumb: if you’re looping in SQL there’s probably a better way.

You want to set state = 'down' until you have a certain number of idle servers.

We can do this in a single statement. Use a Common Table Expression to query your idle servers and feed that to an update from. If you do this a lot you can turn the CTE into a view.

But we need to limit how many we take down based on how many idle servers there are. We can do that with a limit. But update from doesn’t take a limit, so we need a second CTE to limit the results.

Here I’ve hard coded company_id 1 and p_num_min_idle_servers 2.

with idle_servers as (
  select id
  from server
  where server.company_id=1
    and connection_count=0
    and state = 'up'
),
idle_servers_to_take_down as (
  select id
  from idle_servers
  -- limit doesn't work in update from
  limit (select count(*) - 2 from idle_servers)
)
update server
set state = 'down'
from idle_servers_to_take_down
where server.id = idle_servers_to_take_down.id

This has the advantage of being done in one statement avoiding race conditions without having to lock the whole table.

Try it.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top