Re: Alter command getting stuck on a table
Shrikant Bhende <[email protected]> Tue, 7 Jan 2020 15:06:07 +0530
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <CAMTQpJBCrUZr8YX5SFqJ=M1cKHpjk5UcdwXAvinfbV7gU5is8A@mail.gmail.com> |
--00000000000005d91f059b89871d Content-Type: text/plain; charset="UTF-8" Hello Ron, There was no error, even though nothing else was running on the table the command was stuck for a long time, PostgreSQL version is , PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit Thanks On Tue, Jan 7, 2020 at 3:05 PM Ron <[email protected]> wrote: > On 1/7/20 3:28 AM, Shrikant Bhende wrote: > > Hello, > > I am trying to add a column to a table but it's not working, > > > What version of postgres? > What was the error message? > > > I have also tried reindex and drop on the same table but those commands > are also getting stuck. > > Below is the table structure and lock details > > > Table > "csp_details.gcp_compute_pricing" > > Column | Type > | Collation | Nullable | Default > > > > ---------------------------------------------------------+-----------------------------+-----------+----------+------------------------------------------------------------- > > id | bigint > | | not null | > nextval('csp_details.gcp_compute_pricing_id_seq'::regclass) > > instance_type | character > varying(255) | | | > > hourly_rate_full_lowest_price_with_full_sustained_usage | real > | | | > > hourly_rate_typical_price | real > | | | > > hourly_rate_full_price_without_sustained_usage | real > | | | > > hourly_rate_preemptible_price_per_hour | real > | | | > > region | character > varying(255) | | | > > platform | character > varying(255) | | | > > last_updated_timestamp | timestamp > without time zone | | | now() > > current_generation | boolean > | | | false > > Indexes: > > "gcp_compute_pricing_pkey" PRIMARY KEY, btree (id) > > > > > > PG_stat_activity O/P > > > -[ RECORD 1 > ]----+--------------------------------------------------------------------------------------------- > > datid | 24823 > > datname | vmware > > pid | 6672 > > usesysid | 10 > > usename | postgres > > application_name | psql > > client_addr | > > client_hostname | > > client_port | -1 > > backend_start | 2020-01-07 08:31:39.26237+00 > > xact_start | 2020-01-07 08:36:21.025266+00 > > query_start | 2020-01-07 08:36:21.025266+00 > > state_change | 2020-01-07 08:36:21.025273+00 > > wait_event_type | Lock > > wait_event | relation > > state | active > > backend_xid | 388218816 > > backend_xmin | 388175669 > > query | alter table csp_details.gcp_compute_pricing add > one_yr_commitment_price_hourly_rate real; > > backend_type | client backend > > -[ RECORD 2 > ]----+----------------------------------------------------------------------------------------- > > > On this table normal vacuum is working but when I tried for VACUUM FULL it > was not working, I have tried to take pg_dump in case there's any > corruption, pg_dump is working fine. > > > Thanks > > > -- > Angular momentum makes the world go 'round. > --00000000000005d91f059b89871d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Hello Ron,<div><br></div><div>There was no error, even tho= ugh nothing else was running on the table the command was stuck for a long = time,=C2=A0</div><div>PostgreSQL version is ,=C2=A0</div><div>PostgreSQL 10= .6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (= Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit<br></div><div><br></div><div>Thanks</= div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_at= tr">On Tue, Jan 7, 2020 at 3:05 PM Ron <<a href=3D"mailto:ronljohnsonjr@= gmail.com">[email protected]</a>> wrote:<br></div><blockquote clas= s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r= gb(204,204,204);padding-left:1ex"> =20 =20 =20 <div bgcolor=3D"#FFFFFF"> On 1/7/20 3:28 AM, Shrikant Bhende wrote:<br> <blockquote type=3D"cite"> =20 <div dir=3D"ltr">Hello, <div><br> </div> <div>I am trying to add a column to a table but it's not working, </div> </div> </blockquote> <br> What version of postgres?<br> What was the error message?<br> <br> <br> <blockquote type=3D"cite"> <div dir=3D"ltr"> <div>I have also tried reindex and drop on the same table but those commands are also getting stuck.</div> <div><br> </div> <div>Below is the table structure and lock details=C2=A0</div> <div> <p><br> </p> <p><span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>Table "csp_details.gcp_compute_pricing&quo= t;</p> <p><span>=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 </span>Column<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>Type <span>=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| Collation | Nullable | <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>Default<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0</span></p> <p>---------------------------------------------------------+----= -------------------------+-----------+----------+--------------------------= -----------------------------------</p> <p><span>=C2=A0</span>id<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| bigint<span>= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| not null | nextval('csp_details.gcp_compute_pricing_id_seq'::regcl= ass)</p> <p><span>=C2=A0</span>instance_type <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 </span>| character varying(255)<span>=C2=A0 =C2=A0= =C2=A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>hourly_rate_full_lowest_price_with_full_sus= tained_usage | real<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| <span>=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 </span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>hourly_rate_typical_price <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| real<span>= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>hourly_rate_full_price_without_sustained_us= age<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| real<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| <span>=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= /span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>hourly_rate_preemptible_price_per_hour<span= >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| real<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| <span>=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= /span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>region<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| character varying(255)<span>=C2=A0 =C2=A0 =C2=A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>platform<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| character varying(255)<span>=C2=A0= =C2=A0 =C2=A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<s= pan>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0</span></p> <p><span>=C2=A0</span>last_updated_timestamp<span>=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| timestamp = without time zone | <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| now()</p> <p><span>=C2=A0</span>current_generation<span>=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </spa= n>| boolean <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 </span>| <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| false</p> <p>Indexes:</p> <p><span>=C2=A0 =C2=A0 </span>"gcp_compute_pricing_pkey"= ; PRIMARY KEY, btree (id)</p> <p><br> </p> <p><br> </p> <p><br> </p> <p><br> </p> <p>PG_stat_activity O/P</p> <p style=3D"margin:0px;font:12px "Helvetica Neue";min-h= eight:14px"><br> </p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">-[ R= ECORD 1 ]----+---------------------------------------------------------------------= ------------------------</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">dati= d<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 24823</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">datn= ame<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| vmware</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">pid<= span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 6672</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">uses= ysid <span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 10</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">usen= ame<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| postgres</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">appl= ication_name | psql</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">clie= nt_addr<span>=C2=A0 =C2=A0 =C2=A0 </span>|<span>=C2=A0</span></p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">clie= nt_hostname<span>=C2=A0 </span>|<span>=C2=A0</span></p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">clie= nt_port<span>=C2=A0 =C2=A0 =C2=A0 </span>| -1</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">back= end_start<span>=C2=A0 =C2=A0 </span>| 2020-01-07 08:31:39.26237+00</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">xact= _start <span>=C2=A0 =C2=A0 =C2=A0 </span>| 2020-01-07 08:36:21.025266+00</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">quer= y_start<span>=C2=A0 =C2=A0 =C2=A0 </span>| 2020-01-07 08:36:21.025266+00</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">stat= e_change <span>=C2=A0 =C2=A0 </span>| 2020-01-07 08:36:21.025273+00</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">wait= _event_type<span>=C2=A0 </span>| Lock</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">wait= _event <span>=C2=A0 =C2=A0 =C2=A0 </span>| relation</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">stat= e<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| active</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">back= end_xid<span>=C2=A0 =C2=A0 =C2=A0 </span>| 388218816</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">back= end_xmin <span>=C2=A0 =C2=A0 </span>| 388175669</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">quer= y<span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| alter table csp_details.gcp_compute_pricing add one_yr_commitment_price_hourly_rate real;</p> <p style=3D"margin:0px;font:12px "Helvetica Neue"">back= end_type <span>=C2=A0 =C2=A0 </span>| client backend</p> <p> </p> <p>-[ RECORD 2 ]----+---------------------------------------------------------------------= --------------------</p> <p><br> </p> <p>On this table normal vacuum is working but when I tried for VACUUM FULL it was not working, I have tried to take pg_dump in case there's any corruption, pg_dump is working fine.</p> <p><br> </p> <p>Thanks=C2=A0</p> </div> </div> </blockquote> <br> <div>-- <br> Angular momentum makes the world go 'round.</div> </div> </blockquote></div> --00000000000005d91f059b89871d--