Re: Problems with temporary tables created in callable functions - Mailing list pgsql-jdbc
| From | Akhil Srinivasan |
|---|---|
| Subject | Re: Problems with temporary tables created in callable functions |
| Date | |
| Msg-id | ad76b9f2050620090523beaa91@mail.gmail.com Whole thread Raw |
| In response to | Re: Problems with temporary tables created in callable functions (David Gagnon <dgagnon@siunik.com>) |
| Responses |
Re: Problems with temporary tables created in callable functions
|
| List | pgsql-jdbc |
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.
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> 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
>
>
>
pgsql-jdbc by date: