<rant>
Please can someone explain why Postgres cannot recognize that objects
(referenced by pl/pgsql functions) whose OID no longer exists could in
fact be found (as new objects) if the function was reparsed and compiled
again.
Here's an example:
Create table t1 (f1 integer);
insert into t1 values (1);
CREATE OR REPLACE FUNCTION GetOne() RETURNS integer AS '
DECLARE
i integer;
BEGIN
select f1 into i from t1 limit 1;
return i;
END;
' LANGUAGE 'plpgsql';
select GetOne(); <-- WORKS OK
drop table t1;
Create table t1 (f1 integer);
insert into t1 values (1);
select GetOne(); <-- ERROR: relation with OID xxxxxx does not exist
Now I know why this is happening, but it is really crap.
Here's a solution (similar to what Oracle does (I believe):
Whenever you delete an object, you locate any functions (or other objects)
referencing that object's OID, and you mark them as invalid, and any
subsequent objects that now refer to the newly invalidated object(s).
When a function is invoked which is marked invalid, then it is first
parsed/compiled again - if that is successful then the function executes
as before. If compiling is unsuccessful, then the standard "relation with
OID xxxxxx does not exist" error could be returned (or perhaps an even
better error message detailing the actual name of the missing object, God
forbid!).
Where else would this be useful? How about the following scenario when you
want to make use of a temporary table in a pl/pgsql function:
-- This will not work
....
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN SELECT * FROM foo
LOOP
.....
END LOOP;
DROP TABLE foo;
-- You have to do this
....
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN EXECUTE "SELECT * FROM foo"
LOOP
.....
END LOOP;
DROP TABLE foo;
....
This might even make rebuilding databases easier because you could refer
to objects (during the rebuild) that don't yet exist (haven't been
inserted yet), but that would be validated the first time the object was
actually accessed.
There are loads of instances (db in flux, move table to another schema
etc) why you might want/need to drop a table, and recreate it. But in
Postgres, you have to reapply all DDL statements to the db that referenced
that dropped/recreated object - this is just not fun!
I don't know, but cannot believe that it would be hard to implement this,
and although many might not think that it is worth it, it is a really NICE
feature in Oracle. And when you're used to it, it is a royal pain in the
ass not having it.
</rant>
BTW, I still love postgres - I just want it to be better!
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
