Re: ERROR: type "temp_gc" already exists
От | Damon Hart |
---|---|
Тема | Re: ERROR: type "temp_gc" already exists |
Дата | |
Msg-id | 43398A81.4020805@sundial.com обсуждение исходный текст |
Ответы |
Re: ERROR: type "temp_gc" already exists
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
Hi all - I came across this thread after intermittently observing very similar error messages last week from a PostgreSQL (8.0.2) server, e.g.: SQL error: = 'type "local_roll" already exists' in line 1984 (only the portion in quotes is generated by the server, the context is from the client application.) I followed up with the suggested action, including testing of the hardware involved, on the original and a backup server without clearing up the issue. It recurs and I am at a loss for what I might do to further diagnose/isolate/fix the problem. There were several identical errors, referencing both tables and indexes, but always objects in a temporary schema and always with a client app which creates and drops many temp tables and indexes. Following Tom Lane's suggestion, a check of pg_depend never reveals the presence of the affected name, using a very simple query like 'SELECT objid, refobjid FROM pg_depend' which should not use the index (confirmed by EXPLAIN) and simply grepping through the output for the target values. After confirming the absence of a pg_depend entry, I ran queries "DROP TYPE pg_temp_NNN.local_roll;" for each pg_temp_NNN schema and similarly for the other affected types. On one schema the DROP TYPE would succeed (not found, as expected, in the rest.) However, the problem would recur with the same or a different name, so the incorrect entries were being regenerated somehow. Trying to get handle on this, I did a dump/restore to initialize the system tables/indexes to a coherent state (I'm not a PG expert, but 'pg_dump <database> | grep pg_depend' fails, so I presume the restore rebuilds the system tables.) From this starting point, the errors still occurred. I ran several filesystem and smartmontools checks of the disks involved and some memory tests for good measure without any indication of a problem. I restored the same database image to a backup server. I did not immediately get the same errors on the backup server (like I said, it's intermittent) but in the log file on a restart of the backup server after < 24 hours usage I see: WARNING: index "pg_depend_depender_index" contains 6195 row versions, but table contains 6192 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_depend_reference_index" contains 6195 row versions, but table contains 6192 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_depend_depender_index" contains 6195 row versions, but table contains 6192 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_depend_reference_index" contains 6195 row versions, but table contains 6192 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_attribute_relid_attnam_index" contains 4604 row versions, but table contains 4594 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_attribute_relid_attnum_index" contains 4604 row versions, but table contains 4594 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_attribute_relid_attnam_index" contains 4604 row versions, but table contains 4594 row versions HINT: Rebuild the index with REINDEX. WARNING: index "pg_attribute_relid_attnum_index" contains 4604 row versions, but table contains 4594 row versions HINT: Rebuild the index with REINDEX. This seems at least tangentially related, based on Tom's expectation of a corrupt pg_depend index. Sorry I haven't been able to diagnose this any more precisely, but I think a hardware explanation is very doubtful. I'd be happy to run tests or provide further details of the application usage, server environment, etc. - I'm just not sure what's relevant to the issue iat hand. I'd be even happier if someone could reasonably state that this didn't threaten the integrity of my stored data . . . regards, Damon Hart >Date: Mon, 12 Sep 2005 15:43:15 -0400 >From: Tom Lane <tgl@sss.pgh.pa.us> >To: Janning Vygen <vygen@gmx.de> >Cc: pgsql-general@postgresql.org >Subject: Re: ERROR: type "temp_gc" already exists >Message-ID: <24056.1126554195@sss.pgh.pa.us> > >Janning Vygen <vygen@gmx.de> writes: > > >>> Am Samstag, 10. September 2005 18:05 schrieb Tom Lane: >> >> >>>>> If there's no pg_depend entry then DROP TYPE should work. Otherwise >>>>> you might have to resort to manually DELETEing the pg_type row. >>> >>> > > > >>> Thanks for your detailed answer. I don't want to do anything wrong. To be >>> sure, i have some more questions: >> >> > > > >>> - There is no entry in pg_depend. Should i just drop the entry from pg_type or >>> should i REINDEX anyway? >> >> > >Well, what did you do to check that there was no entry? If the index is >corrupt and you issued a query that used the index, it might have failed >to find an entry that's actually there in the table (in fact, if we're >assuming the DROP TYPE didn't happen because the system didn't find the >dependency row while dropping the table, this is pretty much exactly >what you'd expect). I'd REINDEX and then check again. > > > >>> - How can things like this happen? Hardware failure? If yes, should i change >>> my harddisk? >> >> > >Insufficient information to say. It wouldn't be a bad idea to run some >disk tests though. > > > >>> [1] It's not clear to me if pg_depend is a "shared system catalog" because the >>> docs say >>> "any of the shared system catalogs (pg_database, >>> pg_group, pg_shadow, or pg_tablespace)" >>> Maybe the iteration is final, maybe it shows only examples) >> >> > >That's meant to be a complete list --- I've updated the documentation to >make this clearer. But you could check for yourself: > select relname from pg_class where relisshared; > > regards, tom lane >
В списке pgsql-general по дате отправления:
Предыдущее
От: "Thomas Radnetter"Дата:
Сообщение: Problem with Cursor in Oracle from Postgresql via ODBC