Re: what we need to use postgresql in the enterprise

First I would be happy to help get these things in postgresql.  I'm not a
c/c++ programming guru and would have to learn a bit before I could do the
coding.  I would be happy to test or talk about what's needed or anything
like that.  Or just keep the fire burning on these issues that I think are
keeping postgresql from being the database to use for almost everything.  I
mean it's great when other things get optimized and fixed, but to me the
issues I talk about are show stoppers(atleast in my eyes).  I releize that
Oracle software costs easily 500K for a large shop. And Postgresql is 0$.
But if we don't look at what is missing how will things ever improve.  I
have the view point of using Oracle for over 7 years and Postgresql for
about a year off and on for learning puproses.  I love Postgresql and hope
my viewpoint coming from Oracle can help improve things.

My point of this thread is to say why we can't use Postgresql in place of
Oracle at this time because of xy and z. Maybe in years to come we can.  I
would rather use Postgresql if I can.


If some of these things are coming that's great.  From my point of view
they aren't there, therefore I can't use them.



I also think pl/pgsql is a better choice for stored procedures in general
depending on the goal of the procedure.  If the procedure is working with
the database pl/pgsql seems to be the choice.  I would rather use pl/pgsql
and not have some perl and some python or some other language in my stored
procedures. This may be more my opinion then the best way of doing things.
But I like to keep things simple for any future person going to maintain
the system.

I can see where you are coming from if you haven't used Oracle's exception
handling.   Here is a snippet of an exception handling block in one of my
stored procedures.  As you can see I don't need to check for errors after
each piece of code.  The exception block handles all exception handling.  I
would say it's very clean and handles errors very well.  this is a simple
example but you can get the point.

BEGIN

code logic here
code logic here
code logic here
code logic here
code logic here

EXCEPTION
/*  Not all of the non nullable fields passed had values  */
   WHEN e_mandatory_fields_null THEN
      r_return_cd   := pkg_0100.g_return_missing_fields;
      pkg_0100.sp_get_error(r_return_cd,r_return_type,r_return_msg);
/*  Default error code called for all other errors  */
   WHEN others THEN
      pkg_0099.g_sql_code := SQLCODE;
      pkg_0099.g_sql_error_msg := SQLERRM;
      r_return_cd   := pkg_0001.g_return_failure;
      pkg_0001.sp_log_error(r_return_cd,r_return_type,r_return_msg);

END;


I can see how you might get use to something like my example below, but to
be honest once you have used Oracle style exception handling it's very hard
to look at anything with a grain of salt.  Just look how ugly this is
compared to my Oracle exception block.  Now imagine a stored procedure with
2500+ lines of code.  For short very simple 50 lines or less I could live
with postgresql exception handling on some levels. But once the lines start
adding up it's not a good way of doing things.

postgresql error checking if I understand correctly.
code logic here
check for error
code logic here
check for error
code logic here
check for error
code logic here
check for error

Bob Henkel          651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125



                         
                      "Chris Travers"
                         
                      <chris@travelamer        To:       "Robert Treat" <xzilla@users.sourceforge.net>,
<Bob.Henkel@hartfordlife.com>            
                      icas.com>                cc:       <pgsql-general@postgresql.org>
                         
                                               Subject:  Re: [GENERAL] what we need to use postgresql in the enterprise
                         
                      01/13/2004 02:30
                         
                      AM
                         

                         

                         




I am a little confused here.  I agree that there are points mentioned here
that need work, but correct me if I am wrong....
> On Friday 09 January 2004 14:48, Bob.Henkel@hartfordlife.com wrote:
<snip>
> > 1.  Need commit roll back in pl/pgsql much like Oracle does
> > 2.  Need exception handling in pl/pgsql must like Oracle does
> > 3.  A>Need sub transactions .  B>And if an inner transactions fails it
> > should not cause all others to fail.  If #2 was robust enough than #3 B
> > might not be an issue.

OK.  I am not sure about Oracle's exception handling and commit rollback,
as
my experience there is limited, but the subtransaction issue is being
worked
on.

> > 1. It's a must if you have long running complicated and time consuming
> > batch processing.  There is no reason why one should say do all of
commit
> > and rollbacks from the client.

What is described here is a scenario where a stored proceedure wraps
several
transactions.  This is a feature many people have asked for and it is in
the
Todo list, but so far there is no word on any ETA.  Tom Lane has described
it as a complicated problem.

> > 2. Without this you can't trust complicated code as far as I'm
concerned.
> I
> > need to log some errors and continue processing and for others log and
> exit
> > which I think you can do now in pl/pgsql.  Point being pl/pgsql
exception
> > handling is almost nonexistent at best.
> >
Hmmm....  Here is where you have lost me.  Can anyone tell me why RAISE
WARNING doesn't work for the errors that need to continue and RAISE
EXCEPTION doesn't work for the errors that need termination.  Also, if you
need customized logging, you could always use pl/perlu for to create a more
complex logging system that doesn't log to the same PostgreSQL log.  If the
results are written to an outside file, they would be logged independent of
whether the transaction was committed or rolled back.  This would be
trivial
to impliment if the requirements weren't large.

Best Wishes,
Chris Travers








*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************