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(at)gmail(dot)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

"Robins Tharakan" <tharakan(at)gmail(dot)com> writes:
> BEGIN
> CREATE TEMPORARY SEQUENCE s INCREMENT BY 1 START WITH 1;
> FOR rec in
> SELECT nextval('s') as rank, tt.scheme_code, tt.ret
> ...
> DROP SEQUENCE s;
> END;

Sorry, that's not going to work, for fundamentally the same reason that
references in this style to temp tables don't work --- the OID of the
sequence gets embedded into the nextval() call on first use of the
function. Consider creating the temp sequence just once per session
and resetting it on subsequent uses; or use EXECUTE to process that
SELECT. Or maybe you could dispense with the sequence altogether ---
a local-variable counter inside the function would be a vastly
lighter-weight solution anyway.

regards, tom lane

Oh, there's another solution I forgot to mention: you could write the
nextval call as
nextval('s'::text)
which would force a runtime lookup of 's' on each call. On the whole
though, numbering the rows yourself with a local counter is going to
run a lot faster.

regards, tom lane