Thread: could not truncate directory "pg_subtrans": apparent wraparound

could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:
Hi

got the following line at postgresql log:

May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate directory "pg_subtrans": apparent wraparound

I assume this does not refer to xid wraparound? Should I be worried?

Database has only a few users and probably noone accessed it during those hours. No other relevant lines were in the log, no crashes etc have occured.

postgres=# select version();
                                                       version                                                        
----------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit


Regards

Mikko

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Ray Stell
Date:
On Wed, May 19, 2010 at 08:40:04AM +0300, Mikko Partio wrote:
>
> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound
>

http://archives.postgresql.org/pgsql-general/2007-06/msg01050.php

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Tom Lane
Date:
Mikko Partio <mpartio@gmail.com> writes:
> got the following line at postgresql log:
> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound

What's in $PGDATA/pg_subtrans?

            regards, tom lane

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:


On Wed, May 19, 2010 at 10:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mikko Partio <mpartio@gmail.com> writes:
> got the following line at postgresql log:
> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound

What's in $PGDATA/pg_subtrans?


$ ls -l $PGDATA/pg_subtrans
total 228
-rw------- 1 postgres dba 229376 May 20 07:27 000E


Regards

Mikko 

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:


On Wed, May 19, 2010 at 3:21 PM, Ray Stell <stellr@cns.vt.edu> wrote:
On Wed, May 19, 2010 at 08:40:04AM +0300, Mikko Partio wrote:
>
> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound
>

http://archives.postgresql.org/pgsql-general/2007-06/msg01050.php


Browsing through that thread I can see that I have similar symptoms:

$ pg_controldata | grep Multi
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0

$ ls -l $PGDATA/pg_multixact/members
total 8
-rw------- 1 postgres dba 8192 May  3 08:21 0000

$ ls -l $PGDATA/pg_multixact/offsets
total 8
-rw------- 1 postgres dba 8192 May  3 08:49 0000

I don't see any resolution to that thread though.

Regards

Mikko


Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Alvaro Herrera
Date:
Excerpts from Mikko Partio's message of jue may 20 00:39:00 -0400 2010:
> On Wed, May 19, 2010 at 3:21 PM, Ray Stell <stellr@cns.vt.edu> wrote:

> > http://archives.postgresql.org/pgsql-general/2007-06/msg01050.php
>
> Browsing through that thread I can see that I have similar symptoms:
>
> $ pg_controldata | grep Multi
> Latest checkpoint's NextMultiXactId:  1
> Latest checkpoint's NextMultiOffset:  0

I don't remember why I insisted talking about multixacts in that thread,
but seeing that both the poster in the 2007 thread and you have problems
with the pg_subtrans directory, not pg_multixact, these "symptoms"
actually mean nothing at all.  pg_subtrans and pg_multixact are
unrelated.

I wonder if there's an off-by-one bug in SimpleLruTruncate, or perhaps
the "cutoffPage" value passed by the caller is bogus once every ten blue
moons ... hmm, it comes from GetOldestXmin ...

Being more explicit in that error message (logging the cutoffPage) would
help figuring out what's going on.
--

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:


On Thu, May 20, 2010 at 7:53 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Excerpts from Mikko Partio's message of jue may 20 00:39:00 -0400 2010:
> On Wed, May 19, 2010 at 3:21 PM, Ray Stell <stellr@cns.vt.edu> wrote:

> > http://archives.postgresql.org/pgsql-general/2007-06/msg01050.php
>
> Browsing through that thread I can see that I have similar symptoms:
>
> $ pg_controldata | grep Multi
> Latest checkpoint's NextMultiXactId:  1
> Latest checkpoint's NextMultiOffset:  0

I don't remember why I insisted talking about multixacts in that thread,
but seeing that both the poster in the 2007 thread and you have problems
with the pg_subtrans directory, not pg_multixact, these "symptoms"
actually mean nothing at all.  pg_subtrans and pg_multixact are
unrelated.

I wonder if there's an off-by-one bug in SimpleLruTruncate, or perhaps
the "cutoffPage" value passed by the caller is bogus once every ten blue
moons ... hmm, it comes from GetOldestXmin ...


So, I gather this error (is it even error if it's logged on LOG level?) isn't anything serious?

Regards

Mikko

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Tom Lane
Date:
Mikko Partio <mpartio@gmail.com> writes:
> got the following line at postgresql log:

> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound

>  PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

Where did this beta1 installation come from --- was it freshly initdb'd
under 9.0, or did you use pg_upgrade on a pre-existing database that had
been around for awhile?  It strikes me that the recently identified bug
in pg_upgrade about not fixing the datfrozenxid of template0 might
possibly explain this.  You'd need to have upgraded an installation that
was at least a couple billion transactions old to hit that bug.

            regards, tom lane

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:


On Sun, May 23, 2010 at 9:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mikko Partio <mpartio@gmail.com> writes:
> got the following line at postgresql log:

> May 16 01:17:35 xxx postgres[25550]: [1-1] LOG:  could not truncate
> directory "pg_subtrans": apparent wraparound

>  PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

Where did this beta1 installation come from --- was it freshly initdb'd
under 9.0, or did you use pg_upgrade on a pre-existing database that had
been around for awhile?  It strikes me that the recently identified bug
in pg_upgrade about not fixing the datfrozenxid of template0 might
possibly explain this.  You'd need to have upgraded an installation that
was at least a couple billion transactions old to hit that bug.


It was freshly initdb'd with beta1 binaries, the contents were loded from a pg_dump file. The number of transactions is very small, we're talking about thousands (not billions). This database is the master of a hot standby installation, if that matters.

Regards

Mikko

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Simon Riggs
Date:
On Mon, 2010-05-24 at 08:46 +0300, Mikko Partio wrote:

> It was freshly initdb'd with beta1 binaries, the contents were loded
> from a pg_dump file. The number of transactions is very small, we're
> talking about thousands (not billions). This database is the master of
> a hot standby installation, if that matters.

Have you ever performed a switchover operation? If you've never run an
extended recovery on that server, its less likely to be anything HS
related.

Are you running any special hot standby parameters?

--
 Simon Riggs           www.2ndQuadrant.com


Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Mikko Partio
Date:


On Mon, May 24, 2010 at 10:55 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-05-24 at 08:46 +0300, Mikko Partio wrote:

> It was freshly initdb'd with beta1 binaries, the contents were loded
> from a pg_dump file. The number of transactions is very small, we're
> talking about thousands (not billions). This database is the master of
> a hot standby installation, if that matters.

Have you ever performed a switchover operation? If you've never run an
extended recovery on that server, its less likely to be anything HS
related.

Are you running any special hot standby parameters?


With this database instance (beta1 initdb'd) I have not made failovers. I don't think I have any special hot standby parameters either. 

Non-default hot standby related configuration options (master database):

wal_level = hot_standby                 # minimal, archive, or hot_standby
archive_mode = on               # allows archiving to be done
archive_command = '/postgresql/bin/archive_wal.sh "%p" "%f"'            # command to use to archive a logfile segment
archive_timeout = 3600          # force a logfile segment switch after this
hot_standby = off               # allows queries during recovery
max_wal_senders = 3             # max number of walsender processes
wal_keep_segments = 10          # in logfile segments, 16MB each; 0 disables

Regards

Mikko

Re: could not truncate directory "pg_subtrans": apparent wraparound

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 09:01 +0300, Mikko Partio wrote:

> With this database instance (beta1 initdb'd) I have not made
> failovers. I don't think I have any special hot standby parameters
> either.

OK, so that pretty much rules out HS as a direct cause.

> Non-default hot standby related configuration options (master
> database):
>
>
> wal_level = hot_standby         # minimal, archive, or hot_standby
> archive_mode = on               # allows archiving to be done
> archive_command = '/postgresql/bin/archive_wal.sh "%p" "%f"'
>    # command to use to archive a logfile segment
> archive_timeout = 3600          # force a logfile segment switch after
> this
> hot_standby = off               # allows queries during recovery
> max_wal_senders = 3             # max number of walsender processes
> wal_keep_segments = 10          # in logfile segments, 16MB each; 0
> disables

--
 Simon Riggs           www.2ndQuadrant.com