pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAlter command getting stuck on a table
7+ messages / 5 participants
[nested] [flat]
* Alter command getting stuck on a table
@ 2020-01-07 09:28 Shrikant Bhende <shrikantpostgresql@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Shrikant Bhende @ 2020-01-07 09:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-07 09:34 Ron <ronljohnsonjr@gmail.com>
parent: Shrikant Bhende <shrikantpostgresql@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ron @ 2020-01-07 09:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-07 09:36 Shrikant Bhende <shrikantpostgresql@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Shrikant Bhende @ 2020-01-07 09:36 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-07 09:57 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Shrikant Bhende <shrikantpostgresql@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2020-01-07 09:57 UTC (permalink / raw)
To: Shrikant Bhende <shrikantpostgresql@gmail.com>; pgsql-admin@lists.postgresql.org; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-07 12:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-01-07 12:44 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Shrikant Bhende <shrikantpostgresql@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-07 19:04 Jerry Sievers <gsievers19@comcast.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Jerry Sievers @ 2020-01-07 19:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Shrikant Bhende <shrikantpostgresql@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Alter command getting stuck on a table
@ 2020-01-08 05:23 Shrikant Bhende <shrikantpostgresql@gmail.com>
parent: Jerry Sievers <gsievers19@comcast.net>
0 siblings, 0 replies; 7+ messages in thread
From: Shrikant Bhende @ 2020-01-08 05:23 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-01-08 05:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-07 09:28 Alter command getting stuck on a table Shrikant Bhende <shrikantpostgresql@gmail.com>
2020-01-07 09:34 ` Ron <ronljohnsonjr@gmail.com>
2020-01-07 09:36 ` Shrikant Bhende <shrikantpostgresql@gmail.com>
2020-01-07 09:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-01-07 12:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-07 19:04 ` Jerry Sievers <gsievers19@comcast.net>
2020-01-08 05:23 ` Shrikant Bhende <shrikantpostgresql@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox