From: Tom Lane <tgl@sss.pgh.pa.us>
To: aditya desai <admad123@gmail.com>
cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: ALTER STATEMENT getting blocked
In-reply-to: 
 <CAN0SRDFCTuHU6ENsJmhxRtzLF6N4hXPONQsOtoF8ZiwNuSx+bg@mail.gmail.com>
References: 
 <CAN0SRDFCTuHU6ENsJmhxRtzLF6N4hXPONQsOtoF8ZiwNuSx+bg@mail.gmail.com>
Comments: In-reply-to aditya desai <admad123@gmail.com>
	message dated "Thu, 19 Jan 2023 23:00:41 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4178686.1674150355.1@sss.pgh.pa.us>
Date: Thu, 19 Jan 2023 12:45:55 -0500
Message-ID: <4178687.1674150355@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4178687.1674150355%40sss.pgh.pa.us>
Precedence: bulk

aditya desai <admad123@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