7

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?

  • 1
    Try BEGIN or COMMIT in the function body. You'll get an exception, because that's not allowed (not possible). – Erwin Brandstetter Aug 19 '16 at 1:11
7

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:

  • Do we know whether this is still the case? I have a function which needs to loop several hundred times. The first loop takes 2 seconds after the 7th is close to an hour and I've not seen anything after the 10th loop. – Dennis Bauszus Oct 24 '17 at 15:01
1

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.

  • postgresql.org/docs/11/sql-do.html states 'Transaction control statements are only allowed if DO is executed in its own transaction.' This was of course not true with 9.5. OTOH with dblink you will open another transaction, so your COMMIT call there won't affect the calling transaction, if I'm not mistaken. – dezso Apr 25 at 9:20
  • My bad. Transaction control within "DO" was introduced in Postgresql-11. I just check on 10.4 still not working. – Dzhureedzh Apr 25 at 10:05
  • @dezso Thank you for pointing me to this, I was using dblink method even on PG11 servers. – Dzhureedzh Apr 25 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.