Обсуждение: reindexdb program error under PG 8.1.3

When I create a brand new database and then run the reindexdb program
under PG 8.1.3, I get the follow error:

reindexdb: reindexing of database "xxxx" failed: ERROR:  could not open
relation with OID 41675

I'm not sure what this error is since we have not even added any data
yet.  Is this something that's misconfigured in the template1 database
(we have not customized it), something we're doing wrong, or is this
"normal"?

Note that prior to this error, we get many NOTICE messages about each
table being reindexed just as we'd like.

Thanks,
David

David Wall <d.wall@computer.org> writes:
> When I create a brand new database and then run the reindexdb program
> under PG 8.1.3, I get the follow error:

> reindexdb: reindexing of database "xxxx" failed: ERROR:  could not open
> relation with OID 41675

> I'm not sure what this error is since we have not even added any data
> yet.  Is this something that's misconfigured in the template1 database
> (we have not customized it), something we're doing wrong, or is this
> "normal"?

I think you've done something to template1, even though you say you
haven't.  Out-of-the-box, an 8.1.3 database won't have any OIDs assigned
above about 11000, so OID 41675 would have to belong to a user-created
object.  Look in template1 and see if you see a table with that OID.

            regards, tom lane

David,

On Thu, May 25, 2006 at 02:07:27PM -0700, David Wall wrote:
> >object.  Look in template1 and see if you see a table with that OID.

> Thanks, but I clearly lack that expertise.  How do I find "a table with
> that OID"?

Do a normal select against pg_class. Something like:

select oid, relname from pg_class


Joachim

Rats!  I found nothing....  But just for grins, I ran the reindexdb program a second time, and this time it reported a different OID, higher than the previous.  I know we didn't change the template1 database other than using it when creating new databases, creating users, tablespaces, etc.

My guess is this is somehow related to the reindexdb program.  Do you know if there's any advantage to using the reindexdb program versus running the psql commands REINDEX DATABASE and REINDEX SYSTEM? 

When reading the docs, I'm beginning to wonder if it's even necessary to do the reindexing.  I think it may be a holdover from earlier thinking that doesn't apply anymore.  I believe the idea is that as rows are inserted, updated and deleted, the index can get rather scrambled, and that reindexing often sorts the values and rewrites that data so that the indexes take less space and work faster.  Is that not the case here?

Thanks,
David



Tom Lane wrote:

David Wall <d.wall@computer.org> writes: 

Thanks, but I clearly lack that expertise.  How do I find "a table with 
that OID"?   

Try "select * from pg_class where oid = 41675" and
"select * from pg_attribute where attrelid = 41675"
to see if you find any rows.
		regards, tom lane
 

On Thursday 25 May 2006 19:34, David Wall wrote:
> Rats!  I found nothing....  But just for grins, I ran the reindexdb
> program a second time, and this time it reported a different OID, higher
> than the previous.  I know we didn't change the template1 database other
> than using it when creating new databases, creating users, tablespaces,
> etc.
>
> My guess is this is somehow related to the reindexdb program.  Do you
> know if there's any advantage to using the reindexdb program versus
> running the psql commands REINDEX DATABASE and REINDEX SYSTEM?
>
> When reading the docs, I'm beginning to wonder if it's even necessary to
> do the reindexing.  I think it may be a holdover from earlier thinking
> that doesn't apply anymore.  I believe the idea is that as rows are
> inserted, updated and deleted, the index can get rather scrambled, and
> that reindexing often sorts the values and rewrites that data so that
> the indexes take less space and work faster.  Is that not the case here?
>

It really isn't neccessary. That said, there is *something* going on with your
db, so it might be prudent to figure out what it is.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

> It really isn't neccessary. That said, there is *something* going on with your
> db, so it might be prudent to figure out what it is.
>
Thanks, Robert.  Actually, I noted that each time I ran my backup
script, the OID mentioned in the error was bigger than previous number.

Before I run the reindexdb program, I am running the vacuumlo (from
contrib).  Just by putting a sleep 5 between the two commands, I cannot
reproduce the error.  If I remove the sleep, then the error happens
again.  Clearly, there's something "left over" from vacuumlo that
reindexdb is finding.

Any thoughts on that observation?

Thanks,
David

David Wall <d.wall@computer.org> writes:
> Thanks, Robert.  Actually, I noted that each time I ran my backup
> script, the OID mentioned in the error was bigger than previous number.

That's fairly interesting, but can you provide a self-contained test
case?  The reindexdb script really doesn't do anything except invoke
REINDEX, so I see no way to blame it.

            regards, tom lane