Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

Question

I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$.

$do$
BEGIN
    FOR yyyy in 2012..2016 THEN 
        EXECUTE $$COPY table$$||yyyy||$$ FROM 'E:\data\file$$||yyyy||$$.csv DELIMITER ',' CSV;$$;
    END LOOP;
END;
$do$ LANGUAGE plpgsql

This entire process should take about 15 hours and I'm hoping that all the imports won't be rolled back if there's an import error at some point.

IIRC COMMIT doesn't work within stored functions bc the entire function is treated as a single transaction.

From the documentation for $do$

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

I'm assuming this means that the entire $do$ is one transaction, and so commits within the block won't work. Am I correct?

Answer 1

No,

You can't control a transaction inside a plpgsql function (or anonymous block).

The only option that you have its creating a transaction outside the block, eg:

BEGIN;

DO $$
  -- function stuff

  -- but if you use a exception, you will force a rollback
  RAISE EXCEPTION 'message';
$$ LANGUAGE 'plpgsql';

COMMIT; -- OR ROLLBACK

BTW, DO BLOCKS have the same effect that functions who returns void.

Please, see more at the doc:

• https://www.postgresql.org/docs/current/static/sql-do.html

Answer 2

The only solution to commit within "DO" blocks (or functions) (for Postgresql version less then 11) is to use dblink connection to the same server an execute your queries there. Just keep in mind variables and temporary objects visibility.

more information about dblink Starting with Postgresql-11 transaction control from inside of "DO" block is available while "DO-block" is not running within other transaction.