From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Devendra Yadav <devendra.857@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: ALTER TABLE blocked
Date: Tue, 30 Mar 2021 13:09:36 +0200
Message-ID: <c5ac710e20a50d1f1ec1f93e19e383295e7ebcb8.camel@cybertec.at> (raw)
In-Reply-To: <CAE+tJY9xYh4EK==rGe9u9iD+z9kgDQhiQUhb4HRYDK6B5Y6TaQ@mail.gmail.com>
References: <CAE+tJY9xYh4EK==rGe9u9iD+z9kgDQhiQUhb4HRYDK6B5Y6TaQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, devendra.857@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: ALTER TABLE blocked
In-Reply-To: <c5ac710e20a50d1f1ec1f93e19e383295e7ebcb8.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox