postgresql 在执行 postgreql 函数时提交事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22351039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Committing transactions while executing a postgreql Function
提问by Yousuf Sultan
I have Postgresql Function which has to INSERT about 1.5 million data into a table. What I want is I want to see the table getting populated with every one records insertion. Currently what is happening when I am trying with say about 1000 records, the get gets populated only after the complete function gets executed. If I stop the function half way through, no data gets populated. How can I make the record committed even if I stop after certain number of records have been inserted?
我有 Postgresql 函数，它必须将大约 150 万条数据插入到一个表中。我想要的是我希望看到每个记录插入都填充表。目前，当我尝试使用大约 1000 条记录时发生的情况，只有在执行完整函数后才会填充 get。如果我中途停止该功能，则不会填充任何数据。即使在插入一定数量的记录后停止，如何使记录提交？
采纳答案by Kuberchaun
This can be done using dblink. I showed an example with one insert being committed you will need to add your while loop logic and commit every loop. You can http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html
这可以使用 dblink 来完成。我展示了一个示例，其中提交了一个插入，您需要添加 while 循环逻辑并提交每个循环。你可以http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html
CREATE OR REPLACE FUNCTION log_the_dancing(ip_dance_entry text)
RETURNS INT AS
$BODY$
DECLARE
BEGIN
PERFORM dblink_connect('dblink_trans','dbname=sandbox port=5433 user=postgres');
PERFORM dblink('dblink_trans','INSERT INTO dance_log(dance_entry) SELECT ' || '''' || ip_dance_entry || '''');
PERFORM dblink('dblink_trans','COMMIT;');
PERFORM dblink_disconnect('dblink_trans');
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION log_the_dancing(ip_dance_entry text)
OWNER TO postgres;
BEGIN TRANSACTION;
select log_the_dancing('The Flamingo');
select log_the_dancing('Break Dance');
select log_the_dancing('Cha Cha');
ROLLBACK TRANSACTION;
--Show records committed even though we rolled back outer transaction
select *
from dance_log;
回答by Craig Ringer
What you're asking for is generally called an autonomous transaction.
您所要求的通常称为自治事务。
PostgreSQL does not support autonomous transactions at this time (9.4).
PostgreSQL 目前 (9.4) 不支持自治事务。
To properly support them it really needs stored procedures, not just the user-defined functions it currently supports. It's also very complicated to implement autonomous tx's in PostgreSQL for a variety of internal reasons related to its session and process model.
为了正确支持它们，它确实需要存储过程，而不仅仅是它当前支持的用户定义函数。由于与会话和进程模型相关的各种内部原因，在 PostgreSQL 中实现自治 tx 也非常复杂。
For now, use dblink as suggested by Bob.
现在，按照 Bob 的建议使用 dblink。
回答by shcherbak
For Postgresql 9.5 or newer you can use dynamic background workers provided by pg_background extension. It creates autonomous transaction. Please, refer the github pageof the extension. The sollution is better then db_link. There is a complete guide on Autonomous transaction support in PostgreSQL. There is a third way to start autonomous transaction in Postgres, but some patching neede. Please see Peter's Eisentraut patch proposalfor OracleDB-style transactions.
对于 Postgresql 9.5 或更高版本，您可以使用 pg_background 扩展提供的动态后台工作程序。它创建自治事务。请参考扩展的github 页面。解决方案比 db_link 更好。PostgreSQL 中有一个关于自治事务支持的完整指南。在 Postgres 中还有第三种启动自治事务的方法，但需要一些修补。请参阅 Peter针对 OracleDB 样式事务的 Eisentraut补丁建议。
