Re: Do blocks support transaction control?

On Sat, Sep 24, 2022 at 05:14:42PM +0200, Laurenz Albe wrote:
> On Fri, 2022-09-23 at 13:33 +0000, PG Doc comments form wrote:
> > Page: https://www.postgresql.org/docs/14/sql-do.html
> > Description:
> >
> > Apparently now DO blocks support COMMIT; - which make them more like
> > procedures than functions.
> >
> > Tried it with:
> >
> > create table z (a int4, b int4);
> > insert into z (a,b) select i, i from generate_Series(1,10) i;
> > do $$
> > declare
> > begin
> > update z set b = 2;
> > commit;
> > perform pg_sleep(120);
> > end;
> > $$ language plpgsql;
> >
> > And while it was running, in another psql sessions, I:
> >
> > 1. could see b= 2
> > 2. could update any of the rows in z.
> >
> > Is it documented anywhere? DO docs say that do is like function, which it
> > doesn't seem to be?
>
> The documentation says:
>
> If DO is executed in a transaction block, then the procedure code cannot execute
> transaction control statements. Transaction control statements are only allowed
> if DO is executed in its own transaction.
>
> That sentence would not make sense if COMMIT were not allowed in a DO statement.
> So it is not spelled out, but implicitly clear.

Sorry, missed that, focused too much on the earlier part. Thanks a lot.

Best regards,

depesz