Thread: DO like block for for anonymous procedures
Hi,
PostgreSQL: Documentation: 13: DO
--
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
please ignore, i overlooked the obvious.
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; commit;
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
postgres=# table t;
id
----
1
2
3
4
5
(5 rows)
sorry.