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
