Thread: BUG #3718: Unexpected undefined_table error after creating/dropping tables

The following bug has been logged online:

Bug reference:      3718
Logged by:          Dean
Email address:      ratq92nomr@hotmail.com
PostgreSQL version: 8.2.5
Operating system:   Linux (opensuse 10.3 64-bit) and Windows 2000 SP4
Description:        Unexpected undefined_table error after creating/dropping
tables
Details:

If I create a function which relies on the undefined_table exception to test
if a table exists, it does not behave as expected. Here's the simplest
example I could come up with:

CREATE OR REPLACE FUNCTION foo() RETURNS text AS
$$
BEGIN
  DELETE FROM bar;
  RETURN 'Table exists';
EXCEPTION
  WHEN undefined_table THEN
    RETURN 'Table missing';
END;
$$ LANGUAGE plpgsql VOLATILE;

DROP TABLE IF EXISTS bar;
SELECT foo();
CREATE TABLE bar(dummy int);
SELECT foo();
DROP TABLE bar;
SELECT foo();
CREATE TABLE bar(dummy int);
SELECT foo();

The 4 calls to foo() return

Table missing
Table exists
Table missing
Table missing

I expect the final call to foo() to return 'Table exists' not 'Table
missing'.

"Dean" <ratq92nomr@hotmail.com> writes:
> If I create a function which relies on the undefined_table exception to test
> if a table exists, it does not behave as expected.

Try issuing the DELETE via EXECUTE --- you're getting burnt by plan
caching.

But actually, do you really want something as destructive as DELETE
for an existence probe?  I'd try

    PERFORM 'bar'::text::regclass;

and see if that throws an error.  (The double cast is important here,
so that you get a runtime lookup not a compile-time one.)

            regards, tom lane

> Try issuing the DELETE via EXECUTE --- you're getting burnt by plan> cach=
ing.>=20
Ah yes, that makes sense. So the planner is caching the failed query plan f=
rom when the table didn't exist?
Not a bug after all I guess. Sorry.
=20
I'm moving from an Oracle background, where dropping the table would have m=
arked the function as invalid unless I had used EXECUTE IMMEDIATE, so I wou=
ld have been less likely to make this mistake.
=20
> But actually, do you really want something as destructive as DELETE> for =
an existence probe? I'd try> > PERFORM 'bar'::text::regclass;> > and see if=
 that throws an error. (The double cast is important here,> so that you get=
 a runtime lookup not a compile-time one.)> > regards, tom lane
Actually the DELETE was just an artificial example. My real code reads from=
 a temporary table, creating it if necessary. Typically it would not be dro=
pped mid-session, so I shouldn't hit this problem. I only fell over it duri=
ng testing, when I was getting some quite confusing results. I think it all=
 makes sense if I think about how these query plans are cached.
=20
Thanks for your help.
=20
Dean.
=20
_________________________________________________________________
100=92s of Music vouchers to be won with MSN Music
https://www.musicmashup.co.uk=

ratq nomr <ratq92nomr@hotmail.com> writes:
> I'm moving from an Oracle background, where dropping the table would
> have marked the function as invalid unless I had used EXECUTE
> IMMEDIATE, so I would have been less likely to make this mistake.

PG 8.3 will behave that way, but there's no support for it in existing
releases :-(

            regards, tom lane