Re: Problems with temporary tables created in callable functions

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