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.m.holl...@gmail.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.gritt...@wicourts.gov> wrote: > >> Peter Geoghegan <pe...@2ndquadrant.com> wrote: >> > Benedict Holland <benedict.m.holl...@gmail.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 >> > >
