handling exceptions, really not simple... :-((

Started by Eagletabout 24 years ago2 messages
#1Eaglet
Aquil8@infinito.it

Hi 2 everybody!

I'm evaluating a database that supports our 3-tier solutions, in addition to
Oracle.
Postgres is at the moment the candidate n. 1, but I realized it's very hard
to translate this type of code (Orqcle PL/SQL):

FUNCTION F_EXT(par1, par2 ...) RETURN.... IS
BEGIN
SAVEPOINT spF_EXT;
...[instructions]
INSERT INTO...
UPDATE...
DELETE...
...[instructions]

SAVEPOINT spF_INT;
ret := F_INT(par1, par2 ...);

IF ret = 'KO' THEN
ROLLBACK TO spF_EXT;
ELSE IF ret IS NULL THEN
ROLLBACK TO spF_INT;
END IF;

...[instructions]
COMMIT;
EXCEPTION
WHEN.....
...[instructions]
END F_EXT;

The goal to achieve is to trap every kind of execution errors without trying
to prevent their occurrence.
In any case, I usually can't be sure the function (F_EXT) works correcty: an
exception could be lanched by an internal function (F_INT), I couldn't know
to estabilish what happens and where ...
By the way, the evironment is:
the browser requests a PHP page that connect to a Data Source (Oracle) and
lanch a Stored Procedure; after the execution by Oracle, PHP get the result
and returns data or a message to the client.
It's very important for me to solve the application logic in the Back-End of
the system (Oracle or Postgres): integrity and meaning of function aren't
only "commit all" or "rollback all", and when something of wrong happens, I
need to know the type of exception and when it occurred. In Oracle I can do
this, but in Postgres I've not yet found anything of similar.
Can anyone help me?
Thanks in advance...

Eaglet

#2Josh Berkus
josh@agliodbs.com
In reply to: Eaglet (#1)
Re: handling exceptions, really not simple... :-((

Eaglet,

In the future, please refrain from cross-posting on multiple PostgreSQL
lists. We get enough traffic without seeing the same message on 3
lists.

The goal to achieve is to trap every kind of execution errors without
trying
to prevent their occurrence.
In any case, I usually can't be sure the function (F_EXT) works
correcty: an
exception could be lanched by an internal function (F_INT), I
couldn't know
to estabilish what happens and where ...

Unfortunately, PG/plSQL does not currently support any programmed
exception handling. If an exception occurs in a pgplsql function, it
rolls back the entire function, including rolling back any calling
functions on a cascading basis.

This is partly due, as I understand it, to Postgres' lack of support for
nested transactions. Hopefully one of the Core Team will speak up with
the prognosis on fixing this particular issue. Right now, projects
needing sophisticated exception handling are being done in middleware
languages that support it, such as Java and Perl.

See Oracle <--> Postgres porting guides at
http://techdocs.postgresql.org/

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco