Re: Alter command getting stuck on a table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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.

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux