Date: Mon, 26 Sep 2022 05:44:31 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Do blocks support transaction control?
Message-ID: <YzEgH26TCMdAsdPQ@depesz.com>
Reply-To: depesz@depesz.com
References: <166394003436.654.15293085988078557076@wrigleys.postgresql.org>
 <b1b9330fed3738135aba8664a55a6fd8239985c8.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <b1b9330fed3738135aba8664a55a6fd8239985c8.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/YzEgH26TCMdAsdPQ%40depesz.com>
Precedence: bulk

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