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