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;