Thread: Error from index "pg_type_typname_index"????
Hi all... 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" That of course doesn't cause any problem 99% of the time. The fact that it happens doesn't seem to be related to load, neither vacuumize the db seems to change anything. Now my question is: anybody has a hint on what mey be happening in that darn 1%??? TIA, Ciao! /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ 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
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