aditya desai <admad...@gmail.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
