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

http://svr5.postgresql.org/pgsql-hackers/2006-03/msg01198.php
http://www.mail-archive.com/pgsql-general(at)postgresql(dot)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
>

On 8/22/07, Patrick Lindeman <patrick(at)my-mac(dot)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(at)my-mac(dot)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