Thread: what we need to use postgresql in the enterprise

what we need to use postgresql in the enterprise

From
Bob.Henkel@hartfordlife.com
Date:
I write this to tell you why we won't use postgresql even though we wish we
could at a large company.  Don't get me wrong I love postgresql in many
ways and for many reasons , but fact is fact.  If you need more detail I
can be glad to prove all my points.  Our goal is to make logical systems.
We don't want php,perl, or c++ making all the procedure calls and having
the host language to be checking for errors and handleing all the
transactions commits and rollbacks.  That is not very logical in a large
batch system.  Also please don't tell me to code the changes myself.  I'm
not at that part of the food chain.  That is to low level for me and I
don't have the time to put that hat on.  I build the applications that use
the database systems.  Also please feel free to correct me in any area I
don't know everything I'm just stating my opinion here

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.

With those two things I could accomplish pretty much everything with
postgresql that we're currently doing in Oracle.

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. Our current batch system gets fired off by
running some sql scripts that start an entry point into the batch system.
Once the first stored procedure is called it will call all the rest.  This
encapsulates all logic and processing in the database where it belongs.
There is no client traffic because once that first script kicks off there
is no outside process running , just our pl/sql.  Now I'm not a postgresql
expert at all but when I read up on it looks like this is something you
can't accomplish and I see no word of this being worked on.

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.

3. We use this all the time in pl/sql and we need to. To write this off as
not need is wrong and will not get postgresql to where it can be(AT THE
TOP).






*************************************************************************
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. 
*************************************************************************


Re: what we need to use postgresql in the enterprise

From
Robert Treat
Date:
I think your pretty much on target with the below. It is possible to work
around these issues on some level, but I can see how that might get unwieldly
in a hurry.  While I won't tell you to "go code it if you need it", I might
ask that you consider what your paying in Oracle licenses and think about
spending that money to hire someone to develop those features in PostgreSQL,
I'm thinking you'd save money in the long run.

Robert Treat

On Friday 09 January 2004 14:48, Bob.Henkel@hartfordlife.com wrote:
> I write this to tell you why we won't use postgresql even though we wish we
> could at a large company.  Don't get me wrong I love postgresql in many
> ways and for many reasons , but fact is fact.  If you need more detail I
> can be glad to prove all my points.  Our goal is to make logical systems.
> We don't want php,perl, or c++ making all the procedure calls and having
> the host language to be checking for errors and handleing all the
> transactions commits and rollbacks.  That is not very logical in a large
> batch system.  Also please don't tell me to code the changes myself.  I'm
> not at that part of the food chain.  That is to low level for me and I
> don't have the time to put that hat on.  I build the applications that use
> the database systems.  Also please feel free to correct me in any area I
> don't know everything I'm just stating my opinion here
>
> 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.
>
> With those two things I could accomplish pretty much everything with
> postgresql that we're currently doing in Oracle.
>
> 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. Our current batch system gets fired off by
> running some sql scripts that start an entry point into the batch system.
> Once the first stored procedure is called it will call all the rest.  This
> encapsulates all logic and processing in the database where it belongs.
> There is no client traffic because once that first script kicks off there
> is no outside process running , just our pl/sql.  Now I'm not a postgresql
> expert at all but when I read up on it looks like this is something you
> can't accomplish and I see no word of this being worked on.
>
> 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.
>
> 3. We use this all the time in pl/sql and we need to. To write this off as
> not need is wrong and will not get postgresql to where it can be(AT THE
> TOP).
>
>
>
>
>
>
> *************************************************************************
> PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is
> for the exclusive use of addressee and may contain proprietary,
> 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, please notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> *************************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: what we need to use postgresql in the enterprise

From
Bob.Henkel@hartfordlife.com
Date:
I couldn't agree with you more.  I'm just a developer in a very large
company and getting anyone to listen and then understand that logic would
be a nightmare to say the least.  If it was my company I would put money
toward those issues.






                           
                      Robert Treat
                           
                      <xzilla@users.sourc        To:       Bob.Henkel@hartfordlife.com, pgsql-general@postgresql.org
                           
                      eforge.net>                cc:
                           
                                                 Subject:  Re: [GENERAL] what we need to use postgresql in the
enterprise                          
                      01/12/2004 12:45 AM
                           

                           

                           




I think your pretty much on target with the below. It is possible to work
around these issues on some level, but I can see how that might get
unwieldly
in a hurry.  While I won't tell you to "go code it if you need it", I might

ask that you consider what your paying in Oracle licenses and think about
spending that money to hire someone to develop those features in
PostgreSQL,
I'm thinking you'd save money in the long run.

Robert Treat

On Friday 09 January 2004 14:48, Bob.Henkel@hartfordlife.com wrote:
> I write this to tell you why we won't use postgresql even though we wish
we
> could at a large company.  Don't get me wrong I love postgresql in many
> ways and for many reasons , but fact is fact.  If you need more detail I
> can be glad to prove all my points.  Our goal is to make logical systems.
> We don't want php,perl, or c++ making all the procedure calls and having
> the host language to be checking for errors and handleing all the
> transactions commits and rollbacks.  That is not very logical in a large
> batch system.  Also please don't tell me to code the changes myself.  I'm
> not at that part of the food chain.  That is to low level for me and I
> don't have the time to put that hat on.  I build the applications that
use
> the database systems.  Also please feel free to correct me in any area I
> don't know everything I'm just stating my opinion here
>
> 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.
>
> With those two things I could accomplish pretty much everything with
> postgresql that we're currently doing in Oracle.
>
> 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. Our current batch system gets fired off by
> running some sql scripts that start an entry point into the batch system.
> Once the first stored procedure is called it will call all the rest.
This
> encapsulates all logic and processing in the database where it belongs.
> There is no client traffic because once that first script kicks off there
> is no outside process running , just our pl/sql.  Now I'm not a
postgresql
> expert at all but when I read up on it looks like this is something you
> can't accomplish and I see no word of this being worked on.
>
> 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.
>
> 3. We use this all the time in pl/sql and we need to. To write this off
as
> not need is wrong and will not get postgresql to where it can be(AT THE
> TOP).
>
>
>
>
>
>
> *************************************************************************
> PRIVILEGED AND CONFIDENTIAL: This communication, including attachments,
is
> for the exclusive use of addressee and may contain proprietary,
> 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, please
notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> *************************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL







*************************************************************************
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. 
*************************************************************************


Re: what we need to use postgresql in the enterprise

From
Jeff Eckermann
Date:
--- Bob.Henkel@hartfordlife.com wrote:
> I write this to tell you why we won't use postgresql
> even though we wish we
> could at a large company.  Don't get me wrong I love
> postgresql in many
> ways and for many reasons , but fact is fact.  If
> you need more detail I
> can be glad to prove all my points.  Our goal is to
> make logical systems.
> We don't want php,perl, or c++ making all the
> procedure calls and having
> the host language to be checking for errors and
> handleing all the
> transactions commits and rollbacks.  That is not
> very logical in a large
> batch system.  Also please don't tell me to code the
> changes myself.  I'm
> not at that part of the food chain.  That is to low
> level for me and I
> don't have the time to put that hat on.  I build the
> applications that use
> the database systems.  Also please feel free to
> correct me in any area I
> don't know everything I'm just stating my opinion
> here
>
> 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.

#1 & #3 both refer to the same thing, i.e. nested
transactions.  Alvaro Herrera has been working on this
for some time, and recently stated on (I think) the
pgsql-hackers list that he intended to have nested
transactions ready for the next release of PostgreSQL.
 On the other hand, Tom Lane recently responded to a
question about nested transactions by warning about
the complexity of the problems needing to be overcome
to make that happen, and expressing doubt about an
early solution.  So you could say that the status is
unclear.  A question on the -hackers list would
probably get more information.

Agreed that Oracle-style exception handling in
pl/pgsql would be a good thing.  If I understand
things correctly, the new error codes scheme in
PostgreSQL version 7.4 makes that easier to implement
than before.  But I am not aware of anyone working on
it.

*** Note To Developers ***
Adding Oracle-style exception handling to pl/pgsql
would greatly ease the migration path from Oracle to
PostgreSQL, and could easily result in many more
instances of Postgres being used for enterprise apps
that are now using Oracle.  But I'm not up to the
task, so I'm flagging it here for anyone else who
might want to take a crack at it.

>
> With those two things I could accomplish pretty much
> everything with
> postgresql that we're currently doing in Oracle.
>
> 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. Our current batch
> system gets fired off by
> running some sql scripts that start an entry point
> into the batch system.
> Once the first stored procedure is called it will
> call all the rest.  This
> encapsulates all logic and processing in the
> database where it belongs.
> There is no client traffic because once that first
> script kicks off there
> is no outside process running , just our pl/sql.
> Now I'm not a postgresql
> expert at all but when I read up on it looks like
> this is something you
> can't accomplish and I see no word of this being
> worked on.
>
> 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.
>
> 3. We use this all the time in pl/sql and we need
> to. To write this off as
> not need is wrong and will not get postgresql to
> where it can be(AT THE
> TOP).
>
>
>
>
>
>
>
*************************************************************************
> PRIVILEGED AND CONFIDENTIAL: This communication,
> including attachments, is for the exclusive use of
> addressee and may contain proprietary, 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,
> please notify the sender immediately by return
> e-mail, delete this communication and destroy all
> copies.
>
*************************************************************************
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

Re: what we need to use postgresql in the enterprise

From
"Chris Travers"
Date:
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


Re: what we need to use postgresql in the enterprise

From
"Chris Travers"
Date:
From: <Bob.Henkel@hartfordlife.com>
> 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 am in the "keep the fire burning" camp, though if I had the need and the
financing, I would add features as I needed them.

> I love Postgresql and hope
> my viewpoint coming from Oracle can help improve things.

I think that this is a perspective much valued on the list.  I have no
Oracle experience, but it is still the tarket to beat.
> 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.

In general I am inclined to agree.  However, there are a few exceptions
(mostly with untrusted languages, such as Pl/PerlU).  They include:
1:  The ability to access the system outside the database.  For example,
writing a logging function in PL/Perlu would allow you to log to a file, and
when this is logged, it will happen regardless of whether the transaction
commits or aborts (i.e. occurs outside the space of the normal transaction).
This could be used in creating a database application error log separate
from the PostgreSQL log.
2:  The ability to do text processing or other advanced features not easily
done in PLPGSQL.
3:  The ability to use network services, such as email, jabber, etc.

Note that you can do all these things in C functions as well, but I would
rather write it in Perl than C :-)

>
> 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;
>
>
Ok.  I understand.  This is extremely useful.  I agree that this should be
done.  Is it on the ToDo?

Best Wishes,
Chris Travers


Re: what we need to use postgresql in the enterprise

From
"Uwe C. Schroeder"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 14 January 2004 11:05 pm, Chris Travers wrote:
> From: <Bob.Henkel@hartfordlife.com>
>
> > 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 am in the "keep the fire burning" camp, though if I had the need and the
> financing, I would add features as I needed them.
>
> > I love Postgresql and hope
> > my viewpoint coming from Oracle can help improve things.
>
> I think that this is a perspective much valued on the list.  I have no
> Oracle experience, but it is still the tarket to beat.
>
> > 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.
>
> In general I am inclined to agree.  However, there are a few exceptions
> (mostly with untrusted languages, such as Pl/PerlU).  They include:
> 1:  The ability to access the system outside the database.  For example,
> writing a logging function in PL/Perlu would allow you to log to a file,
> and when this is logged, it will happen regardless of whether the
> transaction commits or aborts (i.e. occurs outside the space of the normal
> transaction). This could be used in creating a database application error
> log separate from the PostgreSQL log.
> 2:  The ability to do text processing or other advanced features not easily
> done in PLPGSQL.
> 3:  The ability to use network services, such as email, jabber, etc.
>
> Note that you can do all these things in C functions as well, but I would
> rather write it in Perl than C :-)
>
> > 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;
>
> Ok.  I understand.  This is extremely useful.  I agree that this should be
> done.  Is it on the ToDo?

That's a nice one. Reminds me of python try: except: structures - very useful
indeed. It would make exception handling so much easier. I'd also say it
would improve readability of the code.


    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFABkarjqGXBvRToM4RArdNAJ9Cg4saorEpxirE9e0GGanZssG0xACeL7BP
f5sRQ2TqKp5xC3ceOu2nFrE=
=e9X6
-----END PGP SIGNATURE-----


Re: what we need to use postgresql in the enterprise

From
Bob.Henkel@hartfordlife.com
Date:
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. 
*************************************************************************


Re: what we need to use postgresql in the enterprise

From
Christopher Browne
Date:
Clinging to sanity, xzilla@users.sourceforge.net (Robert Treat) mumbled into her beard:
> I think your pretty much on target with the below. It is possible to
> work around these issues on some level, but I can see how that might
> get unwieldly in a hurry.  While I won't tell you to "go code it if
> you need it", I might ask that you consider what your paying in
> Oracle licenses and think about spending that money to hire someone
> to develop those features in PostgreSQL, I'm thinking you'd save
> money in the long run.

I was under the impression that someone was working on nested
transactions, with a view of them maybe becoming a 7.5 feature.

It might thereby be worthwhile to figure out who that "someone" is,
which is probably googleable, and maybe offer them something of a
"bounty," whereby if the feature makes it in, there might be some
money in it...
--
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/spreadsheets.html
This login session:  only $23.95!