what we need to use postgresql in the enterprise

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

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!

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

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

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

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

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

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