pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedDo blocks support transaction control?
3+ messages / 3 participants
[nested] [flat]
* Do blocks support transaction control?
@ 2022-09-23 13:33 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2022-09-23 13:33 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: depesz@depesz.com
The following documentation comment has been logged on the website:
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Do blocks support transaction control?
@ 2022-09-24 15:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2022-09-24 15:14 UTC (permalink / raw)
To: depesz@depesz.com; pgsql-docs@lists.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Do blocks support transaction control?
@ 2022-09-26 03:44 hubert depesz lubaczewski <depesz@depesz.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: hubert depesz lubaczewski @ 2022-09-26 03:44 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-09-26 03:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-23 13:33 Do blocks support transaction control? PG Doc comments form <noreply@postgresql.org>
2022-09-24 15:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-09-26 03:44 ` hubert depesz lubaczewski <depesz@depesz.com>
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