Re: ALTER STATEMENT getting blocked

aditya desai <admad123(at)gmail(dot)com> writes:
> We have a Postgres 11.16 DB which is continuously connected to informatica
> and data gets read from it continuously.

> When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
> on the table mentioned by process above.

> Is there any way to ALTER the table concurrently without getting blocked?
> Any parameter or option? Can someone give a specific command?

ALTER TABLE requires exclusive lock to do that, so it will queue up
behind any existing table locks --- but then new lock requests will
queue up behind its request. So this'd only happen if your existing
reading transactions don't terminate. Very long-running transactions
are unfriendly to other transactions for lots of reasons including
this one; see if you can fix your application to avoid that. Or
manually cancel the blocking transaction(s) after the ALTER begins
waiting.

regards, tom lane