Re: ALTER TABLE blocked

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