Thread: Update blocking a select count(*)?

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

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@wicourts.gov> wrote:

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

Benedict Holland <benedict.m.holland@gmail.com> 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

Benedict Holland <benedict.m.holland@gmail.com> wrote:

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

You have three VACUUM FULL commands running?  VACUUM FULL is very
aggressive maintenance, which is only needed for cases of extreme
bloat.  It does lock the table against any concurrent access, since
it is completely rewriting it.

Now, if you are running UPDATE statements which affect all rows in a
table, you will *get* extreme bloat.  You either need to do such
updates as a series of smaller updates with VACUUM commands in
between, or schedule your aggressive maintenance for a time when it
can have exclusive access to the tables with minimal impact.

Reporting the other issues without mentioning the VACUUM FULL
processes is a little bit like calling from the Titanic to mention
that the ship isn't going as fast as it should and neglecting to
mention the iceberg.  :-)

-Kevin

Yes. I needed to do a full vacuum. Again, the database is very large. I batch inserted quite a lot of data and then modified that data. The vacuum isn't blocking anything. It was blocking other tables (as expected) but continues to run and clean. My tables in general are around 10GB, each update seems to nearly double the size of it so I required a full vacuum. The blocked statements are the select count(*) and the alter table. Both are blocked on the update table command. The alter table command SHOULD be blocked and that is fine. The select count(*) should never be blocked as that is the whole point of running an MVCC operation at least to my understanding. I can even accept the use case that the select should block with an Alter Table operation if data is retrieved from the table, but a select count(*) only returns the number of rows and should be table space independent. I also don't understand why a select count(*) requires an AccessShareLock. I don't understand why a select should lock anything at all.

~Ben

On Fri, Jun 15, 2012 at 3:03 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Benedict Holland <benedict.m.holland@gmail.com> wrote:

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

You have three VACUUM FULL commands running?  VACUUM FULL is very
aggressive maintenance, which is only needed for cases of extreme
bloat.  It does lock the table against any concurrent access, since
it is completely rewriting it.

Now, if you are running UPDATE statements which affect all rows in a
table, you will *get* extreme bloat.  You either need to do such
updates as a series of smaller updates with VACUUM commands in
between, or schedule your aggressive maintenance for a time when it
can have exclusive access to the tables with minimal impact.

Reporting the other issues without mentioning the VACUUM FULL
processes is a little bit like calling from the Titanic to mention
that the ship isn't going as fast as it should and neglecting to
mention the iceberg.  :-)

-Kevin

Benedict Holland <benedict.m.holland@gmail.com> wrote:

> Yes. I needed to do a full vacuum. Again, the database is very
> large. I batch inserted quite a lot of data and then modified that
> data. The vacuum isn't blocking anything. It was blocking other
> tables (as expected) but continues to run and clean. My tables in
> general are around 10GB, each update seems to nearly double the
> size of it so I required a full vacuum.

I was trying to suggest techniques which would prevent that bloat
and make the VACUUM FULL unnecessary.  But, now that I've had a
chance to format the attachment into a readable format, I agree that
it isn't part of the problem.  The iceberg in this case is the ALTER
TABLE, which is competing with two other queries.

> The blocked statements are the select count(*) and the alter
> table.

OK.

> Both are blocked on the update table command.

Not directly.  The lock held by the UPDATE would *not* block the
SELECT; but it *does* block the ALTER TABLE command, which can't
share the table while it changes the structure of the table.  The
SELECT is blocked behind the ALTER TABLE.

> The alter table command SHOULD be blocked and that is fine.

I'm glad we're on the same page there.

> The select count(*) should never be blocked as that is the whole
> point of running an MVCC operation at least to my understanding. I
> can even accept the use case that the select should block with an
> Alter Table operation if data is retrieved from the table, but a
> select count(*) only returns the number of rows and should be
> table space independent.

In PostgreSQL SELECT count(*) must scan the table to see which rows
are visible to the executing database transaction.  Without that, it
can't give a completely accurate count from a transactional
perspective.  If you can settle for a non-transactional
approximation, select the reltuples value from the pg_class row for
the table.

> I also don't understand why a select count(*) requires an
> AccessShareLock. I don't understand why a select should lock
> anything at all.

So that the table isn't dropped or truncated while the count is
scanning the table.

-Kevin

Benedict Holland <benedict.m.holland@gmail.com> wrote:

> I can even accept the use case that the select should block with
> an Alter Table operation if data is retrieved from the table, but
> a select count(*) only returns the number of rows and should be
> table space independent.

Just as an example of why the data must be scanned for transactional
behavior.  Open three connections to the same database.  On the
first, run this:

create table t (id int not null);
insert into t select generate_series(1, 1000000);
vacuum analyze t;
begin;
delete from t where id between 1 and 50000;

Then, on the second, run this:

begin;
insert into t select generate_series(1000001, 1000600);

Now, run this on each of the three connections:

select count(*) from t;

You should not get the same count on each one.  Depending on your
transactional context, you will get 950000, 1000600, or 1000000.
Over and over as long as the modifying transactions are open.  If
you want a fast approximation:

select reltuples from pg_class where oid = 't'::regclass;
 reltuples
-----------
    1e+06
(1 row)

-Kevin

Benedict Holland <benedict.m.holland@gmail.com> wrote:

> Is it a bug that the blocking process reported is the finial
> process but really the process blocking the intermediate?

What reported that?  The PostgreSQL server doesn't report such
things directly, and I don't know pgadmin, so I don't know about
that tool.  I wrote the recursive query on this page:

http://wiki.postgresql.org/wiki/Lock_dependency_information

So if that reported anything incorrecly, please let me know so I can
fix it.

By the way, the example with the three connections would have been
better had I suggested a BEGIN TRANSACTION ISOLATION LEVEL
REPEATABLE READ; on the third connection.  With that, even if one or
both of the transactions on the other connections committed, the
third transaction's count should remain unchanged.

-Kevin