Thread: rollback in C functions
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!!!
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!!!
> 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 infor Hi, I think you want ereport(), here is an example: http://www.postgresql.org/docs/9.3/static/xfunc-c.html Bye, Chris.
Hi Thanks Chris, yes i have already tested ereport and even made up my own sql state to report and error but from the application i can not access this error code directly (through the PQexec,
PQresultErrorField,etc functions because the PGresult returns NULL) but i have to register a callback function that will be called with this error code with the idea to format the error message so i could intercept this and do something, but this does not allow me to have direct access to the flow where this is all happening .. i hope it is clear ... On 19 February 2015 at 15:02, Chris Mair <chris@1006.org> wrote:
> 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 infor
Hi,
I think you want ereport(), here is an example:
http://www.postgresql.org/docs/9.3/static/xfunc-c.html
Bye,
Chris.
Juan Pablo L wrote: > Hello, i have created a function (in C) that receives an array that > contains tuples of ID's and values. Why are you writing a C function? Sounds like you could accomplish the same with a plpgsql function, with much less effort. > 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. Do you want previous updates to remain in place, or do you want to roll them back too? This is not clear. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour)
On 19 February 2015 at 15:34, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Juan Pablo L wrote:
> Hello, i have created a function (in C) that receives an array that
> contains tuples of ID's and values.
Why are you writing a C function? Sounds like you could accomplish the
same with a plpgsql function, with much less effort.
> 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.
Do you want previous updates to remain in place, or do you want to roll
them back too? This is not clear.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Juan Pablo L wrote: > Hi, i want previous updates to rollback ... like nothing happened (a normal > begin/rollback behaviour) Ah, so ereport() is exactly what you want, like Chris Mair said. Assuming you wrote it correctly, you should see the ERROR line in the server logs (set log_message_verbosity=verbose in postgresql.conf to see the full details such as the sqlstate etc). Did you notice you must add an extra ( before errmsg and other sub-calls within the ereport call? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2/19/2015 1:41 PM, Juan Pablo L wrote: > Hi, i want previous updates to rollback ... like nothing happened (a > normal begin/rollback behaviour) so thrown an exception. The actual rollback has to be invoked by the client application program, which should catch the error thrown by the query that causes the exception. In pl/pgsql this would be easy, RAISE SQLSTATE 'string'; but i'm not sure how you'd do this in a C function. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc
On 19 February 2015 at 15:57, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Juan Pablo L wrote:
> Hi, i want previous updates to rollback ... like nothing happened (a normal
> begin/rollback behaviour)
Ah, so ereport() is exactly what you want, like Chris Mair said.
Assuming you wrote it correctly, you should see the ERROR line in the
server logs (set log_message_verbosity=verbose in postgresql.conf to see
the full details such as the sqlstate etc). Did you notice you must add
an extra ( before errmsg and other sub-calls within the ereport call?
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2/19/2015 2:02 PM, Juan Pablo L wrote: > Thank you Alvaro, i m afraid ereport seems to be the way, that it is > complicated to catch this error code in the code of the caller. cause > you have to use a callback etc etc a query that triggers ereport(ERROR,....) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thank you, i will try this, honestly i was checking if PGResult is NULL, when i trigger the exception i always get NULL so i did not any further but i will try this .....
On 19 February 2015 at 16:22, John R Pierce <pierce@hogranch.com> wrote:
On 2/19/2015 2:02 PM, Juan Pablo L wrote:Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc
a query that triggers ereport(ERROR,....) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
i tried this but the call to PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) is returning NULL, this is what trigger the exception in the function code:
ereport(ERROR,(errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),errmsg("Plan with id %s does not allow balance with id %s",plan_id,in_balanceid)));
and this is the caller code:ereport(ERROR,(errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),errmsg("Plan with id %s does not allow balance with id %s",plan_id,in_balanceid)));
if(PQresultStatus(pg_res) == PGRES_FATAL_ERROR)
{
char *t = PQresultErrorField(pg_res,PG_DIAG_SQLSTATE);
log_debug("[C%03dH%03d] PQres returned NULL: %s",handler->my_connection->id,handler->id,t);
}
On 19 February 2015 at 16:27, Juan Pablo L <jpablolorenzetti@gmail.com> wrote:
Thank you, i will try this, honestly i was checking if PGResult is NULL, when i trigger the exception i always get NULL so i did not any further but i will try this .....On 19 February 2015 at 16:22, John R Pierce <pierce@hogranch.com> wrote:On 2/19/2015 2:02 PM, Juan Pablo L wrote:Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc
a query that triggers ereport(ERROR,....) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Just an update, it was making a mistake, the execution of PQexecute, to execute the query, was wrapped in a function called "PGresult *db_execute(....)" that was returning the PGresult as NULL, i completely forgot this, inside that function the exception was being caught and discarded so by the time i got the PGresult pointer back in the caller it contained nothing so trying to read any error from it at that point was useless.
Just to clarify and for future reference, the proposal to use ereport worked perfectly as proposed by the all members. thank you very much!!
On 19 February 2015 at 16:42, Juan Pablo L <jpablolorenzetti@gmail.com> wrote:
the above call log_debug show that t is NULL. am i doing something wrong ? thanks!i tried this but the call to PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) is returning NULL, this is what trigger the exception in the function code:and this is the caller code:
ereport(ERROR,(errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),errmsg("Plan with id %s does not allow balance with id %s",plan_id,in_balanceid)));
if(PQresultStatus(pg_res) == PGRES_FATAL_ERROR)
{
char *t = PQresultErrorField(pg_res,PG_DIAG_SQLSTATE);
log_debug("[C%03dH%03d] PQres returned NULL: %s",handler->my_connection->id,handler->id,t);
}On 19 February 2015 at 16:27, Juan Pablo L <jpablolorenzetti@gmail.com> wrote:Thank you, i will try this, honestly i was checking if PGResult is NULL, when i trigger the exception i always get NULL so i did not any further but i will try this .....On 19 February 2015 at 16:22, John R Pierce <pierce@hogranch.com> wrote:On 2/19/2015 2:02 PM, Juan Pablo L wrote:Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc
a query that triggers ereport(ERROR,....) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general