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.