Re: Update blocking a select count(*)?

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
>