I've been struggling with this issue for the last several days, and I feel
like I'm running into a few different issues that I don't understand. I'm
using postgres 9.0.8, and here's the OS I'm running this on:
inux 2.6.18-308.4.1.el5xen #1 SMP Tue Apr 17 17:49:15 EDT 2012 x86_64
x86_64 x86_64
Anish,
I've been struggling with this issue for the last several days, and I feel
like I'm running into a few different issues that I don't understand. I'm
using postgres 9.0.8, and here's the OS I'm running this on:
inux 2.6.18-308.4.1.el5xen #1 SMP Tue Apr 17 17:49:15 EDT 2012 x86_64
On Fri, Jun 15, 2012 at 9:17 AM, Anish Kejariwal anish...@gmail.com wrote:
Below are the tables, queries, and execution plans with my questions with
more detail. (Since I have 250 partitions, I can query one partition after
the other to ensure that I'm not pulling results form the cache)
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
On 15 June 2012 19:22, 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
Benedict Holland benedict.m.holl...@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
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
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
Sorry about the raw text but this is what I am seeing:
1736postgres6/396/39ExclusiveLockYes
2012-06-15 13:36:22.997-04insert into inspections
select * from inspections_1
1736rmv49896postgres6/39AccessShareLockYes
2012-06-15
Benedict Holland benedict.m.holl...@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
Benedict Holland benedict.m.holl...@gmail.com wrote:
10:25:08.329-04vacuum (analyze, verbose, full)
2096rmv33528postgres8/151
AccessExclusiveLock
Yes2012-06-15 10:25:08.329-04vacuum (analyze, verbose,
full)
2096rmv50267postgres8/151
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
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
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.holl...@gmail.com wrote:
Yes. I needed to do a full vacuum. Again, the
Benedict Holland benedict.m.holl...@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
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
Benedict Holland benedict.m.holl...@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
I see! Thank you very much!
~Ben
On Fri, Jun 15, 2012 at 3:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Benedict Holland benedict.m.holl...@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
Benedict Holland benedict.m.holl...@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
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
Benedict Holland benedict.m.holl...@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
21 matches
Mail list logo
