Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iolGK-0006tt-RL for pgsql-admin@arkaria.postgresql.org; Tue, 07 Jan 2020 09:34:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iolGJ-00062X-K2 for pgsql-admin@arkaria.postgresql.org; Tue, 07 Jan 2020 09:34:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iolGJ-00062Q-1c for pgsql-admin@lists.postgresql.org; Tue, 07 Jan 2020 09:34:55 +0000 Received: from mail-oi1-x232.google.com ([2607:f8b0:4864:20::232]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iolGG-0007Cd-MQ for pgsql-admin@lists.postgresql.org; Tue, 07 Jan 2020 09:34:53 +0000 Received: by mail-oi1-x232.google.com with SMTP id i1so17364208oie.8 for ; Tue, 07 Jan 2020 01:34:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=tTFnUZdhiojaFbr6d2pHRcYf8UqYiJhn+Eh4BYPbPx4=; b=PHNiwfPaZoYVOke8puK2OsUyxz5eSnvOG07fBJjDR+UEewaNU3ZK0sMUwE3Xblxe6k cz3Q1jXQrKrgS8H4/Yd+35fXOAmsl3J9n0MEYJadKqWKJ6uYRvGTREIHbE8fxodiiZoz pb9n/rsGatGwm2CJqq1XWP1oiLR/6ifbZ3I7lZz+xjVbkVOwwAs+GevhUuvzwbxTZTLh Oi/ITvLZEi1PGFTWu+zlNnJaLRUSslLHbPbb2mu9tFPEuSoNbry6o6L2hFmhX6HkvgoT V9TNBNnVQTo08l/LsYNrsN354no2JXrcjyAMxgk2DvouycPj78FUbJFpo2aBYZKERU4L +/tQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=tTFnUZdhiojaFbr6d2pHRcYf8UqYiJhn+Eh4BYPbPx4=; b=ilWm3JNo6zx47N/PRyu+qNjglL7jfmirWcrTNDg1utmp00omZzcJwnr+9BZpYONsz1 1kEgoAgM2tKcLcMY59WsQ0183EC+eiGKOX1sB/A96Bha5UV5wj8e/8wUGorP0rvg8flD qYKJRa6bUf/drVQq/OlbfR+Jg59f/J9Y27TjyRajQvvAWhkT7CT6/YTA4C8LyHnXh+T5 qBNjGh8XcgGuMOxERgKKFMPPcIU9/iArXThnqxTx0jcRPoPkfbp6YW31iN62DTLcH7qj AXmXHr/JQFWeILPFaCpo892JrlLJUkeCETRRNoULKYby7tD3o6ddXmqaZtU7fNmEPcnn sB/w== X-Gm-Message-State: APjAAAVs7vV93g0+V4TkrbJySKpteYjx+kYet+pAbbPtUlTOB0LvUrhI pMQdHbiiBkpsdtA3elbrIPoadQR+vF0= X-Google-Smtp-Source: APXvYqz4zO3TsSR9utjYdjg8BabxYHQGxe6r1FvgOaKrmqoolsA7fxo+7zYGs+UlNFvTVqbDRIR+rg== X-Received: by 2002:aca:e106:: with SMTP id y6mr6957616oig.131.1578389691806; Tue, 07 Jan 2020 01:34:51 -0800 (PST) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id e10sm24990189otj.59.2020.01.07.01.34.51 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 07 Jan 2020 01:34:51 -0800 (PST) Subject: Re: Alter command getting stuck on a table To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <8ce3a6b4-050c-5759-a86b-ef23bf46a545@gmail.com> Date: Tue, 7 Jan 2020 03:34:50 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------704F35C3158F9FA2384D44BC" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 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--