Thread: 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
(repost to newsgroup since the other went directly to Mr. Lane's email address)
Is it possible that the 'reindexdb' program (not the command within psql, but the standalone program) creates some temp tables that might reflect this oid?
Thanks,
David
I think you've done something to template1, even though you say youThanks, but I clearly lack that expertise. How do I find "a table with that OID"? The \dl lists large objects, but there's no oid with that number in my regular db and template1 has not oids at all using that command. I also checked the database area with all the files that have such numbers, and there's no entry with the matching number either, though there are some that are close.
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.
Is it possible that the 'reindexdb' program (not the command within psql, but the standalone program) creates some temp tables that might reflect this oid?
Thanks,
David
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:
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