Re: ERROR: duplicate key violates unique constraint "pg_type_typname_nsp_index"

Tom Lane wrote:

>Michael Guerin <guerin@rentec.com> writes:
>
>
>>     I'm not sure what the cause of this error is, but I figured I
>>should post it.
>>
>>
>
>
>
>>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), ownerid smallint, tag varc
>>har(50), level int4 )"
>>
>>
>
>Hmm.  It looks like the source of the problem is an
>only-partially-deleted temp table left behind by some prior failure.
>Specifically, the rowtype entry for the table is still there in
>pg_type, though its pg_class entry must be gone or you'd have gotten
>a different error message.  This seems pretty odd, since the catalog
>entries should have been deleted in a single transaction.
>
>Can you look back just before you started getting these, and see if
>there is any record of a backend crashing during exit?
>
>It's too bad you already restored from backup, since you've now wiped
>out all the evidence ...
>
>            regards, tom lane
>
>
It's a production database, so I had to get it back online.  I did
however leave the old files around, and restored to a new db install.
I'll startup the old files on another port to poke around.  I believe
the only restart happened after the error, i'll confirm this.

-michael