Thread: Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS NOT THE SAME AS HEAP' (1070)

Anyone seen this message or know what it means?

NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
NOT THE SAME AS HEAP' (1070)

Cheers,
Ed Loehr

> Anyone seen this message or know what it means?
>
> NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> NOT THE SAME AS HEAP' (1070)

Drop index and recreate.  Next release will be more specific in error
message.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > Anyone seen this message or know what it means?
> >
> > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > NOT THE SAME AS HEAP' (1070)
>
> Drop index and recreate.  Next release will be more specific in error
> message.

I have no idea *which* index to drop/recreate, and I have hundreds of them.
Ouch.

> Bruce Momjian wrote:
>
> > > Anyone seen this message or know what it means?
> > >
> > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > NOT THE SAME AS HEAP' (1070)
> >
> > Drop index and recreate.  Next release will be more specific in error
> > message.
>
> I have no idea *which* index to drop/recreate, and I have hundreds of them.
> Ouch.

That will also be fixed.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > Bruce Momjian wrote:
> >
> > > > Anyone seen this message or know what it means?
> > > >
> > > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > > NOT THE SAME AS HEAP' (1070)
> > >
> > > Drop index and recreate.  Next release will be more specific in error
> > > message.
> >
> > I have no idea *which* index to drop/recreate, and I have hundreds of them.
> > Ouch.
>
> That will also be fixed.

Do you mean to say the offending index will be auto-corrected on the fly?  That
would be almost as good as preventing the root cause in the first place...

Cheers,
Ed Loehr

> > That will also be fixed.
>
> Do you mean to say the offending index will be auto-corrected on the fly?  That
> would be almost as good as preventing the root cause in the first place...

No, it just reports the index name.  In 7.1, I think this problem will
go away, if not in 7.0.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > > That will also be fixed.
> >
> > Do you mean to say the offending index will be auto-corrected on the fly?  That
> > would be almost as good as preventing the root cause in the first place...
>
> No, it just reports the index name.  In 7.1, I think this problem will
> go away, if not in 7.0.

Is the problem well-understood?  Is there a place where I can read up on it?  This
kind of instability is painful enough to get me thinking about trying to hack my
distribution...

Cheers,
Ed Loehr

> Bruce Momjian wrote:
>
> > > > That will also be fixed.
> > >
> > > Do you mean to say the offending index will be auto-corrected on the fly?  That
> > > would be almost as good as preventing the root cause in the first place...
> >
> > No, it just reports the index name.  In 7.1, I think this problem will
> > go away, if not in 7.0.
>
> Is the problem well-understood?  Is there a place where I can read up on it?  This
> kind of instability is painful enough to get me thinking about trying to hack my
> distribution...

I believe it has to do with extra index tuples showing up in the index
that are not in the heap.  When the count's don't match, the problem is
reported.  I believe it only happens when the system crashes during an
index update.  I think it is harmless.  To fix it properly requires a
very sophisticated write-ahead log that is scheduled for 7.1 in about
six months.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > Is the problem well-understood?  Is there a place where I can read up on it?  This
> > kind of instability is painful enough to get me thinking about trying to hack my
> > distribution...
>
> I believe it has to do with extra index tuples showing up in the index
> that are not in the heap.  When the count's don't match, the problem is
> reported.  I believe it only happens when the system crashes during an
> index update.

That is consistent with my crash experiences this evening.

>  I think it is harmless.  To fix it properly requires a
> very sophisticated write-ahead log that is scheduled for 7.1 in about
> six months.

This problem stops my psql dead in its tracks for related queries even across new
sessions.  Requires a rebuild of indices before any queries work with the related
tables/functions, and since I don't know which one to rebuild (die, horsey, die), I
might as well rebuild them all.  In production mode, that means stopping user access due
to the possibility of violating unique constraints enforced by unique indices.  That
means downtime, which would makes moi persona non grata.  But maybe my assumptions are
incorrect or I didn't understand what you mean by harmless?

Cheers,
Ed Loehr

> >  I think it is harmless.  To fix it properly requires a
> > very sophisticated write-ahead log that is scheduled for 7.1 in about
> > six months.
>
> This problem stops my psql dead in its tracks for related queries even across new
> sessions.  Requires a rebuild of indices before any queries work with the related
> tables/functions, and since I don't know which one to rebuild (die, horsey, die), I
> might as well rebuild them all.  In production mode, that means stopping user access due
> to the possibility of violating unique constraints enforced by unique indices.  That
> means downtime, which would makes moi persona non grata.  But maybe my assumptions are
> incorrect or I didn't understand what you mean by harmless?

Maybe other people can chime in here.  Why are you getting the inital
crashes?


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > >  I think it is harmless.  To fix it properly requires a
> > > very sophisticated write-ahead log that is scheduled for 7.1 in about
> > > six months.
> >
> > This problem stops my psql dead in its tracks for related queries even across new
> > sessions.  Requires a rebuild of indices before any queries work with the related
> > tables/functions, and since I don't know which one to rebuild (die, horsey, die), I
> > might as well rebuild them all.  In production mode, that means stopping user access due
> > to the possibility of violating unique constraints enforced by unique indices.  That
> > means downtime, which would makes moi persona non grata.  But maybe my assumptions are
> > incorrect or I didn't understand what you mean by harmless?
>
> Maybe other people can chime in here.  Why are you getting the inital
> crashes?

I don't know.  My only suspect right now is that it may be the residual effects of having
parameter mismatches in 'RAISE' statements in PL/pgSQL.  In any event, I'll try to collect
some data for troubleshooting...

Cheers,
Ed Loehr

Bruce Momjian wrote:
>
> > Bruce Momjian wrote:
> >
> > > > Anyone seen this message or know what it means?
> > > >
> > > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > > NOT THE SAME AS HEAP' (1070)
> > >
> > > Drop index and recreate.  Next release will be more specific in error
> > > message.
> >
> > I have no idea *which* index to drop/recreate, and I have hundreds of them.
> > Ouch.
>
> That will also be fixed.

I thought that the index in question was, in fact,
pg_proc_prosrc_index in the above example. If that's the
case, then is it possible for Ed to rebuild a system index?
The only absolutely surefire way is to dump/reload, isn't
it? Maybe somewhere someone is doing a heap_insert(),
heap_replace(), et al, and an event is happening which is
causing the code to not get to the
CatalogOpenIndices()/CatalogIndexInsert()/CatalogCloseIndices()...

Just curious,

Mike Mascari

> Bruce Momjian wrote:
> >
> > > Bruce Momjian wrote:
> > >
> > > > > Anyone seen this message or know what it means?
> > > > >
> > > > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > > > NOT THE SAME AS HEAP' (1070)
> > > >
> > > > Drop index and recreate.  Next release will be more specific in error
> > > > message.
> > >
> > > I have no idea *which* index to drop/recreate, and I have hundreds of them.
> > > Ouch.
> >
> > That will also be fixed.
>
> I thought that the index in question was, in fact,
> pg_proc_prosrc_index in the above example. If that's the
> case, then is it possible for Ed to rebuild a system index?
> The only absolutely surefire way is to dump/reload, isn't
> it? Maybe somewhere someone is doing a heap_insert(),
> heap_replace(), et al, and an event is happening which is
> causing the code to not get to the
> CatalogOpenIndices()/CatalogIndexInsert()/CatalogCloseIndices()...

Signe me up as a dope.  Yes, it is clearly that index.  I was thinking
of another place that has this problem, the famous "My bits moved off
the end of the world"  error message.  This one is clearly the
pg_proc_prosrc_index index.

The only way to fix that is to initdb, I think.  I would recommend
pg_upgrade, after removing the disable from the pg_upgrade script that
was added in 6.5.  That will fix it.  Not sure how it got that way,
though.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> > > > > > Anyone seen this message or know what it means?
> > > > > >
> > > > > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > > > > NOT THE SAME AS HEAP' (1070)
> > > > >
>
> The only way to fix that is to initdb, I think.  I would recommend
> pg_upgrade, after removing the disable from the pg_upgrade script that
> was added in 6.5.  That will fix it.  Not sure how it got that way,
> though.

El dope primero aqui...

Thanks for the tip.  Here's what I had to do to make this work, for what it's worth
to future travelers...

Cheers,
Ed Loehr

psql -d mydb -c "select * from title;" > before
                                             # Save for after cmp test...
cd /usr/local/pgsql                          # my install root...
vacuumdb mydb                                # clean up before moving...
cp -pr data data.backup                      # make a backup...
pg_dumpall -s > schema.sql                   # dump schema w/out data...
killall postmaster                           # stop the server...
sleep 3
mv data data.old                             # set it aside...
cd /usr/src/pgsql/src                        # to the src tree...
gmake install                                # reinstall binaries...
cd /usr/local/pgsql                          # back to my install root...
initdb                                       # recreate template1, sys stuff...
postmaster -i -o "-F -S 4096 -s" >& log &    # restart server...
sleep 3
pg_upgrade -f schema.sql data >& upgrade.log # reload schema...
cp -p data.old/base/mydb/* data/base/mydb/   # replace data...
psql -d mydb -c "select * from title;" > after
                                             # verify we still have data...
diff before after                            # quick sanity check...

> Bruce Momjian wrote:
>
> > > > > > > Anyone seen this message or know what it means?
> > > > > > >
> > > > > > > NOTICE:  Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS
> > > > > > > NOT THE SAME AS HEAP' (1070)
> > > > > >
> >
> > The only way to fix that is to initdb, I think.  I would recommend
> > pg_upgrade, after removing the disable from the pg_upgrade script that
> > was added in 6.5.  That will fix it.  Not sure how it got that way,
> > though.
>
> El dope primero aqui...
>
> Thanks for the tip.  Here's what I had to do to make this work, for what it's worth
> to future travelers...

Glad to see pg_upgrade fixed you.



--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026