Transaction execution on postgreql function

Question

I have a Postgresql function that should INSERT about 1.5 million data into a table. I want me to see that the table is filled with each insertion of records. Currently, what happens when I try to say about 1000 records, get is filled only after the full function is executed. If I stop the function halfway, the data will not be filled. How can I make a record perfect even if I stop after a certain number of records have been inserted?

Answer 1

This can be done using dblink. I showed an example in which one insert is fixed, you will need to add your while loop logic and commit each 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; 

Answer 2

What you ask for is usually called an autonomous transaction.

PostgreSQL does not support offline transactions at this time (9.4).

For proper support, they really need stored procedures, not just user-defined functions that are currently supported. It is also very difficult to implement standalone tx in PostgreSQL for many internal reasons related to its session and process model.

For now, use dblink, as suggested by Bob.

Answer 3

For Postgresql 9.5 or later, you can use the dynamic background workers provided by the pg_background extension. It creates an autonomous transaction. Please refer to the github extension page . The solution is better than db_link. There is a complete guide to Offline Transaction Support in PostgreSQL . There is a third way to start a standalone transaction in Postgres, but some fixes are needed. Please see the Peter Eisentraut suggestion for a fix for OracleDB-style transactions.