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 <gaurav_lists@yahoo.com> writes:
> So as you can see, I get a "relation does not exist" error on a
> function that is very much there.=20

It's not there at the point of the failure, though.  Your observation
that foo_1 is still there is made after rolling back the drop and
recreate of foo_1.

The reason there's an issue is that plpgsql caches a query plan for the
"SELECT x FROM foo_1" query, and that plan is no good after you drop the
original version of foo_1; but we don't currently have a mechanism to
invalidate the cached plan.

Workarounds: (1) use EXECUTE to avoid caching a plan for the problem
queries; (2) instead of DROP/CREATE, consider DELETE FROM/INSERT INTO
(or perhaps TRUNCATE instead of DELETE, although TRUNCATE can't be
rolled back).

            regards, tom lane

plpgsql functions that want to do drops and
creates will need to use EXECUTE to run
statements for those tables.

On Thu, 11 Oct 2001, Gaurav Priyolkar wrote:

> 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';

[Apologies to Tom Lane. I was about to send this mail a third time
wondering why it was not appearing on the list when I notices my
replies were going to you. I had not set up pgsql-general as a "list"
in mutt. Sorry.]

On Fri, Oct 12, 2001 at 02:12:16PM -0400, Tom Lane wrote:
>
> The reason there's an issue is that plpgsql caches a query plan for the
> "SELECT x FROM foo_1" query, and that plan is no good after you drop the
> original version of foo_1; but we don't currently have a mechanism to
> invalidate the cached plan.

Why does putting BEGIN-END blocks around the queries, and thereby
separating the transactions, not help? Should this not get around
caching as each transaction is committed before proceeding to the next?


> Workarounds: (1) use EXECUTE to avoid caching a plan for the problem
> queries;

Will using EXECUTE only for the "CREATE TABLE ... SELECT" query be
sufficient? Because after that I have "FOR record IN SELECT .. LOOP" queries
in some functions with some of the LOOP bodies being dynamic queries.

> (2) instead of DROP/CREATE, consider DELETE FROM/INSERT INTO
> (or perhaps TRUNCATE instead of DELETE, although TRUNCATE can't be
> rolled back).

Not feasible, because some functions have dynamically created tables so
I need to re-create table at each invocation.  Come to think of it,
EXECUTE around only the CREATE may not help because I have already
used it in such cases. Will check on Monday and revert to the list.

TIA

Regards,
Gaurav.

--
Sleep:  A completely inadequate substitute for caffeine.

>>>>> "Gaurav" == Gaurav Priyolkar <gaurav_lists@yahoo.com> writes:

Gaurav> Not feasible, because some functions have dynamically created
Gaurav> tables so I need to re-create table at each invocation.

When I hear that, I always think "bad design".

If the answer is "dynamically created tables", I think you asked the
wrong question to begin with.

It's a bit like saying "I need to invent a new language for every book
I write".  Uh, probably not. :)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

> On Fri, Oct 12, 2001 at 02:12:16PM -0400, Tom Lane wrote:
> >
> > The reason there's an issue is that plpgsql caches a query plan for the
> > "SELECT x FROM foo_1" query, and that plan is no good after you drop the
> > original version of foo_1; but we don't currently have a mechanism to
> > invalidate the cached plan.
>
> Why does putting BEGIN-END blocks around the queries, and thereby
> separating the transactions, not help? Should this not get around
> caching as each transaction is committed before proceeding to the next?

Currently, I believe the queries are cached for the life of the backend.

> > Workarounds: (1) use EXECUTE to avoid caching a plan for the problem
> > queries;
>
> Will using EXECUTE only for the "CREATE TABLE ... SELECT" query be
> sufficient? Because after that I have "FOR record IN SELECT .. LOOP" queries
> in some functions with some of the LOOP bodies being dynamic queries.

No.  IIRC, you have to use EXECUTE on pretty much any query that
references the table, because any query that was run on the old foo_1
would have the old foo_1 cached.  The other option is to use pltcl or
something which doesn't cache the plans I believe, but I don't know for
certain since I don't know tcl and haven't used it.

On Tue, Oct 16, 2001 at 01:22:45AM -0700, Randal L. Schwartz wrote:
> >>>>> "Gaurav" == Gaurav Priyolkar <gaurav_lists@yahoo.com> writes:
>
> Gaurav> Not feasible, because some functions have dynamically created
> Gaurav> tables so I need to re-create table at each invocation.
>
> When I hear that, I always think "bad design".

Perhaps there is a better way, but let me explain what I need to do.
A (temporary) table, say B, has to be created which is basically a
vertical representation of a number rows of another table, A. The data
in rows of A is interpreted differently depending on which one of a
number of groups that record belongs to, which is decided by certain
attributes which are ids.

So, a row in B is actually a number of rows from A, with the item in
each column of B corresponding to the data in one of the said rows of A.

I will try to give an example to be a little more clear: (please note
this is purely for the sake of description)
Consider A to be a table having rows with fields: city_id,  month_id
and temperature.
So B will be a table with columns city_id and a number of columns for
months. A row of B will then be the temperatures measured over a
number of months.

Now, reason for all of the above:
Apparently, when creating a Delphi report, it has to be fed with data
from a database table with exactly the columns as they are to appear
on the report. I am just meeting the specified requirement.

However, is there a better way.

Disclaimer:
Yes, I am not a Delphi programmer either.

> If the answer is "dynamically created tables", I think you asked the
> wrong question to begin with.

Sorry, I have amended my subject line as you suggested.
I had used the error message for subject thinking someone might find
it familiar and be able to help.

> It's a bit like saying "I need to invent a new language for every book
> I write".  Uh, probably not. :)

Hey, Programming Perl has gone an edition ahead of Learning Perl
... when are we going to see the Third Edition of the 'Llama book'?
No new languages til then :)

Regards,
Gaurav

>>>>> "Gaurav" == Gaurav Priyolkar <pgaurav@goatelecom.com> writes:

Gaurav> Hey, Programming Perl has gone an edition ahead of Learning Perl
Gaurav> ... when are we going to see the Third Edition of the 'Llama book'?
Gaurav> No new languages til then :)

It's been out since July.  All new, all better jokes.  Not a single
bit of cut and paste from either of the prior editions.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

I would bet this requirement is really that the SQL SELECT column order must
match the column order of the report, which is something else entirely. If
the number of 'months' columns (per your example) are fixed, and the
specific months are known ahead of time, you could (theoretically) do
self-join. This may have to be an outer join if it is possible that there
were no entries for any particular month.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

> From: Gaurav Priyolkar <pgaurav@goatelecom.com>
> Date: Tue, 16 Oct 2001 23:32:49 +0530
> To: pgsql-general@postgresql.org
> Subject: Dynamically created tables [WAS Re: [GENERAL] "Relation x does not
> exist" ...
>
> Apparently, when creating a Delphi report, it has to be fed with data
> from a database table with exactly the columns as they are to appear
> on the report. I am just meeting the specified requirement.

On Wed, Oct 17, 2001 at 08:59:23AM -0600, Keary Suska wrote:
> I would bet this requirement is really that the SQL SELECT column order must
> match the column order of the report, which is something else entirely. If

Actually, column order does not matter. The requirement is that whatever
columns have to be displayed on the report must be available in he
table/view.

> the number of 'months' columns (per your example) are fixed, and the
> specific months are known ahead of time, you could (theoretically) do
> self-join. This may have to be an outer join if it is possible that there
> were no entries for any particular month.

Therein lies the catch. As in the example, the number of months
columns as well as which months columns are not fixed. If they were,
it would be possible to create the table only once and truncate each
time.

TIA

Regards,
Gaurav.

--
Sleep:  A completely inadequate substitute for caffeine.