Обсуждение: DB Crash, recoverable?

Hi All,

   Our database just reset and now I'm getting Errors in the log and I
would like to know if this is recoverable.  Here's the segment of the
log right before and after it reset.

LOG:  unexpected EOF on client connection
ERROR:  type "t" already exists
ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
CONTEXT:  SQL statement "create temp table tmp_children ( uniqid bigint, memberid bigint, membertype varchar(50),
owneridsmallint, tag varchar(50), level int4 )" 
    PL/pgSQL function "fngetcompositeids2" line 14 at SQL statement
ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
ERROR:  cache lookup failed for type 2449707570
FATAL:  cache lookup failed for type 2449707570
LOG:  server process (PID 5834) exited with exit code 1
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because
anotherserver process exited abnormally and possibly corrupted shared memory. 


...
ERROR:  cache lookup failed for type 2449707570
ERROR:  cache lookup failed for type 2449707570
ERROR:  cache lookup failed for type 2449707570
ERROR:  cache lookup failed for type 2449707570....


This has happened in the past, however the only way I know to fix it is
a dump and restore.

=> select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2
(1 row)

Thanks
Michael

Michael Guerin <guerin@rentec.com> writes:
>    Our database just reset and now I'm getting Errors in the log and I
> would like to know if this is recoverable.  Here's the segment of the
> log right before and after it reset.

> ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
> ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
> ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
> CONTEXT:  SQL statement "create temp table tmp_children ( uniqid bigint, memberid bigint, membertype varchar(50),
owneridsmallint, tag varchar(50), level int4 )" 
>     PL/pgSQL function "fngetcompositeids2" line 14 at SQL statement

Hmm, looks like a previous incarnation of this table was not completely
deleted.  Does it help if you REINDEX pg_type?

> => select version();
>                                    version
> -----------------------------------------------------------------------------
>  PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2

You really shouldn't be still using such an old beta version.  There are
a heck of a lot of bug fixes since beta5 ...

            regards, tom lane

ERROR: duplicate key violates unique constraint "pg_type_typname_nsp_index"

>>ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
>>ERROR:  duplicate key violates unique constraint "pg_type_typname_nsp_index"
>>CONTEXT:  SQL statement "create temp table tmp_children ( uniqid bigint, memberid bigint, membertype varchar(50),
owneridsmallint, tag varchar(50), level int4 )" 
>>    PL/pgSQL function "fngetcompositeids2" line 14 at SQL statement
>>
>>
>
>Hmm, looks like a previous incarnation of this table was not completely
>deleted.  Does it help if you REINDEX pg_type?
>
>
>
I will try.  The last time this happened I re-indexed all the system &
user tables, clustered the users tables as well as re-created all the
user defined types & functions.  These steps didn't resolve the problem.

I tried poking around the system tables, but I didn't see anything wrong.

>>=> select version();
>>                                   version
>>-----------------------------------------------------------------------------
>> PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2
>>
>>
>
>You really shouldn't be still using such an old beta version.  There are
>a heck of a lot of bug fixes since beta5 ...
>
>
>
Yes, looks like I'll get to upgrade today when I do the dump and restore
;)

>            regards, tom lane
>
>

Michael Guerin <guerin@rentec.com> writes:
>> Does it help if you REINDEX pg_type?
>>
> I will try.  The last time this happened I re-indexed all the system &
> user tables, clustered the users tables as well as re-created all the
> user defined types & functions.  These steps didn't resolve the problem.

In that case REINDEX probably won't help.  You could manually delete the
conflicting pg_type row I suppose.

            regards, tom lane

Tom Lane wrote:

>Michael Guerin <guerin@rentec.com> writes:
>
>
>>>Does it help if you REINDEX pg_type?
>>>
>>>
>>>
>>I will try.  The last time this happened I re-indexed all the system &
>>user tables, clustered the users tables as well as re-created all the
>>user defined types & functions.  These steps didn't resolve the problem.
>>
>>
>
>In that case REINDEX probably won't help.  You could manually delete the
>conflicting pg_type row I suppose.
>
>            regards, tom lane
>
>
I can't see this row, could I select the contents of pg_type into a
table, truncate pg_type and load the table back up?

Thanks for you help.
Michael

Michael Guerin <guerin@rentec.com> writes:
> I can't see this row, could I select the contents of pg_type into a
> table, truncate pg_type and load the table back up?

No --- truncating pg_type is a good way to render your database
nonfunctional ...

            regards, tom lane