缺少OID咆哮 [英] Missing OID rant
问题描述
< rant>
请有人解释为什么Postgres无法识别那些OID不再存在的对象
（由pl / pgsql函数引用）可以在
中找到（作为新对象）如果函数被重新分析和编译
再次。
这里''例如：
创建表t1（f1整数）;
插入t1值（1）;
创建或替换函数GetOne（）返回整数AS''
DECLARE
i整数;
BEGIN
从t1限制1中选择f1进入i;
返回i;
END;
''LANGUAGE''plpgsql'';
选择GetOne（）; < - WORKS OK
drop table t1;
创建表t1（f1整数）;
插入t1值（1）;
选择GetOne（）; < - 错误：与OID xxxxxx的关系不存在
现在我知道为什么会发生这种情况，但它确实很糟糕。
这是一个解决方案（类似于Oracle所做的（我相信）：
每当你删除一个对象时，你找到任何函数（或其他对象）
引用该对象的OID，并将它们标记为无效，以及任何现在引用新无效对象的后续对象。
当调用一个被标记为无效的函数时，它首先再次解析/编译
- 如果成功则该函数执行
，如前所述。编译不成功，那么标准的与
OID xxxxxx的关系不存在可能会返回错误（或者甚至可能是一个更好的错误消息，详细说明实际名称丢失的物体，上帝
禁止！）。
这还有什么用处呢？以下怎么样？当你想要在pl / pgsql函数中使用临时表时，你需要使用一个临时表：
- 这不会起作用
....
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN SELECT * FROM foo
LOOP < br $>
.....
END LOOP;
DROP TABLE foo;
- 你必须这样做
....
CREATE TEMP TABLE foo AS SELECT ....;
FOR r IN EXECUTE" SELECT * FROM foo"
LOOP
.....
END LOOP;
DROP TABLE foo;
....
这甚至可以使重建数据库更容易，因为你可以将
引用到对象（在重建期间）还没有存在（还没有插入
），但是第一次实际访问该对象实际上是有效的。
有大量的实例（db在不断变化，将表移动到另一个模式
等）为什么你可能需要/需要t o删除一个表，然后重新创建它。但是在
Postgres中，你必须将所有DDL语句重新应用到引用
的数据库中删除/重新创建对象 - 这只是不好玩！
我不知道，但不敢相信这很难实现，
虽然很多人可能认为不值得，但它确实是NICE
Oracle中的
功能。当你习惯了它的时候，没有它的话就是一个皇家的痛苦。
< / rant>
BTW，我仍然喜欢postgres - 我只是希望它更好！
John Sidney-Woollett
---------------------------（广播结束）---------------- -----------
提示2：您可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma*******@postgresql.org ）
<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)
推荐答案
Alvaro Herrera说：
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.
谢谢，我不是'不知道。
John Sidney-Woollett
---------------- -----------（播出结束）---------------------------
提示4：不要杀死-9''邮政局长
Thanks, I wasn''t aware of that.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
Alvaro Herrera说：
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.
谢谢，我不是'不知道。
John Sidney-Woollett
---------------- -----------（播出结束）---------------------------
提示4：不要杀死-9''邮政局长
Thanks, I wasn''t aware of that.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
Tom Lane说：
Tom Lane said:
" John Sidney-Woollett <乔**** @ wardbrook.com>写道：
"John Sidney-Woollett" <jo****@wardbrook.com> writes:
请有人解释为什么Postgres无法识别那些OID不再存在的对象（由pl / pgsql函数引用）可以在其中找到（作为新对象） ）如果函数再次被重新编译和编译。
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... :)
每当你删除一个对象时，你找到引用该对象的OID的任何函数（或其他
对象），并将它们标记为无效，以及任何
现在引用新失效对象的后续对象。
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).
是的，这是在TODO列表上，已经有一段时间了：
*当基础目录数据发生变化时刷新缓存的查询计划
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 ...
相信你，我，你不需要我的代码 - 这已经有好几年了
我已经完成了任何C编程，我制作的补丁可能会让Postgres回归5
年!!
顺便说一句，我只是突出了这个问题，因为缺少OID消息位
我今天又来了屁股，我并没有意识到它作为一个优秀的待办事项存在。
我想还有其他缺失的功能，如异常处理和
巢ed交易更重要，需要更多关注
紧急...
John Sidney-Woollett
---------------------------（广播结束）------------------ ---------
提示1：订阅和取消订阅命令转到 ma ** *****@postgresql.org
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
这篇关于缺少OID咆哮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
