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(at)siunik(dot)com
> <mailto:dgagnon(at)siunik(dot)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
> >
> >
> >
>
>