Re: Problems with temporary tables created in callable functions

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> 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
> >
> >
> >
>
>