在执行 postgreql 函数时提交事务 [英] Committing transactions while executing a postgreql Function
问题描述
我有 Postgresql 函数,它必须将大约 150 万条数据插入到一个表中.我想要的是我想看到表被每一条记录插入填充.目前,当我尝试使用大约 1000 条记录时发生的情况,只有在执行完整函数后才会填充 get.如果我中途停止该函数,则不会填充任何数据.即使在插入一定数量的记录后停止,如何使记录提交?
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?
推荐答案
这可以使用 dblink 来完成.我展示了一个示例,其中提交了一个插入,您需要添加 while 循环逻辑并提交每个循环.您可以 http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html
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
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;
这篇关于在执行 postgreql 函数时提交事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!