Thread: how to force an insert before Raise Exception?

Hi.

I made a function that Raise exception with some conditions.
No problem with that, it is the goal.

My problem is that i want to do an INSERT into a log table before to raise
the exception. But RAISE EXCEPTION cancels my Insert.

How to force the insert to not being cancelled please?
Note that i want my exception to be raised , so i can't use:
EXCEPTION WHEN THEN

Thanks for your help.

example :
CREATE OR REPLACE FUNCTION "PrepareTrialLic"(userid integer)
BEGIN;
IF userid = 0 THEN
     --I want to force that insert !!!!!!!!!!!!!!!!!!
     INSERT INTO log_error(caller, description) VALUES('PrepareTrialLic',
'userid is 0');
     RAISE EXCEPTION 'userid can''t be equal to 0';
ENDIF;
END;


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-force-an-insert-before-Raise-Exception-tp4313283p4313283.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tuesday, April 19, 2011 6:39:23 am giova wrote:

> Hi.

>

> I made a function that Raise exception with some conditions.

> No problem with that, it is the goal.

>

> My problem is that i want to do an INSERT into a log table before to raise

> the exception. But RAISE EXCEPTION cancels my Insert.

>

> How to force the insert to not being cancelled please?

> Note that i want my exception to be raised , so i can't use:

> EXCEPTION WHEN THEN

Why not? From the docs:

"When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. As an example, consider this fragment: "

And instead of RAISE EXCEPTION use RAISE NOTICE in the exception clause.

>

> Thanks for your help.

>

--

Adrian Klaver

adrian.klaver@gmail.com

On Tue, Apr 19, 2011 at 4:39 PM, giova <giovainstall2@yahoo.fr> wrote:
> My problem is that i want to do an INSERT into a log table before to raise
> the exception. But RAISE EXCEPTION cancels my Insert.

That's the point of transaction, if it failed the data and any other
changes are rolled back.
You can use dblink (http://www.postgresql.org/docs/current/static/dblink.html),
which will issue a separate connection to database to simulate
autonomous transaction.

Regards,
Taras Kopets

On 19/04/11 21:39, giova wrote:
> Hi.
>
> I made a function that Raise exception with some conditions.
> No problem with that, it is the goal.
>
> My problem is that i want to do an INSERT into a log table before to raise
> the exception. But RAISE EXCEPTION cancels my Insert.
>
> How to force the insert to not being cancelled please

You can't have part of a transaction cancelled without cancelling all of
it. The INSERT is part of the transaction.

If you really need to insert a record in a log table before rolling back
a transaction, you need to use dblink to do the insert in another
transaction.

Personally, I'd enable PostgreSQL's CSV logging and then process the log
files, so you can include your logging info in the Pg logs. Using
logging tables will be way more hassle than it's worth.

--
Craig Ringer