relation 12345 is still open

Hello Novices,

i wrote a function "historische_zeitstempel_berechnen(date) returns
setof date..." which computes 20 dates corresponding to the date of
the argument:

olsim@traffic_nrw_0_3_0=# select current_date as referenz, pendant
from historische_zeitstempel_berechnen(current_date) h(pendant);
  referenz  |  pendant
------------+------------
 2007-02-08 | 2007-02-01
...
 2007-02-08 | 2006-08-17
(20 rows)

Since the computation takes a while, and the computed result is needed
quite often during a day (using that day as argument), I want to store
the result of one computation in a part of a table using table
partitioning. The parent table looks like that (the values of column
'pendant' contain the computed days which correspond to each value of
column 'referenz'):

CREATE TABLE historische_zeitstempel (
    referenz DATE,
    pendant DATE
);

Now I wrote some useful functions _part_name [1], _part_exists [2],
_create_part [3], and _drop_part [4]. Functions [3] and [4] use
EXECUTE to create and drop the table.

Instead of computing the dates each time i use function
historische_zeitstempel(date) [5], which checks whether a part
corresponding to the date of its argument already exists or not. If
not, the function creates the table, otherwise it uses the already
existing table:

olsim@traffic_nrw_0_3_0=# \dt
                       List of relations
   Schema    |              Name               | Type  | Owner
-------------+---------------------------------+-------+-------
...
 olsim_3_5_0 | historische_zeitstempel         | table | olsim
...

olsim@traffic_nrw_0_3_0=# select * from historische_zeitstempel(current_date);
 historische_zeitstempel
-------------------------
 2007-02-01 00:00:00+01
...
 2006-08-17 00:00:00+02
(20 rows)

olsim@traffic_nrw_0_3_0=# \dt
                        List of relations
   Schema    |                Name                | Type  | Owner
-------------+------------------------------------+-------+-------
...
 olsim_3_5_0 | historische_zeitstempel            | table | olsim
 olsim_3_5_0 | historische_zeitstempel_2007_02_08 | table | olsim
...

olsim@traffic_nrw_0_3_0=# select * from
historische_zeitstempel((current_date + interval '1 day')::date);
 historische_zeitstempel
-------------------------
 2007-02-02 00:00:00+01
...
 2006-08-18 00:00:00+02
(20 rows)

olsim@traffic_nrw_0_3_0=# \dt
                        List of relations
   Schema    |                Name                | Type  | Owner
-------------+------------------------------------+-------+-------
...
 olsim_3_5_0 | historische_zeitstempel            | table | olsim
 olsim_3_5_0 | historische_zeitstempel_2007_02_08 | table | olsim
 olsim_3_5_0 | historische_zeitstempel_2007_02_09 | table | olsim
...

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:

olsim@traffic_nrw_0_3_0=# select * from
historische_zeitstempel_clean(current_date, current_date);
NOTICE:  failed to drop table historische_zeitstempel_2007_02_09
CONTEXT:  SQL function "historische_zeitstempel_drop_part" statement 1
SQL statement "SELECT historische_zeitstempel_drop_part('2007-02-09');"
PL/pgSQL function "historische_zeitstempel_clean" line 18 at execute statement
NOTICE:  relation 50435 is still open
CONTEXT:  SQL function "historische_zeitstempel_drop_part" statement 1
SQL statement "SELECT historische_zeitstempel_drop_part('2007-02-09');"
PL/pgSQL function "historische_zeitstempel_clean" line 18 at execute statement
 historische_zeitstempel_clean
-------------------------------
(0 rows)

What does it mean "relation 50435 is still open"?
What is wrong with function _clean [6]?

I don't think there are any locks granted on that table, but see [7].

Thanks for any help in advance.

Best regards,
Johannes

--------------------------------------------------------------------------
[1]: _part_name
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_part_name(DATE)
RETURNS char(34)
IMMUTABLE STRICT AS $$

    SELECT  'historische_zeitstempel_'
        || substring(to_char(date_part('year', $1),'SG9999') from 2 for 4) || '_'
        || substring(to_char(date_part('month', $1),'SG09') from 2 for 2) || '_'
        || substring(to_char(date_part('day', $1),'SG09') from 2 for 2);

$$ LANGUAGE sql;

--------------------------------------------------------------------------
[2]: _part_exists
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_part_exists(DATE)
RETURNS BOOLEAN
IMMUTABLE STRICT AS $$

    SELECT count(tablename) > 0
    FROM pg_catalog.pg_tables
    WHERE tablename = (SELECT historische_zeitstempel_part_name($1))
    LIMIT 1;

$$ LANGUAGE sql;

--------------------------------------------------------------------------
[3]: _create_part
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_create_part(for_day DATE)
RETURNS BOOLEAN STRICT AS $$
DECLARE
    tname char(34);
BEGIN
    tname := historische_zeitstempel_part_name(for_day);

    BEGIN
        EXECUTE '
        CREATE TABLE ' || tname || ' (
            CHECK (referenz >= ' || quote_literal(for_day) || ' AND referenz < ' || quote_literal((for_day + interval
'1day')::date) || ') 
        ) INHERITS (historische_zeitstempel);

        INSERT INTO pg_autovacuum
        SELECT oid, true, 1, 0.0, 1, 0.0, 0, 200
        FROM pg_class
        WHERE relname = ' || quote_literal(tname) || '
          AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema());
            ';
        EXECUTE ' INSERT INTO ' || tname || ' SELECT ' || quote_literal(for_day)
        || ', z.h FROM historische_zeitstempel_berechnen(' || quote_literal(for_day) || ') z(h);';

        EXCEPTION WHEN OTHERS
        THEN
        RAISE NOTICE 'failed to create table %', tname;
        RAISE NOTICE '%', SQLERRM;
        RETURN false;
    END;

    RETURN true;
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------
[4]: _drop_part
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_drop_part(for_day DATE)
RETURNS BOOLEAN STRICT AS $$
DECLARE
    tname char(34);
BEGIN
    tname := historische_zeitstempel_part_name(for_day);

    IF NOT historische_zeitstempel_part_exists(for_day)
    THEN
        RETURN false;
    ELSE
        BEGIN
            EXECUTE 'DROP TABLE ' || tname || ';';
            EXCEPTION WHEN OTHERS
            THEN
            RAISE NOTICE 'failed to drop table %', tname;
            RAISE NOTICE '%', SQLERRM;
            RETURN false;
        END;

        RETURN true;
    END IF;

END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------
[5]:
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel(zeitpunkt TIMESTAMP WITH TIME ZONE)
RETURNS SETOF TIMESTAMP WITH TIME ZONE
STRICT AS $$
DECLARE
    tname char(34);
    tag date;
    zeit interval;
    str text;
    t RECORD;
BEGIN
    tag := zeitpunkt::date;
    tname := historische_zeitstempel_part_name(tag);

    IF NOT historische_zeitstempel_part_exists(tag)
    THEN
        IF NOT historische_zeitstempel_create_part(tag)
        THEN
            RETURN;
        END IF;
    END IF;

    zeit := (zeitpunkt - date(zeitpunkt))::interval;
    str := ' SELECT referenz, (pendant + ' || quote_literal(zeit) || '::interval)::timestamptz AS pendant
                 FROM ' || quote_ident(tname) || '
                 WHERE referenz = ' || quote_literal(tag) || ';';

    FOR t IN
    EXECUTE str
    LOOP
        RETURN NEXT t.pendant;
    END LOOP;

    RETURN;

END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------
[6]: _clean
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION historische_zeitstempel_clean(keep_from DATE, keep_to DATE)
RETURNS SETOF DATE STRICT AS $$
DECLARE
    tname char(34);
    t RECORD;
    success boolean;
BEGIN

    IF keep_to < keep_from
    THEN
        RETURN;
    END IF;

    FOR t IN EXECUTE '
    SELECT DISTINCT referenz, 0 as i
    FROM historische_zeitstempel
    WHERE referenz NOT BETWEEN ' || quote_literal(keep_from) || ' AND ' || quote_literal(keep_to) || ';'
    LOOP
        BEGIN
        EXECUTE 'SELECT historische_zeitstempel_drop_part(' || quote_literal(t.referenz) || ');' INTO success;
        END;
        IF success
        THEN
            RETURN NEXT t.referenz;
        END IF;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------
[7]:
--------------------------------------------------------------------------
olsim@traffic_nrw_0_3_0=# select * from pg_locks;
   locktype    | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid
|     mode       | granted  

---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+-----------------+---------
 transactionid |          |          |      |       |         69864 |         |       |          |       69864 | 39570
|ExclusiveLock   | t 
 relation      |    49671 |    10342 |      |       |               |         |       |          |       69864 | 39570
|AccessShareLock | t 
(2 rows)