Thread: rollback in C functions

rollback in C functions

From
Juan Pablo L
Date:
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!!!

Re: rollback in C functions

From
Chris Mair
Date:
> 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.




Re: rollback in C functions

From
Juan Pablo L
Date:
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.



Re: rollback in C functions

From
Alvaro Herrera
Date:
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


Re: rollback in C functions

From
Juan Pablo L
Date:
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

Re: rollback in C functions

From
Alvaro Herrera
Date:
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


Re: rollback in C functions

From
John R Pierce
Date:
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



Re: rollback in C functions

From
Juan Pablo L
Date:
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

Re: rollback in C functions

From
John R Pierce
Date:
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



Re: rollback in C functions

From
Juan Pablo L
Date:
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

Re: rollback in C functions

From
Juan Pablo L
Date:
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:

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);
     }

the  above call log_debug show that t is NULL. am i doing something wrong ? thanks!

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


Re: rollback in C functions

From
Juan Pablo L
Date:
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:
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:

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);
     }

the  above call log_debug show that t is NULL. am i doing something wrong ? thanks!

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