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

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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:

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

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:

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:

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: