Re: Error from index "pg_type_typname_index"????

fabrizio(dot)ermini(at)sysdat(dot)it writes:
> I've a postgresql 7.0.2 used as a backend for a website. Randomly,
> and not very frequently, an error pops up saying that the following
> problem has happened:
> ERROR: Cannot insert a duplicate key into unique index
> pg_type_typname_index
> The query causing it it's an innocent query that duplicates a table
> in a temporary one, i.e.
> "select * into forum_clone from forums"

I think you're probably trying to do two of these at the same time.

Table creation also creates an entry in pg_type for the table's row
type, and IIRC that happens before the pg_class entry is made.
Example:

session 1:

regression=# begin;
BEGIN
regression=# create table foot (f1 int);
CREATE

session 2:

regression=# create table foot (f1 int);
<< blocks waiting to see if session 1 commits or not >>

session 1 again:

regression=# end;
COMMIT

now session 2 reports:

ERROR: Cannot insert a duplicate key into unique index pg_type_typname_index

Session 2's check to see if the table name already existed didn't find a
conflict because session 1 hadn't committed yet; it was only the first
insert into a unique index that caused a synchronization point.

I'll take a look to see if the order of operations can't be reversed so
that you get a more understandable complaint about a unique index on
pg_class in this case. However, the real answer for you is to be using
a TEMP table if you are going to have multiple clients creating
temporary tables at about the same time. That avoids the name conflict.

regards, tom lane