Обсуждение: 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