Thread: Alter command getting stuck on a table

Alter command getting stuck on a table

From
Shrikant Bhende
Date:
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 

Re: Alter command getting stuck on a table

From
Ron
Date:
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.

Re: Alter command getting stuck on a table

From
Shrikant Bhende
Date:
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@gmail.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.

Re: Alter command getting stuck on a table

From
Laurenz Albe
Date:
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




Re: Alter command getting stuck on a table

From
Tom Lane
Date:
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



Re: Alter command getting stuck on a table

From
Jerry Sievers
Date:
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



Re: Alter command getting stuck on a table

From
Shrikant Bhende
Date:
Hello,

Thank you all, 
that helped me to get rid of the issues, using pg_locks and pg_stat_activity I was able to find out concurrent session which was not allowing to hold exclusive lock. 

Thanks 

On Wed, Jan 8, 2020 at 12:34 AM Jerry Sievers <gsievers19@comcast.net> wrote:
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