Message-ID: <c5ac710e20a50d1f1ec1f93e19e383295e7ebcb8.camel@cybertec.at>
Subject: Re: ALTER TABLE blocked
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Devendra Yadav <devendra.857@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Tue, 30 Mar 2021 13:09:36 +0200
In-Reply-To: 
 <CAE+tJY9xYh4EK==rGe9u9iD+z9kgDQhiQUhb4HRYDK6B5Y6TaQ@mail.gmail.com>
References: 
	<CAE+tJY9xYh4EK==rGe9u9iD+z9kgDQhiQUhb4HRYDK6B5Y6TaQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c5ac710e20a50d1f1ec1f93e19e383295e7ebcb8.camel%40cybertec.at>
Precedence: bulk

On Tue, 2021-03-30 at 12:36 +0530, Devendra Yadav wrote:
> Hope everything is well with you. 

Thanks, yes.  I hope you are fine too.

> We are running on PostgreSQL v 12.1 , Redhat 7.8.
> We are running a query  - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION;
> 
> This query is blocked by pid 0. It's not a postgres PID and won't allow to terminate from postgres. 
> What would  be the way forward to troubleshoot this?

That is probably a stale prepared transaction.

Look into "pg_prepared_xacts" for any prepared transactions and use

  ROLLBACK PREPARED 'name';

to remove stale entries.

When using prepared transactions, you must use a transaction manager
that does these things for you.  Also, you should monitor prepared
transactions.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com