Hi, PostgreSQL: Documentation: 13: DO <https://www.postgresql.org/docs/current/sql-do.html> Is it possible to run a DO block for multiple transactions ? I am not sure if i'll be able explain it more verbally, but
-- the entire DO block like a function block is a single tx postgres=# do $$ declare x bigint; begin for i in 1..10 loop select txid_current()::bigint into x; raise notice '%', x; end loop; end $$; NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 NOTICE: 779 DO -- is it possible for a DO block to execute multiple txs postgres=# create or replace procedure pp() as $$ declare x bigint; begin for i in 1..10 loop select txid_current()::bigint into x; commit; raise notice '%', x; end loop; end; $$ language plpgsql; CREATE PROCEDURE postgres=# call pp(); NOTICE: 781 NOTICE: 782 NOTICE: 783 NOTICE: 784 NOTICE: 785 NOTICE: 786 NOTICE: 787 NOTICE: 788 NOTICE: 789 NOTICE: 790 CALL one of the use case would be batch inserts, but from within a single psql session create table t(id int primary key); postgres=# truncate table t; TRUNCATE TABLE postgres=# do $$ declare valuelist int[] := ARRAY[1,2,3,4,5,1]; -- purposely inserting duplicate that would rollback everything declare i int; begin for i in select k from unnest(valuelist) p(k) loop insert into t values(i); raise notice 'trying to insert %', i; end loop; end; $$; NOTICE: trying to insert 1 NOTICE: trying to insert 2 NOTICE: trying to insert 3 NOTICE: trying to insert 4 NOTICE: trying to insert 5 ERROR: duplicate key value violates unique constraint "t_pkey" DETAIL: Key (id)=(1) already exists. CONTEXT: SQL statement "insert into t values(i)" PL/pgSQL function inline_code_block line 6 at SQL statement --- so everything got rolled back, as duplicate key, table t empty postgres=# create or replace procedure proc_ins() as $$ declare valuelist int[] := ARRAY[1,2,3,4,5,1]; declare i int; begin for i in select k from unnest(valuelist) p(k) loop insert into t values(i); raise notice 'trying to insert %', i; commit; -- explict commit, every insert in a new tx. end loop; end; $$ language plpgsql; CREATE PROCEDURE postgres=# call proc_ins(); NOTICE: trying to insert 1 NOTICE: trying to insert 2 NOTICE: trying to insert 3 NOTICE: trying to insert 4 NOTICE: trying to insert 5 ERROR: duplicate key value violates unique constraint "t_pkey" DETAIL: Key (id)=(1) already exists. CONTEXT: SQL statement "insert into t values(i)" PL/pgSQL function proc_ins() line 6 at SQL statement --only the erroneous data insert failed, but earlier committed data was successful. postgres=# table t; id ---- 1 2 3 4 5 (5 rows) Ok, there might be better ways to do this using insert on conflict, handling exceptions etc, but I hope you got my point. I would go on and say DO block to waste transactions to simulate wraparound with minimum concurrent connections, but that would divert the discussion, hence just keeping it simple. you can point me to docs, if i am missing the obvious reference for what a DO serves or what it was created for. -- Thanks, Vijay Mumbai, India
