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

Kragen Sitaker <kragen+pgsql(at)airwave(dot)com> writes:
> We'll run the experiment again. Should we try 7.3.3 too?

No, I don't think 7.3.3 is likely to behave differently from 7.3.4
as far as this goes. What would actually be interesting is whether
you can make 7.4 fail.

> Well, it's possible the daemon could have gotten killed while it was
> inside the transaction, followed shortly by a shutdown of postgres ---
> a dozen times or more --- and during development, we frequently kill
> the daemon so that it will restart with new code.

But you're seeing these errors in production, on a machine where you're
not doing that, no? In any case there is code in place to clean out
a temp schema of any pre-existing junk when a new backend starts to use
it ... perhaps there's a bug in that, but that code was not changed
since 7.3.2 ...

Another question: are you fairly confident that if the same bug had been
in 7.3.2, you would have found it? Were there any changes in your usage
patterns around the time you adopted 7.3.4?

> For our application, we shut down and restart Postgres every night
> because it seems to make VACUUM FULL work better.

[ itch... ] Let's not discuss the wisdom of that just now, but ...

> I wonder why those old namespaces are left around?

They're supposed to be; there's no point in deleting the pg_namespace
entry only to recreate it the next time someone needs it. The real
question is whether you see any tables belonging to those namespaces.
The count(*) query on pg_class looked like a fine way to watch that.

> BTW, we're using the 7.3.4 PGDG RPMs with an extra patch to add
> pg_autovacuum.

If you're not planning to go to 7.4 soon, you might want to think about
an update to 7.3.5, just on general principles.

regards, tom lane