Thread: Errors with temporary tables

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

Alexander Presber <aljoscha@weisshuhn.de> writes:
> 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

The first of these looks like the same problem we've seen reported
before of a table's rowtype not getting dropped when the table is
dropped.  The second one is new though.  Can you reproduce it?
If so, a debugger backtrace from the errfinish() call would be very
helpful.

            regards, tom lane

Jens Wendelmuth <jw@weisshuhn.de> writes:
> I'm a newbie with PG and i do not understand the meaning of "a table's rowtype not getting dropped when the table is
> dropped."

Every table has an associated composite type of the same name, which
represents the type of each row of the table.  This type entry should
go away automatically if the table is dropped.  We've seen a few trouble
reports that look like that mechanism failed for some reason, but no one
yet has any clue why.

> Is this a failure of us or PG(8.1.0)?
> Is there a way to solve/workaround this error?

It's certainly not your fault, but with so little information about
what's happening or what causes it, it's hard to say how to fix it
or avoid it.  If you can come up with a test case that lets other
people reproduce the problem, I'm sure we could fix it in short order...

            regards, tom lane

Hi Tom,

Tom Lane wrote:

>The first of these looks like the same problem we've seen reported
>before of a table's rowtype not getting dropped when the table is
>dropped.
>
I'm a newbie with PG and i do not understand the meaning of "a table's rowtype not getting dropped when the table is
dropped."

Is this a failure of us or PG(8.1.0)?
Is there a way to solve/workaround this error?

If this error occurs, what happens to the SQL query? Is it completely processed or aborted?


>The second one is new though.  Can you reproduce it?
>If so, a debugger backtrace from the errfinish() call would be very
>helpful.
>
Currently we're not able to directly reproduce this strange error. If so it will be posted.


Thanks in advance and best regards
Jens