Re: ALTER STATEMENT getting blocked

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks All. Let me check this and get back to you.

On Fri, Jan 20, 2023 at 2:36 AM MichaelDBA <MichaelDBA@xxxxxxxxxxx> 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 <admad123@xxxxxxxxx> 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

Michaeldba@xxxxxxxxxxx

703-600-9343

 




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux