Re: committing inside cursor loop

On Tue, 13 Mar 2018 11:08:36 -0400
Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 3/6/18 07:48, Ildus Kurbangaliev wrote:
> > Although as was discussed before it seems inconsistent without
> > ROLLBACK support. There was a little discussion about it, but no
> > replies. Maybe the status of the patch should be changed to
> > 'Waiting on author' until the end of discussion.
>
> I'm wondering what the semantics of it should be.
>
> For example, consider this:
>
> drop table test1;
> create table test1 (a int, b int);
> insert into test1 values (1, 11), (2, 22), (3, 33);
>
> do
> language plpgsql
> $$
> declare
> x int;
> begin
> for x in update test1 set b = b + 1 where b > 20 returning a loop
> raise info 'x = %', x;
> if x = 2 then
> rollback;
> end if;
> end loop;
> end;
> $$;
>
> The ROLLBACK call in the first loop iteration undoes the UPDATE
> command that drives the loop. Is it then sensible to continue the
> loop?
>

I think that in the first place ROLLBACK was prohibited because of cases
like this, but it seems to safe to continue the loop when portal
strategy is PORTAL_ONE_SELECT.

--
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company