Errors with temporary tables

From: Alexander Presber <aljoscha(at)weisshuhn(dot)de>
To: pgsql-general(at)postgresql(dot)org
Cc: Bernhard Weißhuhn <bkw(at)weisshuhn(dot)de>, Frank Wittig <fw(at)weisshuhn(dot)de>, jw(at)weisshuhn(dot)de
Subject: Errors with temporary tables
Date: 2005-11-24 11:20:04
Message-ID: A668239E-CD7B-43CD-9794-042F8F39F135@weisshuhn.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everyone,

We encounter the following two strange errormessages when working
with temporary tables.

1) ERROR: duplicate key violates unique constraint
"pg_type_typname_nsp_index"
2) ERROR: tuple concurrently updated

This is how:
To cache large resultsets of searches we select them into temporary
tables "searches.tmp_*" and use an additional table "searches.tables"
to keep track of the tables.

While selecting into the temporary table we get the first error:

BEGIN TRANSACTION;
SELECT DISTINCT
ex.fk_product,ex.title_soup
INTO
searches.tmp_c7470136936abaa8322358ad4905e5a3
FROM pdb.expose ex JOIN pdb.main t1 ON (ex.fk_product = t1.id)
WHERE (upper(t1.isbn) like upper('3406538967'||'%')) ORDER BY
ex.title_soup ;
GRANT ALL ON searches.tmp_c7470136936abaa8322358ad4905e5a3 TO smg_own;
INSERT INTO searches.tables (name,query) VALUES
('tmp_c7470136936abaa8322358ad4905e5a3','isbn=''3406538967''/
ex.title_soup ');
COMMIT;
---
DB Error: constraint violation
ERROR: duplicate key violates unique constraint
"pg_type_typname_nsp_index"
---

What kind of index would that be? How do we violate its uniqueness?

When a cachetable has a certain age, we drop it and the corresponding
entry in the tracker table, this is where we get the second error:

BEGIN TRANSACTION;
DROP TABLE searches.tmp_c7470136936abaa8322358ad4905e5a3;
DELETE FROM searches.tables WHERE
name='tmp_c7470136936abaa8322358ad4905e5a3';
COMMIT;
---
DB Error: unknown error
ERROR: tuple concurrently updated
---

What does that mean? How can we avoid this error?
In reality we encountered these two errors directly one after the
other and in reversed order, so that the second could be the reason
for the first.
Thanks for any hints

Sincerely yours
Alexander Presber

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2005-11-24 11:24:50 Re: Private email requests
Previous Message A.j. Langereis 2005-11-24 10:47:53 selecting a attribute from a function