Обсуждение: Btree index left link changed unexpectedly after bringing up 8.3.11 warm standby

I'm seeing this on a Pitrtools managed warm standby box that we
periodically bring the db fully up on in order to test if the standby is
good.

After the standby is up, then a db wide VACUUM produces:

2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248500 is
uninitialized --- fixing
2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248502 is
uninitialized --- fixing
2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248504 is
uninitialized --- fixing
2010-10-28 17:20:54 NZDT ERROR:  left link changed unexpectedly in block
21042 of index "content_node_node_type_id_inserted_idx"

I'm guessing the index error is due to the uninitialized table pages
(the index "content_node_node_type_id_inserted_idx" is on the "node"
table). Are the uninitialized pages cause for concern, or merely an
artifact of log shipping?

The is 8.3.11 on Debian Lenny x86-64.

Thanks

Mark

Excerpts from Mark Kirkwood's message of jue oct 28 02:20:56 -0300 2010:
> I'm seeing this on a Pitrtools managed warm standby box that we
> periodically bring the db fully up on in order to test if the standby is
> good.
>
> After the standby is up, then a db wide VACUUM produces:
>
> 2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248500 is
> uninitialized --- fixing
> 2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248502 is
> uninitialized --- fixing
> 2010-10-28 17:20:51 NZDT WARNING:  relation "node" page 248504 is
> uninitialized --- fixing
> 2010-10-28 17:20:54 NZDT ERROR:  left link changed unexpectedly in block
> 21042 of index "content_node_node_type_id_inserted_idx"
>
> I'm guessing the index error is due to the uninitialized table pages
> (the index "content_node_node_type_id_inserted_idx" is on the "node"
> table).

Not necessarily ... You still have the index in that state, right?
We could try some diagnostics on it.

> Are the uninitialized pages cause for concern, or merely an artifact
> of log shipping?

These *should* be innocuous.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 29/10/10 04:32, Alvaro Herrera wrote:
> Excerpts from Mark Kirkwood's message of jue oct 28 02:20:56 -0300 2010:
>
>> I'm guessing the index error is due to the uninitialized table pages
>> (the index "content_node_node_type_id_inserted_idx" is on the "node"
>> table).
>>
> Not necessarily ... You still have the index in that state, right?
> We could try some diagnostics on it.
>
>
>

Doing some more digging - there was an out of memory incident on the
master the previous day, so I guess both of these observations could
well be caused by leftover partially completed operations.

Mark

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> On 29/10/10 04:32, Alvaro Herrera wrote:
>> Excerpts from Mark Kirkwood's message of jue oct 28 02:20:56 -0300 2010:
>>> I'm guessing the index error is due to the uninitialized table pages
>>> (the index "content_node_node_type_id_inserted_idx" is on the "node"
>>> table).

>> Not necessarily ... You still have the index in that state, right?
>> We could try some diagnostics on it.

> Doing some more digging - there was an out of memory incident on the
> master the previous day, so I guess both of these observations could
> well be caused by leftover partially completed operations.

Were there similar warnings on the master?  Uninitialized-page warnings
are expected in certain error-recovery scenarios, but I'd be a little
worried if the slave appeared to be out of sync with the master.

            regards, tom lane

On 29/10/10 10:27, Tom Lane wrote:
>
> Were there similar warnings on the master?  Uninitialized-page warnings
> are expected in certain error-recovery scenarios, but I'd be a little
> worried if the slave appeared to be out of sync with the master.
>
>
>

I don't see any in the logs - however the relevant table may not have
been vacuum recently enough to spit out the warning. I do see lots of these:

2010-09-30 17:31:20 NZDT ERROR:  could not open relation with OID 1836671
2010-09-30 17:31:20 NZDT ERROR:  could not open relation with OID 1836671

and also the following has recently started appearing on the console:

kernel: [ 7341.689322] ECC/ChipKill ECC error.

So... lots of brokeness to examine here (have promoted one of our slaves
to be the new master). I'll see if we still have the old master db
around, it would be interesting to see what happens if we start it up
and try a VACUUM - however the dbas may have set the box up as a slave
again before we noticed the memory errors (so possibly deleted the old
master).

Cheers

Mark