Re: Problems with temporary tables created in callable functions

Here is the FAQ I was talking about:
http://www.postgresql.org/docs/faqs.FAQ.html#4.19.
Everything should be in execute: The CREATE TABLE, and INSERT too... If
you access the TEMP table without an execute the plan is cached an you
get an exception next time (or when you close/reopen the connection)

In your example.   You still have the  CREATE TEMPORARY TABLE.  I think
you should have error when you restart your application (since the
TEMPORARY TABLE is deleted when the connection is closed).

Using EXECUTE is the only way I found after losing a lot of time trying
to found another way.  For what I know, if you want to use a TEMPORARY
table .. that the only way.

 Let me know if you found something else.

Ciao
/David



Akhil Srinivasan wrote:

> I saw the faq, but its not a simple temporary table so formatting and
> building the create and insert strings are non trivial tasks. I would
> rather not have to do it unless i really need to. And i still get "the
> table already exists error" even with the execute.
>
> I have tried another approach after reading a mailing list posting.
>
>     tempTableExists:=0;
>
> SELECT * INTO rectable FROM PG_TABLES WHERE SCHEMANAME LIKE
> \'pg_temp_%\' and tablename like \'%TEMQUERYPERSONALPPORTFOLIOLIST%\' ;
>
>     IF rectable.tablename like \'%TEMQUERYPERSONALPPORTFOLIOLIST%\' THEN
>         tempTableExists:=1;
>     END IF;
>
>     if tempTableExists = 0 then
>     CREATE TEMPORARY TABLE "TEMQUERYPERSONALPPORTFOLIOLIST"
>     (
>         "PORTFOLIOID" INTEGER,
>         "NAME"    VARCHAR,
>         "ACCOUNTID" INTEGER,
>         "CONTESTID" INTEGER,
>         "CASHAMOUNT" INTEGER,
>         "CURRENTVALUE" INTEGER,
>         "STATUS" VARCHAR
>     )without oids on commit delete rows;
>     end if ;
>
> with a commit being called at the end after retrieving the resultset.
> This seems to be working so far and i have done a couple of tests with
> multiple users and they have gone fine. Does anybody see anything that
> can cause a problem here.
>
> In this i check to see if the table is already created, if so i do a
> pretty much blanket delete. I believe that stored procedure call is
> atomic so multiple calls to the same procedure should be serialized
> which works for me here as there can be only one temporary table.
>
> I might try on commit drop, but i was curious as to how reliable it
> is. Because that should remove any need  for a temporary table check.
> Hopefully it would also become more reliable.
>
> Any ideas or improvements would be welcome.
>
> Thanks
> Akhil Srinivasan
>
> PS: I have another problem buts thats coming next.
>
> On 6/20/05, *David Gagnon* <dgagnon@siunik.com
> <mailto:dgagnon@siunik.com>> wrote:
>
>     Hi,
>
>       Temporary table a tricky in postgresql you must use:
>       EXECUTE ''
>         CREATE TEMP TABLE TMP_IC (
>             ICNUM VARCHAR(20) primary key
>         ) ON COMMIT DROP'';
>
>
>     And for your sql :
>       statement := ''
>           INSERT INTO TMP_IC
>           SELECT ICNUM FROM IC'';
>     EXECUTE STATEMENT;
>
>     The problem is that the SQL planner keeps the plan for deleted temp
>     table.  Using the EXECUTE statement avoid caching in the
>     planner.  This
>     is the official way to use temp table in postgresql.  Look in the
>     FAQ (I
>     think,)  everything is explained there.
>
>     Ciao
>     /David
>
>
>
>
>     Akhil Srinivasan wrote:
>
>     > Hi
>     > I have a an application that uses a lot of stored functions,
>     some of
>     > them which create temporary tables and refcursors that point to the
>     > tables. Something that comes up occasionally are errors like
>     >
>     > This is when we have some code in the function to delete existing
>     > temporary tables.
>     > ERROR: relation with OID xxxxx does not exist.
>     > ERROR: relation "TEMPSTOCKVALUEALERT" already exists
>     >
>     > When we comment that code we get errors like
>     > ERROR: relation "TEMPSTOCKANALYSIS" already exists. This happens
>     every
>     > second call to the function.
>     >
>     > In the jdbc call code we have setAutoCommit to false, And I have
>     tried
>     > a couple of options when preparing the call  like
>     >
>     >         CallableStatement proc = con.prepareCall("{ ? = call
>     > \"queryStockAnalysisSingle\" ( ?,?,?,? ) }"
>     >
>     ,ResultSet.CONCUR_READ_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT);
>     >
>     >         CallableStatement proc = con.prepareCall("{ ? = call
>     > \"queryStockAnalysisSingle\" ( ?,?,?,? ) }"
>     >
>     ,ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT);
>     >
>     > None of them seem to work. Has anybody faced this problem? And
>     > hopefully cone up with a usable fix.
>     > The application has a commons-dbcp maintaining a connection pool to
>     > the database server. The jdbc driver used is the jdbc3. THe database
>     > version is 7.4.x . It has been deployed on a Debian Linux system
>     with
>     > kernel version 2.6.8.
>     >
>     > Thanks
>     > Akhil Srinivasan
>     >
>     >
>     >
>
>