| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Devendra Yadav <devendra(dot)857(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | Re: ALTER TABLE blocked |
| Date: | 2021-03-30 11:09:36 |
| Message-ID: | c5ac710e20a50d1f1ec1f93e19e383295e7ebcb8.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2021-03-30 11:12:16 | Re: ALTER TABLE ADD COLUMN takes forever |
| Previous Message | Devendra Yadav | 2021-03-30 07:06:15 |