Fwd: BUG #3662: Seems that more than one run of a functions causes an error

Hi,

I submitted this (supposed bug) via a web form earlier, but it got back with
this bounce.
Since I was not sure whether this was accepted, so I registered with this
list and am sending this again.

Hope it helps to find the problem.

Robins

---------- Forwarded message ----------
From: pgsql-bugs-owner@postgresql.org <pgsql-bugs-owner@postgresql.org>
Date: Oct 9, 2007 9:20 PM
Subject: Stalled post to pgsql-bugs
To: Robins Tharakan <tharakan@gmail.com>

Your message to pgsql-bugs has been delayed, and requires the approval
of the moderators, for the following reason(s):

The author ("Robins Tharakan" <tharakan@gmail.com>)
  is not a member of any of the restrict_post groups.

If you do not wish the message to be posted, or have other concerns,
please send a message to the list owners at the following address:
  pgsql-bugs-owner@postgresql.org


---------- Forwarded message ----------
From: "Robins Tharakan" <tharakan@gmail.com>
To: pgsql-bugs@postgresql.org
Date: Tue, 9 Oct 2007 15:49:53 GMT
Subject: BUG #3662: Seems that more than one run of a functions causes an
error

The following bug has been logged online:

Bug reference:      3662
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.com
PostgreSQL version: 8.2.5
Operating system:   Windows XP Professional SP2
Description:        Seems that more than one run of a functions causes an
error
Details:

The situation is this. If I create a Type and create a function returning
this type, a simple select query works fine. But the same query run
immediately after, fails with an error.




This is the error I get in PgAdmin:
ERROR: could not open relation with OID 68916
SQL state: XX000
Context: PL/pgSQL function "ranked_set" line 9 at for over select rows




This is the query I am running:
SELECT * FROM ranked_set(10, ('2007-8-31'::date - interval '180
days')::date, '2007-8-31'::date);




This is the SQL for the Type and the function:
DROP FUNCTION ranked_set(integer, date, date);

DROP TYPE ranked_set_type;

CREATE TYPE ranked_set_type AS
   (rank integer,
    scheme_code integer,
    return real);
ALTER TYPE ranked_set_type OWNER TO postgres;

CREATE OR REPLACE FUNCTION ranked_set(given_set_id integer, given_start_date
date, given_end_date date)
  RETURNS SETOF ranked_set_type AS
$BODY$
DECLARE
    rec ranked_set_type;

BEGIN
    CREATE TEMPORARY SEQUENCE s INCREMENT BY 1 START WITH 1;

    FOR rec in
    (
        SELECT nextval('s') as rank, tt.scheme_code, tt.ret
            FROM (

            SELECT
                sets.scheme_code,
                fund_return_in_period(sets.scheme_code, given_start_date,
given_end_date, FALSE) as ret
            FROM sets
            WHERE sets.set_id = given_set_id
            ORDER BY ret DESC
            ) tt
        ) LOOP

        RETURN NEXT rec;

    END LOOP;

    DROP SEQUENCE s;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION ranked_set(integer, date, date) OWNER TO postgres;




I tried restarting PgAdmin (1.8.0 Beta1) and repeating the steps over and
over about 4-5 times but got the exact same output.

What I didn't do as yet is restart the PG server and Vaccuum full the DB,
although the DB was vaccuumed (full) this morning without much activity
thereafter.

Hope this helps, do get back if any special tests are to be performed for
any confirmations.

Robins Tharakan