Thread: Missing OID rant
<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
Alvaro Herrera said: > Actually, in your example the only thing you need to do is close the > connection and reconnect. I agree it would be nice to recompile the > function automatically, but it's not as bad as you put it. Thanks, I wasn't aware of that. John Sidney-Woollett
On Thu, Apr 22, 2004 at 02:07:39PM +0100, John Sidney-Woollett wrote: > 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! Actually, in your example the only thing you need to do is close the connection and reconnect. I agree it would be nice to recompile the function automatically, but it's not as bad as you put it. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Sallah, I said NO camels! That's FIVE camels; can't you count?" (Indiana Jones)
Tom Lane said: > "John Sidney-Woollett" <johnsw@wardbrook.com> writes: >> 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. > > Criticism in the form of a patch would be useful. I know but I don't have the expertise to do that - I was only trying to raise the issue... :) >> 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). > > Yup, this is on the TODO list, and has been for awhile: > > * Flush cached query plans when their underlying catalog data changes Is the query plan cached for the life of the session, or the life of the cache/database/postmaster session? Isn't removing the plan from the cache, and marking objects invalid two separate although related issues? >> I don't know, but cannot believe that it would be hard to implement >> this, > > Step right up ... Believe you me, you don't want my code - it's been quite a few years since I've done any C programming, a patch I produced might set Postgres back 5 years!! BTW, I was only highlighting the issue because the missing OID message bit me in the ass again today, and I didn't realise that it existed as an outstanding todo item. I guess that there are other missing features like exception handling and nested transactions which are more important and need attention more urgently... John Sidney-Woollett
"John Sidney-Woollett" <johnsw@wardbrook.com> writes:
> 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.
Criticism in the form of a patch would be useful.
> 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).
Yup, this is on the TODO list, and has been for awhile:
* Flush cached query plans when their underlying catalog data changes
> I don't know, but cannot believe that it would be hard to implement this,
Step right up ...
regards, tom lane
Jan Wieck said: > Exactly, and because of that we want you to do the easy stuff with the > cache invalidation, so that we have the time to think about the others. Might be easy for you... I shudder when I try to imagine the size of the mountain that that simple task would represent for me ;) John Sidney-Woollett
John Sidney-Woollett wrote: > Tom Lane said: >> Yup, this is on the TODO list, and has been for awhile: >> >> * Flush cached query plans when their underlying catalog data changes > > Is the query plan cached for the life of the session, or the life of the > cache/database/postmaster session? DB connection is the lifetime of PL/pgSQL cached plans. > > Isn't removing the plan from the cache, and marking objects invalid two > separate although related issues? It is. > >>> I don't know, but cannot believe that it would be hard to implement >>> this, >> >> Step right up ... > > Believe you me, you don't want my code - it's been quite a few years since > I've done any C programming, a patch I produced might set Postgres back 5 > years!! Well, that's what I do with French people, I convince them that it hurts less if they rather talk English or German instead of me uttering something in their language. > > BTW, I was only highlighting the issue because the missing OID message bit > me in the ass again today, and I didn't realise that it existed as an > outstanding todo item. > > I guess that there are other missing features like exception handling and > nested transactions which are more important and need attention more > urgently... Exactly, and because of that we want you to do the easy stuff with the cache invalidation, so that we have the time to think about the others. Jan > > John Sidney-Woollett > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #