Thread: waiting in pg_stats_activity
Hi, little question: when is WAITING in PG_STATS_ACTIVITYset to TRUE ? When this connection is waiting on a lock , or are there any other reasons, waiting on another resource ? TIA ruediger
Hi, While checking the logfiles of our postgresql server I noticed the error mentioned in the subject: ERROR: could not access status of transaction 3221180546 DETAIL: could not open file "pg_clog/0BFF": No such file or directory Searching on google told me that this could be solved by creating the file mentioned in the error using /dev/zero as input. After I have done that and taking another look at the login I also noticed that this error started to show up in the loggin since the 1st of August and from the same date the autovacuum was only vacuuming the 'template0' database. Right now I have created the "pg_clog/0BFF" file and manually started a vacuum. And now my questions: Are those mentioned steps the appropriate steps or should we do anything else? What could have caused the error "could not access status of transaction 3221180546" and is it more than just coincidence that since that moment the vacuum stopped running? Is there anything we can do to prevent this in future use? Besides we are using PostgreSQL 8.1.3. Any help would be appreciated. Regards, Patrick Lindeman
On 8/22/07, Patrick Lindeman <patrick@my-mac.nl> wrote: > Hi, > > While checking the logfiles of our postgresql server I noticed the error > mentioned in the subject: > > ERROR: could not access status of transaction 3221180546 > DETAIL: could not open file "pg_clog/0BFF": No such file or directory What other files are there in the pg_clog directory? > Searching on google told me that this could be solved by creating the file > mentioned in the error using /dev/zero as input. > > After I have done that and taking another look at the login I also noticed > that this error started to show up in the loggin since the 1st of August > and from the same date the autovacuum was only vacuuming the 'template0' > database. OK, there's more going on here than what you're showing us so far. template0 is normally frozen and set to not allow connections. so, what's happened to the db to allow template0 to be connected to? > > Right now I have created the "pg_clog/0BFF" file and manually started a > vacuum. > > And now my questions: > > Are those mentioned steps the appropriate steps or should we do anything > else? > > What could have caused the error "could not access status of transaction > 3221180546" and is it more than just coincidence that since that moment > the vacuum stopped running? Maybe transaction wraparound? I'm not really sure. > Is there anything we can do to prevent this in future use? > > Besides we are using PostgreSQL 8.1.3. Upgrading your postgresql version would not be a bad idea. 8.1 is up to 8.1.9 or so by now. and I know there were some bugs in 8.1.3 or so that could cause things like this to happen. Not sure your exact problem is one of them, but you should really keep up to date on patchs for your pg version.
Thanks for your replies so far, it seems like the problem has been solved, for now! > On 8/22/07, Patrick Lindeman <patrick@my-mac.nl> wrote: >> Hi, >> >> While checking the logfiles of our postgresql server I noticed the error >> mentioned in the subject: >> >> ERROR: could not access status of transaction 3221180546 >> DETAIL: could not open file "pg_clog/0BFF": No such file or directory > > What other files are there in the pg_clog directory? > There are lots of files in the pg_clog directory running from 01FF until 0250. >> Searching on google told me that this could be solved by creating the >> file >> mentioned in the error using /dev/zero as input. >> >> After I have done that and taking another look at the login I also >> noticed >> that this error started to show up in the loggin since the 1st of August >> and from the same date the autovacuum was only vacuuming the 'template0' >> database. > > OK, there's more going on here than what you're showing us so far. > template0 is normally frozen and set to not allow connections. so, > what's happened to the db to allow template0 to be connected to? > I dont know what happened, but one of the solutions posted in the reply from Jeff pointed out that I should 'vacuum freeze' template0. While trying to connect I got the error that I couldn't connect since "template0" was not accepting connections. After manually executing the query: UPDATE pg_database SET datallowconn = true where datname='template0'; I was able to connect and execute 'vacuum freeze;' It seems like vacuum is running on all databases again so it seems like the fix works for now. >> >> Right now I have created the "pg_clog/0BFF" file and manually started a >> vacuum. >> >> And now my questions: >> >> Are those mentioned steps the appropriate steps or should we do anything >> else? >> >> What could have caused the error "could not access status of transaction >> 3221180546" and is it more than just coincidence that since that moment >> the vacuum stopped running? > > Maybe transaction wraparound? I'm not really sure. > >> Is there anything we can do to prevent this in future use? >> >> Besides we are using PostgreSQL 8.1.3. > > Upgrading your postgresql version would not be a bad idea. 8.1 is up > to 8.1.9 or so by now. and I know there were some bugs in 8.1.3 or so > that could cause things like this to happen. > Not sure your exact problem is one of them, but you should really keep > up to date on patchs for your pg version. > We already had in mind that we needed to upgrade to 8.1.9 or even 8.2. This problem just made this upgrade more urgent. Thank you both again ! Regards, Patrick Lindeman
http://svr5.postgresql.org/pgsql-hackers/2006-03/msg01198.php http://www.mail-archive.com/pgsql-general@postgresql.org/msg90845.html This is related to an autovacuum bug and the freeze logic with template0....and probably a corrupted pg_statistics table in template0.... you should upgrade AND repair the template0 issue (I actually replaced template0 with a copy from a replicant database) ......But take heed, if you don't 'do' something about it, autovacuum is actually stopping with tempate 0 and never getting to the rest of your database (as I found out) Patrick Lindeman wrote: > What could have caused the error "could not access status of transaction > 3221180546" and is it more than just coincidence that since that moment > the vacuum stopped running? > > Is there anything we can do to prevent this in future use? > > Besides we are using PostgreSQL 8.1.3. > > Any help would be appreciated. > > Regards, > Patrick Lindeman > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >