Re: CREATE TEMP TABLE

Juris Krumins wrote:
> Hi everybody. I have a problem with my PostgreSQL DBMS.
> In serverl log file I periodicly have errors like:
>
> 2004-03-24 11:43:18 ERROR: Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR: Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR: Relation 'tmp_table1' already exists
> 2004-03-24 11:43:18 ERROR: Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR: Relation "tmp_table1" does not exist
>
> I figure out that this is connected with query I use in one of the
> function:
>
> CREATE TEMP TABLE tmp_table1 AS SELECT bla-bla-bla.
>
> The problem is that when I had no so many users I have rigth now
> everything was ok, but now I have quite more users, so this problem
> appeared. Function , which use this query, is used all over my web site,
> which is base on PostgreSQL.
>
> So anybody have any idea how to correct this or maybe I have to change my
> query. Any help would be appreciated.
>
> I'm using PostgreSQL 7.3.4

I'm not sure, but I suspect your web application is doing connection pooling
to connect to Postgres. If that is the case, it's possible two different
threads of your application are trying to work with "tmp_table1" at the
same time over the same connection, which would cause the errors you
describe.

If this is the case, there are a number of choices:
1) Don't use a temporary table
2) Don't use connection pooling
3) Make you application choose a unique name for the temporary table, so
there's no chance of clash. Instead of always using tmp_table1, append
a unique number to the name each time you create the temp table.

Hope this helps.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com