执行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屋！