Re: Autonomous Transaction (WIP)

2014-07-01 10:38 GMT+02:00 Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com>:

> On 01 July 2014 12:26, Pavel Stehule Wrote:
>
>
>
> >>Have you checked the discussion in Developer meeting notes. Please
>
> >>check the same at below link:
>
> >>
> http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions
>
>
>
> >Are these notes still valid?
>
> >* Why autonomous transaction should be close to functions? We can
> implement AT as first step and next step can be implementation of
> integration AT to stored procedures.
>
> We have implemented AT on the line of sub-transaction. Also we have
> integrated AT with stored procedure i.e. we can create an autonomous
> transaction inside the store procedure, which can be also committed.
>
> >* When autonomous transaction is independent on parent transaction, then
> locks parent and autonomous transaction should be in conflict
>
> Yes our implementation makes the autonomous transaction independent of
> main transaction and hence as per our design parent (main) transaction and
> autonomous may get conflicted. For which we have implemented deadlock
> detection mechanism between autonomous transaction and its parent
> transaction.
>
> > I though about integration to PL/pgSQL and I don't think so close
> integration between autonomous transaction and procedure is optimal. More
> practical is design so autonomous transaction is similar to subtransaction.
>
> Yes as mentioned above, our implementation of autonomous transaction is on
> track of subtransaction.
>

ok

> >Then we can simply wrote some code like
>
> > BEGIN
>
> > .. some code
>
> > WHEN OTHERS THEN
>
> > .. I would to write permanently to log
>
> > BEGIN AUTONOMOUS
>
> > INSERT INTO log VALUES(..);
>
> > WHEN OTHERS
>
> > RAISE WARNING 'Cannot to write to log ..';
>
> > RAISE EXCEPTION ' ...' forward up exception from autonomous
> transaction to parent transaction
>
> > END
>
> > END;
>
> >Now I am thinking so PL/SQL design of autonomous transactions is
> relatively limited and is not best to follow it.
>
> With our approach, we can use autonomous transaction in procedure as given
> below:
>
> BEGIN
>
> .. some code
>
> WHEN OTHERS THEN
>
> .. I would to write permanently to log
>
> *START AUTONOMOUS TRANSACTION*
>
> INSERT INTO log VALUES(..);
>
> *COMMIT: *
>
> WHEN OTHERS
>
> RAISE WARNING 'Cannot to write to log ..';
>
> RAISE EXCEPTION ' ...' forward up exception from autonomous
> transaction to parent transaction
>
> END
>
> END;
>
I don't like this design (really) - it can be used in later implementation
of procedures - but I don't like a explicit transaction manipulation in
functions. It is Oracleism (and this part I don't want to follow, because
Oracle design is not lucky) - and it is one reason, why Oracle SP are
significantly complex than PostgreSQL SP. After all I am thinking so
PostgreSQL relation between transactions and procedures are better, simply
for usage, simply for learning. But it is little bit different topic.

Regards

Pavel

> Please let me know if I have missed to answer any of your queries.
>
> Thanks and Regards,
>
> Kumar Rajeev Rastogi
>
>
>
>
>