From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Juan Pablo L(dot)" <jpablolorenzetti(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: rollback in function |
Date: | 2015-02-24 04:39:03 |
Message-ID: | CAFj8pRAdD9-jQRPYDN02ShfMz1tAn8U1N3bCR4akDR=MmVGR8A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
2015-02-19 21:42 GMT+01:00 Juan Pablo L. <jpablolorenzetti(at)hotmail(dot)com>:
> Hello, i have created a function (in C) that receives an array that
> contains tuples of ID's and values.
>
> The function is to execute updates on each ID assigning the value, but if
> one of these operation fails (does not meet certain criteria)
> inside the function i would like to rollback and leave everything
> untouched, in case other ID;s were already updated previously,
> and come back to the caller and inform about it.
>
> I have read all over that it is not posible to do rollback inside a
> function because each function is executed inside a transaction
> so inside the function you dont have control over BEGIN/ROLLBACK, but i m
> sure there is a way to do this, can anyone please give me a hint
> how this is accomplished ? thank you!!!
>
There are two possibilities
a) simple way - raise a exception - it is usual way in Postgres - when you
diagnose any problem, then raise exception. After exception only rollback
is allowed.
b) start subtransaction, process your work and on the end do commit or
rollback.
In Postgres you can start or commit only transactions, that you are started
explicitly.
Subtransaction is started via BeginInternalSubTransaction function - you
can find few examples in postgres source code - probably most simple is in
interpret plpgsql pl_exec.c
Regards
Pavel
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2015-02-24 06:40:17 | Re: : Getting error while starting the server |
Previous Message | Shanker Singh | 2015-02-24 02:18:18 | Re: parallel dump fails to dump large tables |