The quick thing to try is set lock_timeout=1000;, then try your ALTER a bunch of times. Most likely you'll hit the timeout each time, but you might get lucky and that'll save you from having to hunt down the actual fix, which is, I'm sorry to say, not nearly as easy.
Almost certainly what is going on is that the other processes keep their transaction open ...
You don't need to use the interval syntax to begin with. You can simply subtract an integer from a date value, e.g. current_date - 1 is "yesterday".
select current_date - lag_days
If you do want to use an interval, the make_interval() function is quite useful for this:
select current_date - make_interval(days => lag_days)
The parameters in the array may be transformed into a table
to be be joined against the rest. This transformation is
done with the unnest function and the WHERE conditions can then be expressed as
This could also be done by joining with generate_subscripts($1, 1) but that leads to more complicated syntax for no apparent benefit.
Your session is in idle state, not the query.
Sessions that are idle are not a problem. It simply means the backend is waiting for the client to send a SQL query.
The number of sessions is a little bit on the high side though. You will need to have a really powerful server to cope with that, once all of them start doing something.
Sessions that are idle in ...