Re: Update blocking a select count(*)?

Benedict Holland <benedict.m.holland@xxxxxxxxx> · Fri, 15 Jun 2012 15:04:32 -0400

Sure. The last column are the series of commands to produce the outputs. This is coming from pgadmin3. I should have mentioned before that this is running windows but that shouldn't matter for this particular sense I hope. 

The first column is the PID, the last column is the command running. The dates are the start time of the operations. The YES/NO is the running state of the process. In the activity section the 2nd to last column is the process blocking the executing process. 

Thanks,
~Ben

On Fri, Jun 15, 2012 at 2:54 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:

Benedict Holland <benedict.m.holland@xxxxxxxxx> wrote:

> Sorry about the raw text but this is what I am seeing:

>

> [wrapped text without column headers]

Could you try that as an attachment, to avoid wrapping?  Also, the

column headers, and/or the query used to generate those results

would be helpful.

-Kevin

PG admin 3:

activity
1588		rmv	postgres	::1:49275	2012-06-15 12:12:01.09-04	2012-06-15 13:47:35.494-04	2012-06-15 13:47:35.494-04	2940	ALTER TABLE vins DROP COLUMN owner_type;

1736		rmv	postgres	::1:49231	2012-06-15 09:50:12.891-04	2012-06-15 13:36:22.997-04	2012-06-15 13:36:22.997-04		insert into inspections
select * from inspections_1	
2096		rmv	postgres	::1:49234	2012-06-15 09:57:55.797-04	2012-06-15 10:25:08.329-04	2012-06-15 10:25:08.329-04		vacuum (analyze, verbose, full)	
2316		postgres	postgres	::1:49301	2012-06-15 13:44:52.795-04				<IDLE>	
2620		postgres	postgres	::1:49227	2012-06-15 09:44:12.139-04				<IDLE>	
2716		rmv	postgres	::1:49228	2012-06-15 09:44:15.684-04				<IDLE>	
2844		rmv	postgres	::1:49303	2012-06-15 13:50:29.021-04	2012-06-15 13:50:46.417-04	2012-06-15 13:50:46.417-04	2940	select count(*) from vins	
2940		rmv	postgres	::1:49273	2012-06-15 12:01:34.756-04	2012-06-15 13:34:53.55-04	2012-06-15 13:34:53.55-04		
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

3028		postgres	postgres	::1:49232	2012-06-15 09:52:11.764-04				<IDLE>	

locks:
1588	rmv	33074	postgres		11/71	AccessExclusiveLock	No	2012-06-15 13:47:35.494-04	ALTER TABLE vins DROP COLUMN owner_type;

1588			postgres	11/71	11/71	ExclusiveLock	Yes	2012-06-15 13:47:35.494-04	ALTER TABLE vins DROP COLUMN owner_type;

1736	rmv	33350	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	33081	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	33352	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	33084	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	33351	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	33349	postgres		6/39	RowExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	49899	postgres		6/39	AccessShareLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736			postgres		6/39	ExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736	rmv	49896	postgres		6/39	AccessShareLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
1736			postgres	6/39	6/39	ExclusiveLock	Yes	2012-06-15 13:36:22.997-04	insert into inspections
select * from inspections_1	
2096	rmv	33528	postgres		8/151	AccessExclusiveLock	Yes	2012-06-15 10:25:08.329-04	vacuum (analyze, verbose, full)	
2096	rmv	50267	postgres		8/151	AccessExclusiveLock	Yes	2012-06-15 10:25:08.329-04	vacuum (analyze, verbose, full)	
2096			postgres		8/151	ExclusiveLock	Yes	2012-06-15 10:25:08.329-04	vacuum (analyze, verbose, full)	
2096			postgres	8/151	8/151	ExclusiveLock	Yes	2012-06-15 10:25:08.329-04	vacuum (analyze, verbose, full)	
2844			postgres	5/27	5/27	ExclusiveLock	Yes	2012-06-15 13:50:46.417-04	select count(*) from vins	
2844	rmv	33074	postgres		5/27	AccessShareLock	No	2012-06-15 13:50:46.417-04	select count(*) from vins	
2940	rmv	33079	postgres		2/251	RowExclusiveLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940	rmv	41681	postgres		2/251	AccessShareLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940	rmv	33074	postgres		2/251	RowExclusiveLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940			postgres		2/251	ExclusiveLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940			postgres	2/251	2/251	ExclusiveLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940	rmv	50265	postgres		2/251	RowExclusiveLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

2940	rmv	41684	postgres		2/251	AccessShareLock	Yes	2012-06-15 13:34:53.55-04	
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance