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> caching.>
Ah yes, that makes sense. So the planner is caching the failed query plan from when the table didn't exist?
Not a bug after all I guess. Sorry.
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.
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 dropped mid-session, so I shouldn't hit this problem. I only fell over it during 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.
Thanks for your help.
Dean.
_________________________________________________________________
100’s 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