Re: Alter command getting stuck on a table
Shrikant Bhende <[email protected]> Wed, 8 Jan 2020 10:53:18 +0530
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <CAMTQpJB+TA-CTRyQ7rW00v-0PHMWGcfmwT_CypWSK_kcFwE=ag@mail.gmail.com> |
--000000000000e19173059b9a1c1b Content-Type: text/plain; charset="UTF-8" Hello, Thank you all, that helped me to get rid of the issues, using pg_locks and pg_stat_activity I was able to find out concurrent session which was not allowing to hold exclusive lock. Thanks On Wed, Jan 8, 2020 at 12:34 AM Jerry Sievers <[email protected]> wrote: > Tom Lane <[email protected]> writes: > > > Laurenz Albe <[email protected]> writes: > > > >> On Tue, 2020-01-07 at 14:58 +0530, Shrikant Bhende wrote: > >>> I am trying to add a column to a table but it's not working, I have > also tried > >>> reindex and drop on the same table but those commands are also getting > stuck. > > > >> There is a concurrent session which has an open transaction which > >> holds some lock on the table in question. > > > > Yes, that looks like the cause. The pg_locks view could help you > > find the culprit session. > > Yeah, and IMO the pg_blocking_pids($blocked-guys-pid) function is pretty > snazzy too :-) > > > > regards, tom lane > > > > > > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: [email protected] > --000000000000e19173059b9a1c1b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Hello,<div><br></div><div>Thank you all,=C2=A0</div><div>t= hat helped me to get rid of the issues, using pg_locks and pg_stat_activity= I was able to find out concurrent session which was not allowing to hold e= xclusive lock.=C2=A0</div><div><br></div><div>Thanks=C2=A0</div></div><br><= div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Jan= 8, 2020 at 12:34 AM Jerry Sievers <<a href=3D"mailto:gsievers19@comcast= .net">[email protected]</a>> wrote:<br></div><blockquote class=3D"g= mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204= ,204,204);padding-left:1ex">Tom Lane <<a href=3D"mailto:[email protected]= s" target=3D"_blank">[email protected]</a>> writes:<br> <br> > Laurenz Albe <<a href=3D"mailto:[email protected]" target=3D= "_blank">[email protected]</a>> writes:<br> ><br> >> On Tue, 2020-01-07 at 14:58 +0530, Shrikant Bhende wrote:<br> >>> I am trying to add a column to a table but it's not workin= g, I have also tried<br> >>> reindex and drop on the same table but those commands are also= getting stuck.<br> ><br> >> There is a concurrent session which has an open transaction which<= br> >> holds some lock on the table in question.<br> ><br> > Yes, that looks like the cause.=C2=A0 The pg_locks view could help you= <br> > find the culprit session.<br> <br> Yeah, and IMO the pg_blocking_pids($blocked-guys-pid) function is pretty<br= > snazzy too :-)<br> <br> <br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0regards, tom lane<br> ><br> ><br> ><br> <br> -- <br> Jerry Sievers<br> Postgres DBA/Development Consulting<br> e: <a href=3D"mailto:[email protected]" target=3D"_blank">pos= [email protected]</a><br> </blockquote></div> --000000000000e19173059b9a1c1b--