Re: Update blocking a select count(*)?

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

1736 postgres 6/39 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 rmv 33081 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
2096 postgres 8/151 ExclusiveLock Yes 2012-06-15
10:25:08.329-04 vacuum (analyze, verbose, full)
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 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 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 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 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 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;

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 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 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;

On Fri, Jun 15, 2012 at 2:46 PM, Benedict Holland <
benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:

> Yes I actually seem to have two of them for the single update. The update
> I am running will set the value of a single column in the table without a
> where clause. I actually have two AccessShareLock's, two ExclusiveLock's,
> and two RowExclusiveLock's. It sort of seems like overkill for what should
> be a copy the column to make the updates, make updates, and publish updates
> set of operations. On my select statement I have an ExclusiveLock and an
> AccessShareLock. I read the documentation on locking but this seems very
> different from what I should expect.
>
> I am running an update statement without a where clause (so a full table
> update). This is not an alter table statement (though I am running that too
> and it is being blocked). I am looking in the SeverStatus section of
> pgadmin3. There are three queries which are in green (not blocked), two
> statements which are in red (an alter as expected and a select count(*)
> which are blocked by an update process).
>
> I can not tell you how many documents I have read for locks, statements
> which generate locks etc. I accept that this will run slowly, what pgadmin3
> is displaying to me is the described behavior.
>
> Thanks,
> ~Ben
>
>
>
>
> On Fri, Jun 15, 2012 at 2:43 PM, Kevin Grittner <
> Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
>> > Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:
>> >> Do I seem to have this right and is there anything I can do?
>> >
>> > There are a couple of maintenance operations that could block a
>> > select. Do you see any AccessExclusive locks within pg_locks?
>> > That's the only type of lock that will block a select statement's
>> > AccessShare lock.
>>
>> To check for that, see the queries on these Wiki pages:
>>
>> http://wiki.postgresql.org/wiki/Lock_Monitoring
>> http://wiki.postgresql.org/wiki/Lock_dependency_information
>>
>> -Kevin
>>
>
>