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

Поиск
Список
Период
Сортировка
От Robins Tharakan
Тема BUG #3662: Seems that more than one run of a functions causes an error
Дата
Msg-id 200710091549.l99FnrFK028214@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #3662: Seems that more than one run of a functions causes an error
Re: BUG #3662: Seems that more than one run of a functions causes an error
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "David Bachmann"
Дата:
Сообщение: BUG #3661: Missing equality comparator: string = integer
Следующее
От: "Kevin.Lee"
Дата:
Сообщение: BUG #3663: Installer: Encoding(Server) Select list have not UTF-8 encoding.