Re: Autonomous Transaction is back

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.

>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.

>*) 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.

Thanks and Regards,
Kumar Rajeev Rastogi