[GENERAL] 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 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reindexdb program error under PG 8.1.3
David Wall [EMAIL PROTECTED] 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 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] reindexdb program error under PG 8.1.3
(repost to newsgroup since the other went directly to Mr. Lane's email address) 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. Thanks, 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. 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
Re: [GENERAL] reindexdb program error under PG 8.1.3
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexdb program error under PG 8.1.3
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 [EMAIL PROTECTED] 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
Re: [GENERAL] reindexdb program error under PG 8.1.3
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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] reindexdb program error under PG 8.1.3
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reindexdb program error under PG 8.1.3
David Wall [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
