Skip site navigation (1) Skip section navigation (2)

Re: Strange error related to temporary tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Alban Hertroys <alban(at)magproductions(dot)nl>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Strange error related to temporary tables
Date: 2006-08-31 15:52:34
Message-ID: 8375.1157039554@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> In any case, the error message is strange in itself, as if I try to
> create the temporary table when it exists, the error I get in manual
> trial is:
> ERROR:  relation "test_temp_table" already exists
>         ^^^^^^^^
> compared to the error from the OP which I cite here for reference:
> ERROR: type "temp_report" already exists
>        ^^^^

This is not too surprising given the way that heap_create_with_catalog
works --- it happens to be easier to insert the pg_type row before
the pg_class row, so if you have two sessions trying to create the same
table at about the same time, that's where the unique index constraint
will kick in.  The initial check for a duplicate pg_class row doesn't
catch the conflict because the guy who's just slightly ahead won't have
committed yet when the second guy looks.

The question is how is this scenario managing to occur, given that the
tables in question are temp tables?  It seems like this must indicate
two backends trying to use the same pg_temp_NNN schema; but that should
surely be impossible --- it's driven off MyBackendId, and if that's not
unique to a live session then we've got *major* problems.

IIRC we've seen prior reports of similar issues, so I believe there is
something there, but without a test case it's gonna be hard to track down.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Michael NolanDate: 2006-08-31 15:54:07
Subject: Re: Listening on more than one port?
Previous:From: Alban HertroysDate: 2006-08-31 15:50:32
Subject: Re: Strange error related to temporary tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group