Re: [GENERAL] 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 [EMAIL PROTECTED]To: Robert Treat [EMAIL PROTECTED], [EMAIL PROTECTED] icas.comcc: [EMAIL PROTECTED] 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, [EMAIL PROTECTED] wrote: snip 1. Need commit roll back in pl/pgsql much like Oracle does 2. Need
Re: [GENERAL] what we need to use postgresql in the enterprise
From: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] what we need to use postgresql in the enterprise
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, [EMAIL PROTECTED] 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. ANeed sub transactions . BAnd 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] what we need to use postgresql in the enterprise
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 [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] eforge.netcc: 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, [EMAIL PROTECTED] 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. ANeed sub transactions . BAnd 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
Re: [GENERAL] what we need to use postgresql in the enterprise
--- [EMAIL PROTECTED] 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. ANeed sub transactions . BAnd 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 [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 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, [EMAIL PROTECTED] 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. ANeed sub transactions . BAnd 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 [EMAIL PROTECTED]
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
