Re: Problems with temporary tables created in callable functions

Hi
Tried to fight the EXECUTE headache for a long time, But nothing worked, so
now changed all the stored procedures to build an insert query string and
fire it through execute.

Thanks for the help, should have listened sooner.

Akhil Srinivasan

On 6/21/05, David Gagnon <dgagnon(at)siunik(dot)com> wrote:
>
> 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
> > >
> > >
> > >
> >
> >
>
>