Re: Update blocking a select count(*)?

[Benedict Holland <benedict.m.holland@xxxxxxxxx>]

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.Grittner@xxxxxxxxxxxx> wrote:

Peter Geoghegan <peter@xxxxxxxxxxxxxxx> wrote:
> Benedict Holland <benedict.m.holland@xxxxxxxxx> 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