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@xxxxxxxxxxxxx>
To: Janning Vygen <vygen@xxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: ERROR: type "temp_gc" already exists
Message-ID: <24056.1126554195@xxxxxxxxxxxxx>
Janning Vygen <vygen@xxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
