Errors with temporary tables
От | Alexander Presber |
---|---|
Тема | Errors with temporary tables |
Дата | |
Msg-id | A668239E-CD7B-43CD-9794-042F8F39F135@weisshuhn.de обсуждение исходный текст |
Ответы |
Re: Errors with temporary tables
|
Список | 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
В списке pgsql-general по дате отправления: