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

plpgsqlpostgresqlpostgresql-9.5transaction

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?

Best Answer

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:

Related Question