Re: relation 12345 is still open

Hello Novices,
hello Tom,

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Johannes Bruegmann <johannes@jottbee.org> writes:
>> Function _clean [6] wraps function _drop_part [4], and tries to drop
>> some parts not needed any longer. Dropping a part using _drop_part [4]
>> works. However, dropping some parts with function _clean [6] fails
>> with the following error message:
>> NOTICE:  relation 50435 is still open
>> What does it mean "relation 50435 is still open"?
>
> What this probably means is that there is an active query somewhere
> within your session that is referencing that table --- such as an
> unclosed cursor or unfinished plpgsql FOR-in-SELECT loop.

Thank you very much for your prompt answer.

> I don't see any such thing in the functions you showed us, but look
> around in the rest of your application.

I couldn't see anything either, but it has to be in the code i
showed. Because if I implement it the way like [1], everything is
working fine.

Any ideas why this is the case?

Regards,
Johannes

---------------------------------------------------------------------------
[1]:
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_clean(keep_from DATE, keep_to DATE)
RETURNS SETOF DATE STRICT AS $$
DECLARE
    r RECORD;
BEGIN

    IF keep_to < keep_from
    THEN
        RETURN;
    END IF;

    EXECUTE '
    CREATE TEMP TABLE tmp_historische_zeitstempel_clean
    AS SELECT DISTINCT referenz
    FROM historische_zeitstempel
    WHERE referenz
        NOT BETWEEN ' || quote_literal(keep_from) || '
                AND ' || quote_literal(keep_to) || ';';

    FOR r IN EXECUTE '
    SELECT  t.referenz AS referenz,
        historische_zeitstempel_drop_part(t.referenz) AS success
    FROM tmp_historische_zeitstempel_clean t(referenz)'
    LOOP

        IF r.success
        THEN
            RETURN NEXT r.referenz;
        END IF;
    END LOOP;

    EXECUTE 'DROP TABLE tmp_historische_zeitstempel_clean;';

    RETURN;
END;
$$ LANGUAGE plpgsql;