Re: Alter command getting stuck on a table
Ron <[email protected]> Tue, 7 Jan 2020 03:34:50 -0600
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--------------704F35C3158F9FA2384D44BC
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
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.
--------------704F35C3158F9FA2384D44BC
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
On 1/7/20 3:28 AM, Shrikant Bhende wrote:<br>
<blockquote type="cite"
cite="mid:CAMTQpJALSF2EvBqmdYCqKnXx-o2VQZ4+KRn1TsstudQukC0Ryg@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="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="cite"
cite="mid:CAMTQpJALSF2EvBqmdYCqKnXx-o2VQZ4+KRn1TsstudQukC0Ryg@mail.gmail.com">
<div dir="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 </div>
<div>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px"><br>
</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space">
</span>Table "csp_details.gcp_compute_pricing"</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space">
</span>Column<span
class="gmail-Apple-converted-space">
</span>|<span class="gmail-Apple-converted-space">
</span>Type <span
class="gmail-Apple-converted-space"> </span>|
Collation | Nullable | <span
class="gmail-Apple-converted-space">
</span>Default<span
class="gmail-Apple-converted-space">
</span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue"">---------------------------------------------------------+-----------------------------+-----------+----------+-------------------------------------------------------------</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>id<span
class="gmail-Apple-converted-space">
</span>| bigint<span
class="gmail-Apple-converted-space">
</span>| <span class="gmail-Apple-converted-space">
</span>| not null |
nextval('csp_details.gcp_compute_pricing_id_seq'::regclass)</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>instance_type
<span class="gmail-Apple-converted-space">
</span>| character varying(255)<span
class="gmail-Apple-converted-space"> </span>| <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>hourly_rate_full_lowest_price_with_full_sustained_usage
| real<span class="gmail-Apple-converted-space">
</span>| <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>hourly_rate_typical_price
<span class="gmail-Apple-converted-space">
</span>| real<span
class="gmail-Apple-converted-space">
</span>| <span class="gmail-Apple-converted-space">
</span>|<span class="gmail-Apple-converted-space">
</span>|<span class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>hourly_rate_full_price_without_sustained_usage<span
class="gmail-Apple-converted-space"> </span>|
real<span class="gmail-Apple-converted-space">
</span>| <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>hourly_rate_preemptible_price_per_hour<span
class="gmail-Apple-converted-space"> </span>|
real<span class="gmail-Apple-converted-space">
</span>| <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>region<span
class="gmail-Apple-converted-space">
</span>| character
varying(255)<span class="gmail-Apple-converted-space">
</span>| <span class="gmail-Apple-converted-space">
</span>|<span class="gmail-Apple-converted-space">
</span>|<span class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>platform<span
class="gmail-Apple-converted-space">
</span>| character varying(255)<span
class="gmail-Apple-converted-space"> </span>| <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>last_updated_timestamp<span
class="gmail-Apple-converted-space">
</span>| timestamp without time zone | <span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|
now()</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>current_generation<span
class="gmail-Apple-converted-space">
</span>| boolean <span
class="gmail-Apple-converted-space"> </span>|
<span class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span>|
false</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue"">Indexes:</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><span class="gmail-Apple-converted-space"> </span>"gcp_compute_pricing_pkey"
PRIMARY KEY, btree (id)</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px"><br>
</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px"><br>
</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px"><br>
</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px"><br>
</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px">PG_stat_activity O/P</p>
<p class="gmail-p1" style="margin:0px;font:12px
"Helvetica Neue";min-height:14px"><br>
</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">-[ RECORD 1
]----+---------------------------------------------------------------------------------------------</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">datid<span
class="gmail-Apple-converted-space"> </span>|
24823</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">datname<span
class="gmail-Apple-converted-space"> </span>|
vmware</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">pid<span
class="gmail-Apple-converted-space"> </span>|
6672</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">usesysid <span
class="gmail-Apple-converted-space"> </span>| 10</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">usename<span
class="gmail-Apple-converted-space"> </span>|
postgres</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">application_name | psql</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">client_addr<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">client_hostname<span
class="gmail-Apple-converted-space"> </span>|<span
class="gmail-Apple-converted-space"> </span></p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">client_port<span
class="gmail-Apple-converted-space"> </span>| -1</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">backend_start<span
class="gmail-Apple-converted-space"> </span>|
2020-01-07 08:31:39.26237+00</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">xact_start <span
class="gmail-Apple-converted-space"> </span>|
2020-01-07 08:36:21.025266+00</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">query_start<span
class="gmail-Apple-converted-space"> </span>|
2020-01-07 08:36:21.025266+00</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">state_change <span
class="gmail-Apple-converted-space"> </span>|
2020-01-07 08:36:21.025273+00</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">wait_event_type<span
class="gmail-Apple-converted-space"> </span>| Lock</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">wait_event <span
class="gmail-Apple-converted-space"> </span>|
relation</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">state<span
class="gmail-Apple-converted-space"> </span>|
active</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">backend_xid<span
class="gmail-Apple-converted-space"> </span>|
388218816</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">backend_xmin <span
class="gmail-Apple-converted-space"> </span>|
388175669</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">query<span
class="gmail-Apple-converted-space"> </span>|
alter table csp_details.gcp_compute_pricing add
one_yr_commitment_price_hourly_rate real;</p>
<p class="gmail-p2" style="margin:0px;font:12px
"Helvetica Neue"">backend_type <span
class="gmail-Apple-converted-space"> </span>| client
backend</p>
<p class="gmail-p1"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue";min-height:14px">
</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue"">-[ RECORD 2
]----+-----------------------------------------------------------------------------------------</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><br>
</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue"">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 class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue""><br>
</p>
<p class="gmail-p2"
style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:"Helvetica
Neue"">Thanks </p>
</div>
</div>
</blockquote>
<br>
<div class="moz-signature">-- <br>
Angular momentum makes the world go 'round.</div>
</body>
</html>
--------------704F35C3158F9FA2384D44BC--