Error from index "pg_type_typname_index"????

fabrizio.ermini@sysdat.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

On 12 Feb 2001, at 10:10, Tom Lane wrote:

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.

And you do think right. (And this should not came as a surprise, I
would add :-)).
I've ascertained it doing a little stress-testing, and simply rethinking
on the fact that I was doing a dumb thing...

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.

Nope. This is the first thing I've tried after I've realized what was
happening, but it does not work in a web environment, at least in a
PHP based like mine; I think it scales down to PHP ways of
optimizing connection pool (which, in effect, have given me some
worry over time): if use a TEMP table and try to stress test the
page (i.e. "hit furiosly F5 cycling to several explorer windows with
the mouse" :-)) i got many errors complaining things such "table
doesn't exist" or similar. Evidently the various TEMP tables of the
various pages where mismatching, since they have a lifetime based
on the concept of a "session" that's not 1:1 with the lifetime of a
web page.

I resorted to handle the creation of the various tables at application
level, creating temp tablenames with uniqueid() function. A little
overhead but it works well.

Summarizing all this thoughts, the moral is that it's not been PG's
fault (unless for a less-than-clear error message, but that's a venial
sin :-)), that I should think more before screaming wolf, and that I
really should study better the way PHP handles PG connection...
there's some "hidden magic" in there that doesn't convince me.

Thanks for you attention, as ever, and
Ciao

Fabrizio

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini Alternate E-mail:
C.so Umberto, 7 faermini@tin.it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini@sms.tin.it