pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate blocking a select count(*)?
18+ messages / 3 participants
[nested] [flat]
* Update blocking a select count(*)?
@ 2012-06-15 18:22 Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:41 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 18:22 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I am using postgresql 9.0 and I am updating a large table and running a
select count(*). The update is run first and then the select. The update is
blocking the select statement. To use the term MVCC (as seems to be done so
much in this list), well it seems to be broken. MVCC should allow a select
on the same table as an update, in fact nothing at all should block a
select. Also for some reason, the update query seems to always get an
Exclusive Lock which doesn't make any sense to me. At most an update should
require a row lock. This seems to also apply to two updates on the same
table in parallel.
Do I seem to have this right and is there anything I can do?
Thanks,
~Ben
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 18:32 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 18+ messages in thread
From: Peter Geoghegan @ 2012-06-15 18:32 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
On 15 June 2012 19:22, 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.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-06-15 18:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 18:43 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 18:46 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 18:46 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 18:46 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:54 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 18:46 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
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.holland@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.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
>>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 18:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:04 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 18:54 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:54 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 19:04 ` Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 19:04 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
Sure. The last column are the series of commands to produce the outputs.
This is coming from pgadmin3. I should have mentioned before that this is
running windows but that shouldn't matter for this particular sense I hope.
The first column is the PID, the last column is the command running. The
dates are the start time of the operations. The YES/NO is the running state
of the process. In the activity section the 2nd to last column is the
process blocking the executing process.
Thanks,
~Ben
On Fri, Jun 15, 2012 at 2:54 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> 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
>
Attachments:
email.txt (text/plain, 4.3K) download
PG admin 3:
activity
1588 rmv postgres ::1:49275 2012-06-15 12:12:01.09-04 2012-06-15 13:47:35.494-04 2012-06-15 13:47:35.494-04 2940 ALTER TABLE vins DROP COLUMN owner_type;
1736 rmv postgres ::1:49231 2012-06-15 09:50:12.891-04 2012-06-15 13:36:22.997-04 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
2096 rmv postgres ::1:49234 2012-06-15 09:57:55.797-04 2012-06-15 10:25:08.329-04 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, full)
2316 postgres postgres ::1:49301 2012-06-15 13:44:52.795-04 <IDLE>
2620 postgres postgres ::1:49227 2012-06-15 09:44:12.139-04 <IDLE>
2716 rmv postgres ::1:49228 2012-06-15 09:44:15.684-04 <IDLE>
2844 rmv postgres ::1:49303 2012-06-15 13:50:29.021-04 2012-06-15 13:50:46.417-04 2012-06-15 13:50:46.417-04 2940 select count(*) from vins
2940 rmv postgres ::1:49273 2012-06-15 12:01:34.756-04 2012-06-15 13:34:53.55-04 2012-06-15 13:34:53.55-04
update vins
set insp_count=vc.count
from vin_counts vc
where id = vc.vin_id;
3028 postgres postgres ::1:49232 2012-06-15 09:52:11.764-04 <IDLE>
locks:
1588 rmv 33074 postgres 11/71 AccessExclusiveLock No 2012-06-15 13:47:35.494-04 ALTER TABLE vins DROP COLUMN owner_type;
1588 postgres 11/71 11/71 ExclusiveLock Yes 2012-06-15 13:47:35.494-04 ALTER TABLE vins DROP COLUMN owner_type;
1736 rmv 33350 postgres 6/39 RowExclusiveLock 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 33352 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
1736 rmv 33351 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 33349 postgres 6/39 RowExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 rmv 49899 postgres 6/39 AccessShareLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
1736 postgres 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 postgres 6/39 6/39 ExclusiveLock Yes 2012-06-15 13:36:22.997-04 insert into inspections
select * from inspections_1
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 ExclusiveLock 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 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;
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 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 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 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 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 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;
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 19:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 19:03 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 19:12 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:27 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:51 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 3 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 19:12 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 19:27 ` Benedict Holland <benedict.m.holland@gmail.com>
2 siblings, 0 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 19:27 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
For kicks I stopped the full vacuum and the status of the remaining
processes has not changed. The select count(*) is still blocked by the
update.
~Ben
On Fri, Jun 15, 2012 at 3:12 PM, 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.
> 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
>>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 19:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:45 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 19:32 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 19:45 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:00 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 19:45 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
You were completely correct. I stopped the Alter Table and the select is
now running. Is it a bug that the blocking process reported is the finial
process but really the process blocking the intermediate? If alter table
can block a select but the update can't, then I personally would consider
this a rather large bug because from, the DB perspective, the wrong
information is being presented. This also means I am now very skeptical
that the blocking processes are correct in these sorts of situations. I
can't be the first person to discover this and thank you for bearing with
me.
> 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 agree with you somewhat. I would assume that "select count(*)" is special
in the sense that it is table schema independent. I would actually hope
that this is an order 1 operation since the total table length should be
stored somewhere as it's a reasonably useful source of information. Because
it is schema independent, even an alter table shouldn't block it as why
should it? The transaction comes in when you are adding more data to the
end of the table so the select count(*) needs a transaction to guarantee a
finish only. This should not block or be blocked on anything. The where
clause, a group by or a distinct clause etc. should block on an Alter
table. Is this just an edge case which is not worth looking at?
Thank you so much for your help.
~Ben
On Fri, Jun 15, 2012 at 3:32 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:45 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 20:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 20:29 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 20:00 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:45 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:00 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 20:29 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:38 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 20:29 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
I ran the scripts on the page and both returned empty (though I have
queries running and currently nothing blocks). I don't know what they
should have been. The output was from PgAdmin3 which is a UI for postgres.
I assume that they get this queried information from something inside of
postgres as I can't imagine the query tool doing something other than
querying the database for specs. I think it looks at the PID. This very
well might be a PgAdmin issue and have nothing to do with postgres.
~Ben
On Fri, Jun 15, 2012 at 4:00 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:45 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:00 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 20:29 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 20:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 20:38 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
Benedict Holland <benedict.m.holland@gmail.com> wrote:
> I ran the scripts on the page and both returned empty (though I
> have queries running and currently nothing blocks). I don't know
> what they should have been.
It only shows information on blocking, so the list should be empty
when there is none. :-) If it works as intended, it would have
shown the chain of blocking, from the update to the alter to the
select.
> The output was from PgAdmin3 which is a UI for postgres. I assume
> that they get this queried information from something inside of
> postgres as I can't imagine the query tool doing something other
> than querying the database for specs.
They are probably doing something internally which is somewhat
similar to the recursive query on that page. It sounds like when
there is a chain or tree of blocking, they show the process at the
front of the parade, rather than the immediate blocker. I can't say
that's right or wrong, but it should be documented so that people
can understand what they're looking at. Even better would be to
make a nice graphical tree of the blocking, but that would be
getting pretty fancy. :-)
-Kevin
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 19:51 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:58 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 19:51 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Re: Update blocking a select count(*)? Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Re: Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:51 ` Re: Update blocking a select count(*)? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-15 19:58 ` Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Benedict Holland @ 2012-06-15 19:58 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
I see! Thank you very much!
~Ben
On Fri, Jun 15, 2012 at 3:51 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Update blocking a select count(*)?
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
@ 2012-06-15 18:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 18+ messages in thread
From: Kevin Grittner @ 2012-06-15 18:41 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; pgsql-performance
Benedict Holland <benedict.m.holland@gmail.com> wrote:
> I am using postgresql 9.0 and I am updating a large table
Updating as in executing an UPDATE statement, or as in using ALTER
TABLE to update the *structure* of the table?
> and running a select count(*). The update is run first and then
> the select. The update is blocking the select statement.
What is your evidence that there is blocking? (A long run time for
the select does not constitute such evidence. Nor does a longer run
time for the same select when it follows or is concurrent to the
update than it was before.)
> To use the term MVCC (as seems to be done so much in this list),
> well it seems to be broken.
That would be very surprising, and seems unlikely.
> MVCC should allow a select on the same table as an update, in fact
> nothing at all should block a select.
Well, no DML should block a select. DDL can.
> Also for some reason, the update query seems to always get an
> Exclusive Lock which doesn't make any sense to me.
There is no PostgreSQL command which acquires an EXCLUSIVE lock on a
table. An UPDATE will acquire a ROW EXCLUSIVE lock, which is very
different. An ALTER TABLE or TRUNCATE TABLE can acquire an ACCESS
EXCLUSIVE lock, which is the *only* level which can block a typical
SELECT statement.
> At most an update should require a row lock. This seems to also
> apply to two updates on the same table in parallel.
You *really* need to read this chapter in the docs:
http://www.postgresql.org/docs/current/interactive/mvcc.html
The part about the different lock levels and what the conflicts are
might be of particular interest:
http://www.postgresql.org/docs/current/interactive/explicit-locking.html#LOCKING-TABLES
> Do I seem to have this right
No.
> and is there anything I can do?
Probably, but you haven't given us enough information to be able to
suggest what.
-Kevin
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2012-06-15 20:38 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-15 18:22 Update blocking a select count(*)? Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:32 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-06-15 18:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 18:46 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:46 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:04 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:12 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:27 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 19:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:45 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 20:29 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 20:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:51 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-15 19:58 ` Benedict Holland <benedict.m.holland@gmail.com>
2012-06-15 18:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox