Обсуждение: Alter command getting stuck on a table

Hello,

I am trying to add a column to a table but it's not working,

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 

On Tue, 2020-01-07 at 14:58 +0530, Shrikant Bhende wrote:
> I am trying to add a column to a table but it's not working, I have also tried
> reindex and drop on the same table but those commands are also getting stuck.

There is a concurrent session which has an open transaction which
holds some lock on the table in question.

Look for old "active" or "idle in transaction" sessions in "pg_stat_activity"
and see that they get closed.  Then the ALTER TABLE will work fine.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2020-01-07 at 14:58 +0530, Shrikant Bhende wrote:
>> I am trying to add a column to a table but it's not working, I have also tried
>> reindex and drop on the same table but those commands are also getting stuck.

> There is a concurrent session which has an open transaction which
> holds some lock on the table in question.

Yes, that looks like the cause.  The pg_locks view could help you
find the culprit session.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>
>> On Tue, 2020-01-07 at 14:58 +0530, Shrikant Bhende wrote:
>>> I am trying to add a column to a table but it's not working, I have also tried
>>> reindex and drop on the same table but those commands are also getting stuck.
>
>> There is a concurrent session which has an open transaction which
>> holds some lock on the table in question.
>
> Yes, that looks like the cause.  The pg_locks view could help you
> find the culprit session.

Yeah, and IMO the pg_blocking_pids($blocked-guys-pid) function is pretty
snazzy too :-)


>             regards, tom lane
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net