游标和交易，为什么？ [英] Cursors and Transactions, why?
问题描述
为什么必须在打开的事务中定义游标？显然有一个很好的理由，但我无法理解。从较高的层面来看，允许光标比
创建它的交易更长的时间是什么？
游标看起来好像他们有一些不错的性能优势（特别是如果
你没有使用所有的行），但是它们的用处大大减少了
因为你必须留下一个交易开放。
eric
-------------------------- - （广播结束）---------------------------
提示1：订阅和取消订阅命令转到 ma*******@postgresql.org
Why must a cursor be defined in an open transaction? Obviously there''s
a good reason, but I can''t figure it out. On a high level, what would
be involved in allowing a cursor to outlive the transaction that
created it?
Cursors seem as if they have some nice performance benefits (esp. if
you''re not using all rows found), but their usefulness drops
considerably since you must leave a transaction open.
eric
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
推荐答案
2004年4月5日下午6:44，Joe Conway写道：
On Apr 5, 2004, at 6:44 PM, Joe Conway wrote:
Eric Ridge写道：
Eric Ridge wrote:
为什么必须在开放交易中定义游标？显然，这是一个很好的理由，但我无法理解。在较高的层面上，
允许光标比创建它的事务更长久会涉及到什么？
Why must a cursor be defined in an open transaction? Obviously
there''s a good reason, but I can''t figure it out. On a high level,
what would be involved in allowing a cursor to outlive the
transaction that created it?
从历史上看，我认为这是因为内存是最后发布的当前交易（即分配是在TopTransactionContext中进行的）。但是从7.4开始，游标*可以*超过交易：
http://www.postgresql.org/docs/curre...l-declare.html
WITH HOLD
不保持
Historically I think it was because the memory was released at the end
of the current transaction (i.e. allocations were made in
TopTransactionContext). But as of 7.4, cursors *can* outlive
transactions:
http://www.postgresql.org/docs/curre...l-declare.html
WITH HOLD
WITHOUT HOLD
圣牛！这是太棒了。我不知道。 <短暂暂停>哦，和
我看到7.4中的FETCH支持绝对定位。必须升级。
谢谢！
eric
------------ ---------------（播出结束）---------------------------
提示9：如果您的
加入列的数据类型不匹配，计划员将忽略您选择索引扫描的愿望
holy cow! This is fantastic. I had no idea. <short pause> ooh, and
I see FETCH, in 7.4, supports absolute positioning. Must upgrade.
thanks!
eric
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
Eric Ridge写道：
Eric Ridge wrote:
为什么必须在打开的事务中定义游标？显然有一个很好的理由，但我无法理解。在一个很高的层面上，什么会让光标比创建它的交易更长？
Why must a cursor be defined in an open transaction? Obviously there''s
a good reason, but I can''t figure it out. On a high level, what would
be involved in allowing a cursor to outlive the transaction that created
it?
从历史上我认为它是因为内存最后发布了当前交易的
（即分配在
TopTransactionContext中）。但是从7.4开始，游标*可以*比交易更长：
http://www.postgresql.org/docs/curre...l-declare.html
WITH HOLD
没有保留
WITH HOLD指定光标可以继续在
之后使用，创建它的事务成功提交。不保留
指定光标不能在
创建它的事务之外使用。如果既没有指定WITHOUT HOLD也没有指定WITH HOLD，则没有
HOLD是默认值。
HTH，
乔
---------------------------（广播结束）--------- ------------------
提示5：您查看了我们广泛的常见问题解答吗？
http://www.postgresql.org/docs/faqs/FAQ.html
Historically I think it was because the memory was released at the end
of the current transaction (i.e. allocations were made in
TopTransactionContext). But as of 7.4, cursors *can* outlive transactions:
http://www.postgresql.org/docs/curre...l-declare.html
WITH HOLD
WITHOUT HOLD
WITH HOLD specifies that the cursor may continue to be used after
the transaction that created it successfully commits. WITHOUT HOLD
specifies that the cursor cannot be used outside of the transaction that
created it. If neither WITHOUT HOLD nor WITH HOLD is specified, WITHOUT
HOLD is the default.
HTH,
Joe
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Eric Ridge写道：
Eric Ridge wrote:
为什么必须在开放的交易中定义游标？显然有一个很好的理由，但我无法理解。从较高的层面来说，允许光标比创建它的事务更长久会涉及到什么？
因为事务是保护构成结果的行的原因
设置为通过真空删除。在PostgreSQL中，游标是一个运行的
查询执行程序，只是位于其操作的中间。如果
基础查询是一个简单的顺序扫描，那么
结果集没有实现，但是每个未来的提取操作都会读取
直接来自基表。如果真空认为没有人再需要那些行，那么这显然会被搞砸了。
游标看起来好像有一些不错的性能优势（特别是如果
你没有使用所有找到的行），但它们的用处大大减少了，因为你必须让交易保持开放。
Why must a cursor be defined in an open transaction? Obviously there''s
a good reason, but I can''t figure it out. On a high level, what would
be involved in allowing a cursor to outlive the transaction that
created it?
Because the transaction is what protects the rows that build the result
set from being removed by vacuum. In PostgreSQL, a cursor is a running
query executor just sitting in the middle of its operation. If the
underlying query is for example a simple sequential scan, then the
result set is not materialized but every future fetch operation will
read directly from the base table. This would obviously get screwed up
if vacuum would think nobody needs those rows any more.
Cursors seem as if they have some nice performance benefits (esp. if
you''re not using all rows found), but their usefulness drops
considerably since you must leave a transaction open.
现在你知道了为什么如果你不使用所有行，它们会非常好。如果你使用Joe Conway建议的WITH HOLD，我认为这个
的好处就消失了。
Jan
-
＃========================================= ======== =====================＃
＃因为错误而得到宽恕更容易而不是正确的。 ＃
＃让我们打破这个规则 - 请原谅我。 ＃
＃======================================== ========= = Ja******@Yahoo.com ＃
---------------------------（播出结束）-------------- -------------
提示4：不要杀死-9''邮政局长
And now you know why they are so good if you don''t use all rows. This
benefit I think goes away if you use Joe Conway''s suggestion of WITH HOLD.
Jan
--
#================================================= =====================#
# It''s easier to get forgiveness for being wrong than for being right. #
# Let''s break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
这篇关于游标和交易，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
