Various serverlog messages (hey Sxooter ;P )

csn

I was looking through my serverlog and there are a few
items I have questions about:

ERROR: mdfd_getrelnfd: cannot open relation
pg_toast_16384_index: No such file or directory
LOG: recycled transaction log file 00000000000000AB
LOG: recycled transaction log file 00000000000000AC
LOG: recycled transaction log file 00000000000000AD
LOG: recycled transaction log file 00000000000000AE
LOG: recycled transaction log file 00000000000000AF
LOG: pq_recvbuf: unexpected EOF on client connection
ERROR: mdfd_getrelnfd: cannot open relation
pg_toast_16384_index: No such file or directory
LOG: recycled transaction log file 00000000000000B0

Anything to worry about? What's the message about the
toast index mean - db corruption?

What are the recycled transaction log messages about?
Do I need to worry about ID's wrapping?

CSN

Sxooter

The LOG: entries are normal, basically after a transaciton log gets full pgsql starts a new one, and once the old one has no more live transactions it gets recycled. Completely normal. Transaction ID wraparound occurs at about 4 billion transactions, and be prevented by fully vacuuming all your databases before that happens. Once a week to once a year is usually enough. I think the database starts warning you at around 2 billion transactions (i.e. when the highest bit gets set).

The errors ARE something to worry about, maybe. You could be suffering from some kind of corruption problem. Do you have any queries that are failing when that happens?

csn

Here's what The Man 😉 said:

CSN writes:

I was looking through my serverlog and there are a few
items I have questions about:

LOG: recycled transaction log file 00000000000000AD
LOG: recycled transaction log file 00000000000000AE
LOG: recycled transaction log file 00000000000000AF
LOG: pq_recvbuf: unexpected EOF on client connection

These are routine (that's why they're marked LOG).

ERROR: _mdfd_getrelnfd: cannot open relation
pg_toast_16384_index: No such file or directory

This is definitely not routine. Assuming you're running 7.3,
this seems to indicate that you've lost the toast index for
pg_attrdef. Which would be a bad thing, although we can hope
that maybe you didn't have any default expressions long enough
to need to be toasted.

Could we see the output of
select oid,* from pg_class where relname = 'pg_toast_16384_index';
(you should repeat this in each of your databases to make sure
they all give the same answers).

Are you able to do a pg_dump?
		regards, tom lane[/QUOTE]

csn

I replied:

--- Tom Lane wrote:

ERROR: _mdfd_getrelnfd: cannot open relation
pg_toast_16384_index: No such file or directory

This is definitely not routine. Assuming you're
running 7.3,
this seems to indicate that you've lost the toast
index for
pg_attrdef. Which would be a bad thing, although we
can hope
that maybe you didn't have any default expressions
long enough
to need to be toasted.

Could we see the output of
select oid,* from pg_class where relname =
'pg_toast_16384_index';
(you should repeat this in each of your databases to
make sure
they all give the same answers).

Are you able to do a pg_dump?

Yes, pg_dump and pg_dumpall appear to work fine. The
output for all of the dbs (except one) for the select
above is:

oid | relname | relnamespace | reltype
| relowner | relam | relfilenode | relpages |
reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts |
relchecks | reltriggers | relukeys | relfkeys |
relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
-------+----------------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
16656 | pg_toast_16384_index | 99 | 0
| 1 | 403 | 16656 | 1 |

0 | 0 | 0 | f | f

| i | 2 | 0 | 0
| 0 | 0 | 0 | f | f

| f | f |
(1 row)

Here's the exception:

oid | relname | relnamespace | reltype
| relowner | relam | relfilenode | relpages |
reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts |
relchecks | reltriggers | relukeys | relfkeys |
relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
-------+----------------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
16656 | pg_toast_16384_index | 99 | 0
| 1 | 403 | 3682590432 | 1 |

0 | 0 | 0 | f | f

| i | 2 | 0 | 0
| 0 | 0 | 0 | f | f

| f | f |
(1 row)

The 3682590432 value.

BTW, what is toast (for big fields?) and (toasted)
default expressions?

TIA,
CSN

csn

Then he said:

CSN writes:

Yes, pg_dump and pg_dumpall appear to work fine. The
output for all of the dbs (except one) for the select
above is:
[ normal looking ]
Here's the exception:

16656 | pg_toast_16384_index | 99 | 0
| 1 | 403 | 3682590432 | 1 |

0 | 0 | 0 | f | f

| i | 2 | 0 | 0
| 0 | 0 | 0 | f | f

| f | f |
(1 row)

Sure enough, the relfilenode column (which determines the actual on-disk filename) is clobbered. Weird as can be ... could you have suffered a hardware glitch affecting just that one word? Doesn't seem real likely --- the glitches I've seen in the past tend to take out more than a word at a time. But it's not easy to credit as a software error either.

You could fix this database with a quick UPDATE command to set
relfilenode back to what it should be in this pg_class row. However, it'd be wise to wonder what other issues might be lurking. If I were you I'd do a pg_dumpall/initdb/reload cycle, and also spend some time on hardware testing (if you're on Intel hardware, memtest86 has a good reputation for finding RAM problems, and people have successfully found disk problems with badblocks).
		regards, tom lane

csn

I fixed the value with an UPDATE. There didn't appear to be any errors occuring with queries, dumps, etc. in that database. Just happened to be poking around in the logs. BTW, is there a way to get datetimes to appear alongside log entries?

What is all this toast stuff anyhow - isn 't it used for really large fields?

ultraslacker

Toast is used for inserting rows that are at least 1/4 of your blocksize (set in postgresql.conf, default 8k). Data compression is used to move the row out of toast.