Re: duplicate key violates unique contraint on pg_type_typname_nsp_index

Andrea Suisani <sickpig(at)opinioni(dot)net> writes:
> I'm experiencing something weird. here the session's log
> that involves the prob I mentioned

> 2010-03-08 12:59:41 CET p(at)c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
> 2010-03-08 12:59:41 CET p(at)c[3189]STATEMENT: create table check_unfitted_strata_col as select * from sampling.sample_gen_design limit 0

> those sql commands could be executed in separate
> postgres back-ends almost simultaneously (isolation level
> is read committed), but I always thought wrapping the CREATE TABLE
> command inside a transaction avoid any misbehavior (a part from the
> fact that the second process will wait for the first to commit or rollback)

Well, it does avoid any "misbehavior", it's just not producing exactly
the error message you expect. If you have two sessions trying to create
the same table name at exactly the same time, this is a likely result.
They both have to create pg_type entries for the table's row type,
so they can collide on that as well as on the table name itself.

There have been some unexplained reports of similar error messages in
cases where it didn't seem that any concurrent creation could be going
on. But I don't see any reason to think there's anything very
mysterious here, if you do have sessions trying to create the same table
concurrently.

regards, tom lane