pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: ALTER TABLE blocked
2+ messages / 2 participants
[nested] [flat]
* Re: ALTER TABLE blocked
@ 2021-03-30 11:09 Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: Laurenz Albe @ 2021-03-30 11:09 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ALTER TABLE blocked
@ 2021-03-30 11:26 Devendra Yadav <devendra.857@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Devendra Yadav @ 2021-03-30 11:26 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Thanks a ton Laurenz.
It worked
Regards,
Devendra Yadav
On Tue, 30 Mar 2021, 16:39 Laurenz Albe, <laurenz.albe@cybertec.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
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-30 11:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-30 11:09 Re: ALTER TABLE blocked Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-30 11:26 ` Devendra Yadav <devendra.857@gmail.com>
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