From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] Transaction control in procedures |
Date: | 2018-01-19 13:31:30 |
Message-ID: | CANP8+j+jHZiD3aOsCYueQRi9LnTZ8RfnKc8WqbwqaHjQ-7FNFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 6 December 2017 at 22:34, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Dec 6, 2017 at 8:41 AM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> On 12/5/17 13:33, Robert Haas wrote:
>>> On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut
>>> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>>>> I think ROLLBACK in a cursor loop might not make sense, because the
>>>> cursor query itself could have side effects, so a rollback would have to
>>>> roll back the entire loop. That might need more refined analysis before
>>>> it could be allowed.
>>>
>>> COMMIT really has the same problem; if the cursor query has side
>>> effects, you can't commit those side effects piecemeal as the loop
>>> executed and have things behave sanely.
>>
>> The first COMMIT inside the loop would commit the cursor query. This
>> isn't all that different from what you'd get now if you coded this
>> manually using holdable cursors or just plain client code. Clearly, you
>> can create a mess if the loop body interacts with the loop expression,
>> but that's already the case.
>>
>> But if you coded something like this yourself now and ran a ROLLBACK
>> inside the loop, the holdable cursor would disappear (unless previously
>> committed), so you couldn't proceed with the loop.
>>
>> The SQL standard for persistent stored modules explicitly prohibits
>> COMMIT and ROLLBACK in cursor loop bodies. But I think people will
>> eventually want it.
>
> The may want it, but silently promoting all cursors to held ones is
> not the way to give it to them, unless we narrow it down the the
> 'for-loop derived cursor' only.
I don't think we should do that automatically for all cursors, but it
seems clear that we would want that iff the loop contains COMMIT or
ROLLBACK.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2018-01-19 13:42:56 | Re: [HACKERS] Transaction control in procedures |
Previous Message | Antonin Houska | 2018-01-19 13:14:47 | Re: [HACKERS] Secondary index access optimizations |