Re: ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index

Kragen Sitaker <kragen+pgsql(at)airwave(dot)com> writes:
> ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index
> We've been getting this error in our application every once in a while
> --- typically once an hour to once a day, although it varies over time.

This seems to me that it must indicate a collision on name+schema of the
temp table. Now that ought to be impossible :-( --- you should get
errors earlier than this if you were actually creating a duplicately
named temp table, and the design for selecting nonconflicting temp
schemas seems pretty bulletproof to me too.

> We started seeing this error on November 22, three days after we migrated
> from Postgres 7.2.3 and 7.3.2 to 7.3.4. We still see the error with
> 7.3.5, but not with 7.4.0.

Hmm. I'm not aware of any 7.4 bug fix that would affect such a thing,
so I wouldn't want to bet that 7.4 has really solved the issue.

Digging in the 7.3.2-to-7.3.4 change logs, I see one potentially
relevant change:

2003-02-06 20:33 tgl

* src/: backend/catalog/dependency.c, backend/catalog/namespace.c,
include/catalog/dependency.h (REL7_3_STABLE): Revise mechanism for
getting rid of temp tables at backend shutdown. Instead of
grovelling through pg_class to find them, make use of the handy
dandy dependency mechanism: just delete everything that depends on
our temp schema. Unlike the pg_class scan, the dependency
mechanism is smart enough to delete things in an order that doesn't
fall foul of any dependency restrictions. Fixes problem reported
by David Heggie: a temp table with a serial column may cause a
backend FATAL exit at shutdown time, if it chances to try to delete
the temp sequence first.

Now this change also exists in 7.4, but perhaps it is malfunctioning in
7.3.*. Or maybe you just haven't stressed the 7.4 installation enough
to reproduce the problem there --- what do you think are the odds of
that?

Given that you're explicitly dropping the temp table before exit, it's
not clear how a problem in this code could cause you grief anyway.
But it's the only starting point I can see. You might try adding some
monitoring code to see if you can detect temp tables being left around
by exited backends.

> Creating and destroying a temporary table with a lot of attributes every
> second is causing other problems, too; the indices on the pg_attribute
> table grow to hundreds of megabytes in size,

Right. 7.4 should fix that though.

> and for some reason,
> reindexing our system tables corrupts our database.

That seems suspicious as well. What happens exactly? How did you get
out of it??

> Do you have any suggestions for
> how to diagnose this unpredictable performance?

I think you have more urgent things to worry about. Like finding why it
doesn't work reliably.

regards, tom lane