Thread: Re: [GENERAL] database corruption?

Chris Jones schrieb:

> I'm currently getting this error on my nightly vacuum.  These two
> indices (as you may have guessed already) are on columns named
> interface and ewhen, on a table named error.  The error table is
> constantly being updated.  (No comments about the implications of
> that, please.)
>
> NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)

The Index does not contain tuples having NULL in the indexed field.
You should check if you have an tuple containing NULL in your table.
If so remove or change the tuple or ignore the message.

Elmar

Elmar.Haneke@gmx.de writes:

> Chris Jones schrieb:
>
> > NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
>
> The Index does not contain tuples having NULL in the indexed field.
> You should check if you have an tuple containing NULL in your table.
> If so remove or change the tuple or ignore the message.

Table    = error
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null default nextval (  |     4 |
| type                             | int2 not null                    |     2 |
| ewhen                            | datetime not null                |     8 |
| interface                        | varchar() not null               |    40 |
| severity                         | varchar()                        |     5 |
| extra                            | text                             |   var |
+----------------------------------+----------------------------------+-------+
Indices:  error_ewhen_idx
          error_extra_idx
          error_pkey

Since I wrote the original message, I've gotten rid of the "interface"
index and added the "extra" index, but I haven't changed any of the
columns in the table itself.  As you can see, they're declared as "not
null."

Dropping and re-creating the indices made the warning go away, though.

In addition, I started getting this two nights ago:

NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (74) IS NOT THE SAME AS HEAP' (75)
NOTICE:  Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (74) IS NOT THE SAME AS HEAP' (75)

Needless to say, I haven't changed the schema for the PostgreSQL
system tables at all.

Chris

--
---------------------------------------------------- cjones@rightnowtech.com
Chris Jones
           System Administrator, Right Now Technologies, Inc.
"Is this going to be a stand-up programming session, sir, or another bug hunt?"

Chris Jones wrote:
>
> Elmar.Haneke@gmx.de writes:
>
> > Chris Jones schrieb:
> >
> > > NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > > NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > > NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> > > NOTICE:  Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765)
> >
> > The Index does not contain tuples having NULL in the indexed field.
> > You should check if you have an tuple containing NULL in your table.
> > If so remove or change the tuple or ignore the message.
>
> Table    = error
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | id                               | int4 not null default nextval (  |     4 |
> | type                             | int2 not null                    |     2 |
> | ewhen                            | datetime not null                |     8 |
> | interface                        | varchar() not null               |    40 |
> | severity                         | varchar()                        |     5 |
> | extra                            | text                             |   var |
> +----------------------------------+----------------------------------+-------+
> Indices:  error_ewhen_idx
>           error_extra_idx
>           error_pkey
>
> Since I wrote the original message, I've gotten rid of the "interface"
> index and added the "extra" index, but I haven't changed any of the
> columns in the table itself.  As you can see, they're declared as "not
> null."
>
> Dropping and re-creating the indices made the warning go away, though.
>
> In addition, I started getting this two nights ago:
>
> NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (74) IS NOT THE SAME AS HEAP' (75)
> NOTICE:  Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (74) IS NOT THE SAME AS HEAP' (75)

IIRC, I think the problem and solution is basically the same:
corrupted index needing drop/rebuild.  Given they're system indices,
though, you may need to dump, destroydb, createdb, and reload.  Come
to think of it, these might possibly require initdb.  But maybe
someone with more pgsql experience here can confirm/correct that?

Cheers,
Ed Loehr