Re: ALTER TABLE blocked

Thanks a ton Laurenz.

It worked

Regards,
Devendra Yadav

On Tue, 30 Mar 2021, 16:39 Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> 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
>
>