Re: [GENERAL] rollback in C functions
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
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
> 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 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: [GENERAL] rollback in C functions
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
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 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: [GENERAL] rollback in C functions
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 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: [GENERAL] rollback in C functions
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: [GENERAL] rollback in C functions
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 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 Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [GENERAL] rollback in C functions
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rollback in C functions
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rollback in C functions
Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour) On 19 February 2015 at 15:34, Alvaro Herrera 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 Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [GENERAL] rollback in C functions
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rollback in C functions
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 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: [GENERAL] rollback in C functions
> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
