From: Laurenz Albe <laurenz.albe@cybertec.at>
To: depesz@depesz.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: Do blocks support transaction control?
Date: Sat, 24 Sep 2022 17:14:42 +0200
Message-ID: <b1b9330fed3738135aba8664a55a6fd8239985c8.camel@cybertec.at> (raw)
In-Reply-To: <166394003436.654.15293085988078557076@wrigleys.postgresql.org>
References: <166394003436.654.15293085988078557076@wrigleys.postgresql.org>
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.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, depesz@depesz.com, pgsql-docs@lists.postgresql.org
Subject: Re: Do blocks support transaction control?
In-Reply-To: <b1b9330fed3738135aba8664a55a6fd8239985c8.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox