Re: Autonomous Transaction is back

On Tue, Aug 4, 2015 at 4:12 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com> wrote:
> On 03 August 2015 18:40, Merlin Moncure [mailto:mmoncure(at)gmail(dot)com] Wrote:
>>On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi
>><rajeev(dot)rastogi(at)huawei(dot)com> wrote:
>>> On 31 July 2015 23:10, Robert Haas Wrote:
>>>>I think we're going entirely down the wrong path here. Why is it ever
>>useful for a backend's lock requests to conflict with themselves, even
>>with autonomous transactions? That seems like an artifact of somebody
>>else's implementation that we should be happy we don't need to copy.
>>>
>>> IMHO, since most of the locking are managed at transaction level not
>>backend level and we consider main & autonomous transaction to be
>>independent transaction, then practically they may conflict right.
>>> It is also right as you said that there is no as such useful use-cases
>>where autonomous transaction conflicts with main (parent) transaction.
>>But we cannot take it for granted as user might make a mistake. So at-
>>least we should have some mechanism to handle this rare case, for which
>>as of now I think throwing error from autonomous transaction as one of
>>the solution. Once error thrown from autonomous transaction, main
>>transaction may continue as it is (or abort main transaction also??).
>>
>>hm. OK, what's the behavior of:
>>
>>BEGIN
>> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>
>> BEGIN WITH AUTONOMOUS TRANSACTION
>> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>> END;
>>
>> RAISE EXCEPTION ...;
>>EXCEPTION ...
>>
>>END;
>
> It should throw an error (or something equivalent) as the second update will wait for record lock to get released, which in this case will not happen till second update finishes. So catch 22.

Yeah. Point being, from my point of view autonomous transactions have
to conflict with the master transaction (or any transaction really).
I agree the right course of action is to error out immediately...what
else could you do? There isn't even a deadlock in the classic sense
and allowing control to continue would result in indeterminate
behavior FWICT.

>>Also,
>>*) What do the other candidate implementations do? IMO, compatibility>>should be the underlying design principle.
>
> Oracle throws error in such case. But we can decide on what behavior we want to keep.

gotcha. makes sense.

>>*) What will the "SQL only" feature look like?
>
> Similar to PL as mentioned in your example, we can provide the "SQL only" feature also.
>
>>*) Is the SPI interface going to be extended to expose AT?
>
> I don’t think at this point that there is any need of exposing SPI interface for this.

Ok, how do AT work in a non-plpgsql ("SQL only") scenario? Are you
going to similarly extend BEGIN TRANSACTION?

merlin