Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TEMP TABLE

CREATE TEMP TABLE

From
Juris Krumins
Date:
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

Thanks a lot.

Re: CREATE TEMP TABLE

From
Bruce Momjian
Date:
See the FAQ entry about temp tables in function.  Use EXECUTE.

---------------------------------------------------------------------------

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
>
> Thanks a lot.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: CREATE TEMP TABLE

From
Bill Moran
Date:
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