Problem with transaction in functions and tempory tables

Problem with transaction in functions and tempory tables

am 22.07.2004 18:49:32 von gcastillo

This is a multi-part message in MIME format.

--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT

Hello,

I'm using PostgreSQL 7.4

I have a function wich use temporary tables. I read about temporary tables
and they exists during the session.
But i have to call this function many times in the same sesion with
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of
the funcition.

To avoid this, I used this sintax after de create table statement "ON COMMIT
DROP" which destroy the table in the next commit.

for example, If i run this script many times in the same session there
weren't problems:
begin;
create temporary table test(x integer) ON COMMIT DROP;
INSERT INTO test values(1);
select * from test;
commit;

Then I tried to use this in function:

CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;

INSERT INTO test values (1);

--RETORNA LOS RESULTADOS
FOR res IN SELECT x FROM test LOOP
RETURN NEXT res;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER;

and then I executed the function this way:
BEGIN;
SELECT * FROM f_test() AS R(x INTEGER);
COMMIT;

but in the second execution, it falis with an error wich said that doesn't
exist the relation with OID XXXX... I supose it is because the table doesn't
exist because in the second execution the function couldn't create the table
or it is using an old reference of the dropped table.

I think if I put the begin and the commit inside the function, it will work.

I tried this way, but it doesn't compile:
CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
BEGIN;
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;
.....
I tried too with START, but without success.

I'd appeciate some help.

Tanks,
Gerardo.


--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT







class=531441916-22072004>Hello,

 

face=Arial>I'm using PostgreSQL 7.4

face=Arial> 

face=Arial>I have a function wich use temporary tables. I read about temporary
tables and they exists during the session.

face=Arial>But i have to call this function many times in the same sesion with
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of the
funcition.

class=531441916-22072004> class=531441916-22072004> 

face=Arial>To avoid this, I used this sintax after de create table statement "ON
COMMIT DROP" which destroy the table in the next
commit.

face=Arial> 

face=Arial>for example, If i run this script many times in the same session
there weren't problems:


begin;

create temporary
table test(x  integer) ON COMMIT
DROP;

INSERT INTO  class=531441916-22072004>test values(1);

select * from class=531441916-22072004> test;

face=Arial>commit;

face=Arial> 

face=Arial>Then I tried to use this in function:

face=Arial> 

class=187244115-21072004>CREATE OR REPLACE FUNCTION "public"."f_ class=531441916-22072004>test" () RETURNS SETOF "pg_catalog"."record"
AS'
BEGIN


    CREATE
TEMPORARY TABLE test(
class=187244115-21072004> x integer
) ON COMMIT DROP;

  

    INSERT
INTO test class=772173421-21072004> values (1);

 

   
--RETORNA LOS RESULTADOS
    FOR res IN SELECT x 
FROM  class=531441916-22072004>test LOOP
       
RETURN NEXT res;
    END
LOOP;
    RETURN;
END;
'LANGUAGE 'plpgsql' IMMUTABLE
CALLED ON NULL INPUT SECURITY DEFINER;

 

face=Arial>and then I executed the function this
way:

face=Arial>BEGIN;

face=Arial>SELECT * FROM f_test() AS R(x INTEGER);

face=Arial>COMMIT;

face=Arial> 

face=Arial>but in the second execution, it falis with an error wich said that
doesn't exist the relation with OID XXXX... I supose it is because the table
doesn't exist because in the second execution the function couldn't create the
table or it is using an old reference of the dropped
table.

face=Arial> 

face=Arial>I think if I put the begin and the commit inside the function, it
will work.

face=Arial> 

face=Arial>I tried this way, but it doesn't compile:

class=772173421-21072004>CREATE
OR REPLACE FUNCTION "public"."f_test" ()
RETURNS SETOF "pg_catalog"."record" AS'
class=187244115-21072004>BEGIN


class=531441916-22072004>   
BEGIN;


    CREATE
TEMPORARY TABLE test(
class=187244115-21072004> x integer
) ON COMMIT DROP;

face=Arial>    .....

face=Arial>I tried too with START, but without
success.

face=Arial> 

face=Arial>I'd appeciate some help.

face=Arial> 

face=Arial>Tanks,

face=Arial>Gerardo.

class=772173421-21072004> class=772173421-21072004> 


--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)--

Re: Problem with transaction in functions and tempory tables

am 22.07.2004 19:27:00 von sszabo

On Thu, 22 Jul 2004, Gerardo Castillo wrote:

> Hello,
>
> I'm using PostgreSQL 7.4
>
> I have a function wich use temporary tables. I read about temporary tables
> and they exists during the session.
> But i have to call this function many times in the same sesion with
> diferents parameters and expecting different results. So, there is a problem
> because the temporary table already exists during the second execution of
> the funcition.

If you're going to use temporary tables in a plpgsql function, you really
need to use the table only through EXECUTE commands so that the plans
don't get saved.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings