From gcastillo@ice.co.cr Thu Jul 22 16:46:21 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C734DD1B38B for ; Thu, 22 Jul 2004 13:46:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40179-02 for ; Thu, 22 Jul 2004 16:46:13 +0000 (GMT) Received: from icecom.ice.co.cr (icecom.ice.co.cr [200.91.75.2]) by svr1.postgresql.org (Postfix) with ESMTP id 48EC9D1B377 for ; Thu, 22 Jul 2004 13:46:09 -0300 (ADT) Received: from gcastillo (icecom.ice.co.cr [200.91.75.2]) by ice.co.cr (Servidor ESMTP-ICE) with ESMTPA id <0I19005KTIKF3S@ice.co.cr> for pgsql-sql@postgresql.org; Thu, 22 Jul 2004 10:45:53 -0600 (CST) Date: Thu, 22 Jul 2004 10:49:32 -0600 From: Gerardo Castillo Subject: Problem with transaction in functions and tempory tables To: pgsql-sql@postgresql.org Message-id: <0I19005KZIKF3S@ice.co.cr> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Content-type: multipart/alternative; boundary="Boundary_(ID_bvUHanHEVT60/w6NsSufQg)" Importance: High X-Priority: 1 (Highest) X-MSMail-priority: High Thread-index: AcRwC9ycD29I59mRT7S+1avn8Ofmkg== X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=HTML_60_70, HTML_MESSAGE, UPPERCASE_25_50, X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: * X-Archive-Number: 200407/183 X-Sequence-Number: 18435 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

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)-- From sszabo@megazone.bigpanda.com Thu Jul 22 17:27:13 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8531AD1B1C8 for ; Thu, 22 Jul 2004 14:27:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55164-04 for ; Thu, 22 Jul 2004 17:27:05 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 15465D1B199 for ; Thu, 22 Jul 2004 14:27:00 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AF5CB35647; Thu, 22 Jul 2004 10:27:00 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id AD6E735563; Thu, 22 Jul 2004 10:27:00 -0700 (PDT) Date: Thu, 22 Jul 2004 10:27:00 -0700 (PDT) From: Stephan Szabo To: Gerardo Castillo Cc: pgsql-sql@postgresql.org Subject: Re: Problem with transaction in functions and tempory tables In-Reply-To: <0I19005KZIKF3S@ice.co.cr> Message-ID: <20040722102557.V4811@megazone.bigpanda.com> References: <0I19005KZIKF3S@ice.co.cr> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/186 X-Sequence-Number: 18438 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.