Database corruption with duplicate tables.
I have found that I have a database problem after receiving the
following error from pg_dump:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: more than one row returned
by a subquery used as an expression
pg_dump: The command was: SELECT tableoid, oid, typname, typnamespace,
(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = typowner) as
rolname, typinput::oid as typinput, typoutput::oid as typoutput,
typelem, typrelid, CASE WHEN typrelid = 0 THEN ' '::"char" ELSE
(SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind,
typtype, typisdefined, typname[0] = '_' AND typelem != 0 AND (SELECT
typarray FROM pg_type te WHERE oid = pg_type.typelem) = oid AS isarray
FROM pg_type
Upon investigation I found that I have a table that is in the database twice
db=> select oid, relname from pg_class where oid IN (26770910,
26770918, 26770919);
oid | relname
----------+---------------------------------------
26770910 | availcpedata_20100410
26770918 | availcpedata_20100410_date_index
26770919 | availcpedata_20100410_pollgrpid_index
26770910 | availcpedata_20100410
(4 rows)
I was going to recreate the database by running "pg_dump -n public" to
get around the duplicate table, but I get the same pg_dump error
message.
Can anyone suggest a strategy for removing the table? I don't want to
start randomly deleting stuff from the catalogs.
Thanks,
Woody
--
iGLASS Networks
www.iglass.net
George Woodring wrote:
I have found that I have a database problem after receiving the
following error from pg_dump:
Lack of vacuuming, most likely. What version is this? Did you read
previous threads about this problem on the archives?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
George Woodring <george.woodring@iglass.net> writes:
Upon investigation I found that I have a table that is in the database twice
db=> select oid, relname from pg_class where oid IN (26770910,
26770918, 26770919);
oid | relname
----------+---------------------------------------
26770910 | availcpedata_20100410
26770918 | availcpedata_20100410_date_index
26770919 | availcpedata_20100410_pollgrpid_index
26770910 | availcpedata_20100410
(4 rows)
It's not immediately clear whether that's really two instances of the
row for availcpedata_20100410, or a false hit due to index corruption.
If you include ctid in the query, do the rows have distinct ctids?
If not, reindexing pg_class should fix it.
Can anyone suggest a strategy for removing the table? I don't want to
start randomly deleting stuff from the catalogs.
If there are two, manually deleting one is the only way to fix it. Use
the ctid to make sure you remove only one ...
regards, tom lane
The version is 8.3.3, and I use autovacuum for the routine maintenance.
The ctid's are distinct
grande=# select oid, ctid, relname from pg_class where oid IN
(26770910, 26770918, 26770919, 26770920);
oid | ctid | relname
----------+---------+---------------------------------------
26770910 | (36,52) | availcpedata_20100410
26770918 | (36,42) | availcpedata_20100410_date_index
26770919 | (36,45) | availcpedata_20100410_pollgrpid_index
26770910 | (37,19) | availcpedata_20100410
(4 rows)
I will try deleting the one with (37,19) manually in the morning.
Thanks for the suggestion.
Woody
On Mon, Apr 19, 2010 at 1:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
George Woodring <george.woodring@iglass.net> writes:
Upon investigation I found that I have a table that is in the database twice
db=> select oid, relname from pg_class where oid IN (26770910,
26770918, 26770919);
oid | relname
----------+---------------------------------------
26770910 | availcpedata_20100410
26770918 | availcpedata_20100410_date_index
26770919 | availcpedata_20100410_pollgrpid_index
26770910 | availcpedata_20100410
(4 rows)It's not immediately clear whether that's really two instances of the
row for availcpedata_20100410, or a false hit due to index corruption.
If you include ctid in the query, do the rows have distinct ctids?
If not, reindexing pg_class should fix it.Can anyone suggest a strategy for removing the table? I don't want to
start randomly deleting stuff from the catalogs.If there are two, manually deleting one is the only way to fix it. Use
the ctid to make sure you remove only one ...regards, tom lane
--
iGLASS Networks
www.iglass.net