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(at)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(at)traffic_nrw_0_3_0=# \dt
List of relations
Schema | Name | Type | Owner
-------------+---------------------------------+-------+-------
...
olsim_3_5_0 | historische_zeitstempel | table | olsim
...

olsim(at)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(at)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(at)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(at)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(at)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 '1 day')::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(at)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)