Thread: "Relation x does not exist" error when x does exist

Hi all,

I have a function as follows:

<code>
[gaurav@linuxserver gaurav]$ cat foo

DROP FUNCTION foo();
CREATE FUNCTION foo() RETURNS INTEGER AS '

BEGIN

        DROP TABLE foo_1;
        CREATE TABLE foo_1 AS SELECT x FROM foo;

        DROP TABLE foo_2;
        CREATE TABLE foo_2 AS SELECT x FROM foo_1;

        RETURN 1;

END;
' LANGUAGE 'plpgsql';

[gaurav@linuxserver gaurav]$
</code>

Now my problem is as follows:

<problem>
test=> SELECT foo();
 foo
-----
   1
(1 row)

test=> SELECT foo();
ERROR:  Relation 5483738 does not exist
test=>
test=> SELECT relname, relfilenode FROM pg_class WHERE relfilenode=5483738;
 relname | relfilenode
---------+-------------
 foo_1   |     5483738
(1 row)

test=>
test=> \i foo
DROP
CREATE
test=> SELECT foo();
 foo
-----
   1
(1 row)

test=> SELECT foo();
ERROR:  Relation 5483812 does not exist
test=>
test=> \q
[gaurav@linuxserver gaurav]$ psql test
<snip/>
test=> SELECT foo();
 foo
-----
   1
(1 row)

test=>
test=> SELECT foo();
ERROR:  Relation 5483848 does not exist
test=>
test=> \q
</problem>


So as you can see, I get a "relation does not exist" error on a
function that is very much there.

Three observations:
1. When I drop the function and create it again, it
works once before the error is back.
2. I already tried was putting BEGIN-END blocks around the two
DROP/CREATE TABLE pairs.
3. The Delphi app that calls this function (which actually prepares a
table that feeds a report) has to be restarted between invocations of
the report.

One solution would be to create these tables right at the outset
and only truncate them each time a report is to be created with
different parameters. However this does not deliver for the reports
where the table is itself generated dynamically depending on
parameters passed to the function.

Thanks in advance

Regards,
Gaurav.

--
Sleep:  A completely inadequate substitute for caffeine.

Gaurav Priyolkar writes:

> test=> SELECT foo();
> ERROR:  Relation 5483738 does not exist
> test=>
> test=> SELECT relname, relfilenode FROM pg_class WHERE relfilenode=5483738;
>  relname | relfilenode
> ---------+-------------
>  foo_1   |     5483738
> (1 row)

relfilenode has nothing to do with that.

PL/pgSQL compiles and caches the functions you are executing, so if you
change the database schema under it (or in it) you lose.  This is an
intentional design choice.  If you want to execute code dynamically you
should look at the EXECUTE command in PL/pgSQL, or for some other
language.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter