Обсуждение: BUG #2113: PostgreSQL hangs after running for 2 days

The following bug has been logged online:

Bug reference:      2113
Logged by:          Wojciech Sobczuk
Email address:      wojtek@brandlay.com
PostgreSQL version: 8.0.3
Operating system:   FreeBSD 5.4-RELEASE-p1
Description:        PostgreSQL hangs after running for 2 days
Details:

I have a 16GB database which is very busy (100 requests per second).  After
running for some time (sometimes 2 days sometimes longer sometimes shorter)
postgres hangs.  Last time it happened I got the following error in the
log:

    ERROR:  could not access status of transaction 0
    DETAIL:  could not create file "/opt/pgdata/pg_subtrans/6E75": File
exists
    STATEMENT:  SET DATESTYLE TO 'ISO'
    WARNING:  StartTransaction while in START state

I'm running postgres on 3 RAID 1 arrays, with various tables scattered over
the arrays (using tablespaces) for more speed.  The space usage for those
arrays is less than 16% so it is not a space issue.  The machine never
reboots/hangs/etc. so it seems to be a postgres issue.  If you have further
questions please let me know.

Regards

""Wojciech Sobczuk"" <wojtek@brandlay.com> wrote
>
> I have a 16GB database which is very busy (100 requests per second).
> After
> running for some time (sometimes 2 days sometimes longer sometimes
> shorter)
> postgres hangs.

Can you follow the steps in this thread to find out what's going on of the
hanged process (but for sure there is no such problem in 8.x):

http://archives.postgresql.org/pgsql-hackers/2005-10/msg00459.php

Regards,
Qingqing

"Wojciech Sobczuk" <wojtek@brandlay.com> writes:
> I have a 16GB database which is very busy (100 requests per second).  After
> running for some time (sometimes 2 days sometimes longer sometimes shorter)
> postgres hangs.  Last time it happened I got the following error in the
> log:

>     ERROR:  could not access status of transaction 0
>     DETAIL:  could not create file "/opt/pgdata/pg_subtrans/6E75": File
> exists
>     STATEMENT:  SET DATESTYLE TO 'ISO'
>     WARNING:  StartTransaction while in START state

Hmmm ... it seems just possible that this is related to a recent fix:

2005-11-02 19:23  tgl

    * src/backend/access/transam/slru.c (REL8_0_STABLE): Fix
    longstanding race condition in transaction log management: there
    was a very narrow window in which SimpleLruReadPage or
    SimpleLruWritePage could think that I/O was needed when it wasn't
    (and indeed the buffer had already been assigned to another page).
    This would result in an Assert failure if Asserts were enabled, and
    probably in silent data corruption if not.  Reported independently
    by Jim Nasby and Robert Creager.

Would you update to 8.0.5 and let us know if you still see the failure?

            regards, tom lane

"Wojciech Sobczuk" <wojtek@brandlay.com> writes:
> I have a 16GB database which is very busy (100 requests per second).  After
> running for some time (sometimes 2 days sometimes longer sometimes shorter)
> postgres hangs.  Last time it happened I got the following error in the
> log:

>     ERROR:  could not access status of transaction 0
>     DETAIL:  could not create file "/opt/pgdata/pg_subtrans/6E75": File
> exists
>     STATEMENT:  SET DATESTYLE TO 'ISO'
>     WARNING:  StartTransaction while in START state

I think we've identified the reason for this:
http://archives.postgresql.org/pgsql-committers/2006-01/msg00287.php

            regards, tom lane