Thanks All. Let me check this and get back to you. On Fri, Jan 20, 2023 at 2:36 AM MichaelDBA <michael...@sqlexec.com> wrote:
> Do something like this to get it without being behind other > transactions...You either get in and get your work done or try again > > DO language plpgsql $$ > BEGIN > FOR get_lock IN 1 .. 100 LOOP > BEGIN > ALTER TABLE mytable <do something>; > EXIT; > END; > END LOOP; > END; > $$; > > > > Tom Lane wrote on 1/19/2023 12:45 PM: > > aditya desai <admad...@gmail.com> <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 > > > > > > Regards, > > Michael Vitale > > michael...@sqlexec.com <michaelvit...@sqlexec.com> > > 703-600-9343 > > > >
