Re: Error from index "pg_type_typname_index"???? - Mailing list pgsql-general

From fabrizio.ermini@sysdat.it
Subject Re: Error from index "pg_type_typname_index"????
Date
Msg-id 3A88156B.1489.1B75F3C@localhost
Whole thread Raw
In response to Re: Error from index "pg_type_typname_index"????  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Fred Yankowski
Date:
Subject: Re: startup Postgres on NT
Next
From: Tom Lane
Date:
Subject: Re: strange query results