Re: relation 12345 is still open

Hello Novices,
hello Tom,

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Johannes Bruegmann <johannes(at)jottbee(dot)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;