Re: Alter command getting stuck on a table

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 <ronljohnsonjr(at)gmail(dot)com> 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.
>