Thread: Re: [GENERAL] stored procedure revisited
>the closest thing to SP in PG, however, it's limited that it can only >return one row, how about use temp tables? too awkward? too slow -- but who cares that kind of performance? (if that is important, time to go to Oracle etc.). >and it requires a syntax of 'select func1(args)' which >is non-intuitive as a substitute for non-resultset SP. come on, who cares? >I am not even >sure if functions achieve what SP is supposed to achieve - saving the >server time from reparsing the queries; I have a feeling that >functions >are also just place-holders at this point. anybody can comment? pg's dbd does not have "prepare", so, seems that you are right! However, who cares that kind of performance? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
amy cheng wrote: > > >I am not even > >sure if functions achieve what SP is supposed to achieve - saving the > >server time from reparsing the queries; I have a feeling that >functions > >are also just place-holders at this point. > > anybody can comment? pg's dbd does not have "prepare", so, seems > that you are right! However, who cares that kind of performance? > Well, I can't speak for others, but I know I care, and it seems that you don't care :) But you probably care, too. Wouldn't you want to have the ability to convince your boss that PG not only is free, but it also matches the capability and kicks the day light out of [substitute your most-hated commercial RDBMS here]? :) Seriously though, SP is a good thing to have. It increases the performance of the server, and it also increases your personal productivity as well (no-longer thinking about work-arounds). Wouldn't you want DBD's prepare statement work as advertised? Ususally, performance/abstraction are inversely proportional, e.g., C runs faster than Perl but it's also harder to learn and use. However, here's a case that performance/abstraction are directly porportional - not only does SP increases the performance, it also folds the procedural layer into the database. Seems like a win-win situation. I have a feeling that SP has been such a _dead_issue_ for so long that no one seems to think about it (when people do it seems to be just about work-arounds). But as PG's development moves on, this is a good piece of technology to include, just like MVCC. It's not due to chances that commercial RDBMS have it (even Access has it, after a fashion). If you feel the same way (SP makes the program & life better), please raise your voice so the developers can hear it (I certainly hope to make this into their agenda). If you don't feel the same way, please raise your voice too. Maybe I will be convinced that it wasn't such a good idea after all :) Regards, yin-so chen
OK. Stored procedures are one of the most powerful tools available in database design and implementation. U guys/gals forget that placing business rules in stored procedures allows for flexibility and modular reusability of code as well as speeds up such commonly used queries. Although I have never had the need to use stored procedures (never had a project that used PostgreSQL that was large enuff), PostgreSQL allows u to create SQL functions...which I believe meet the criteria u need for stored procedures? I could be wrong, but I dont know of any database that allows you to run an SQL stmt without having the engine process the entire query in the background. Even those that return limited sets must execute a sort on the entire dataset before a subset can be returned... Stored Procedures have always been the strong point of Oracle :) ----- Original Message ----- From: Yin-So Chen <ychen1@uswest.net> To: <pgsql-general@postgreSQL.org> Sent: Tuesday, October 12, 1999 6:41 PM Subject: Re: [GENERAL] stored procedure revisited > amy cheng wrote: > > > > >I am not even > > >sure if functions achieve what SP is supposed to achieve - saving the > > >server time from reparsing the queries; I have a feeling that >functions > > >are also just place-holders at this point. > > > > anybody can comment? pg's dbd does not have "prepare", so, seems > > that you are right! However, who cares that kind of performance? > > > > Well, I can't speak for others, but I know I care, and it seems that you > don't care :) But you probably care, too. Wouldn't you want to have > the ability to convince your boss that PG not only is free, but it also > matches the capability and kicks the day light out of [substitute your > most-hated commercial RDBMS here]? :) > > Seriously though, SP is a good thing to have. It increases the > performance of the server, and it also increases your personal > productivity as well (no-longer thinking about work-arounds). Wouldn't > you want DBD's prepare statement work as advertised? Ususally, > performance/abstraction are inversely proportional, e.g., C runs faster > than Perl but it's also harder to learn and use. However, here's a case > that performance/abstraction are directly porportional - not only does > SP increases the performance, it also folds the procedural layer into > the database. Seems like a win-win situation. > > I have a feeling that SP has been such a _dead_issue_ for so long that > no one seems to think about it (when people do it seems to be just about > work-arounds). But as PG's development moves on, this is a good piece > of technology to include, just like MVCC. It's not due to chances that > commercial RDBMS have it (even Access has it, after a fashion). If you > feel the same way (SP makes the program & life better), please raise > your voice so the developers can hear it (I certainly hope to make this > into their agenda). If you don't feel the same way, please raise your > voice too. Maybe I will be convinced that it wasn't such a good idea > after all :) > > Regards, > > yin-so chen > > ************ > >
Kane Tao wrote: > > OK. Stored procedures are one of the most powerful tools available in > database design and implementation. U guys/gals forget that placing > business rules in stored procedures allows for flexibility and modular > reusability of code as well as speeds up such commonly used queries. > Although I have never had the need to use stored procedures (never had a > project that used PostgreSQL that was large enuff), PostgreSQL allows u to > create SQL functions...which I believe meet the criteria u need for stored > procedures? > Actually, the CREATE FUNCTION is insufficient as a substitute for SP, although it certainly can be improved upon. For example, functions cannot return more than one column :) > I could be wrong, but I dont know of any database that allows you to run an > SQL stmt without having the engine process the entire query in the > background. Even those that return limited sets must execute a sort on the > entire dataset before a subset can be returned... > I don't know how SP is implemented since none of the commercial RDBMS publishes their sources, but they've all claimed that SP saves parsing time and saves query plan time (it's generated once and stored). Need some database experts to verify this point :) And like you said, it's one of the most powerful tools available for database implementation. I want the ability simply because of its conceptual abstraction, even if w/out any of the performance benefit. Come on, everybody, speak out your thought on this matter :) Regards, yin-so chen
Right, PSQL functions cannot return more than one value or a row from a table which is a pain. But inside these routines you can do quite a lot. With PL you can write quite complex functions, and if that is too slow you can write C routines with SPI and call those. In C routines you can store the query plan. It really is easy -- just look at the examples in contrib and the docs. And if the fact that you cannot get more than one value returned bothers you -- well, time to roll up your sleeves and fix it. That's what opensource is all about, after all ;-) Adriaan
Hello, On Tue, 12 Oct 1999, Yin-So Chen wrote: > feel the same way (SP makes the program & life better), please raise > your voice so the developers can hear it (I certainly hope to make this > into their agenda). If you don't feel the same way, please raise your > voice too. Maybe I will be convinced that it wasn't such a good idea > after all :) Ok, here's my two cents worth. I used to SysAdmin a system that was designed quite some time ago, after the original designers had left. Essentially the database we had was a customer contact/billing/invoicing type thing. Oh, and it managed the company accounts as well. Ran on MS SQL using VB3 and 5 client applications. The majority of database manipulation in this environment was acheived by using stored procedures, which were passed parameters from the various client programs. This included the fetching, inserting and updating of all data - this was never done directly from a client app. The reasoning behind this was to keep everything to do with the data in the database. I'm not sure if that's a valid reason, but that's the one I was given. Every single management report that was needed (and there were *lots* of them) was created by using stored procedures to find the data required, and then passing it into Seagate Crystal Info (which is great for reports BTW if you have the NT environment). All in all, without stored procedures making that system work would have been an incredibly difficult task. Another angle to look at this subject from is to say when DBadmins like the people on this list are trying to convince more sneior members of staff, (or in-duh-viduals, if you like), of the merits of something like Postgres, a feature list is something that can work in our favour. The fact that it doesn't do something that most, if not all, commercially available db systems do can work against us, Regards, Mark. -- Mark Jewiss Knowledge Matters Limited http://www.knowledge.com
On Tue, 12 Oct 1999, Yin-So Chen wrote:
[snip]
>
> I don't know how SP is implemented since none of the commercial RDBMS
> publishes their sources, but they've all claimed that SP saves parsing
> time and saves query plan time (it's generated once and stored). Need
> some database experts to verify this point :) And like you said, it's
> one of the most powerful tools available for database implementation. I
> want the ability simply because of its conceptual abstraction, even if
> w/out any of the performance benefit.
>
> Come on, everybody, speak out your thought on this matter :)
Well, for me it would allow the current kludge that the JDBC driver uses
for PreparedStatement. Having SP would allow that class to temporarily
store the procedure, then only the data would need to be transfered to the
backend. This would improve the majority of JDBC useage enormously.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
Peter Mount wrote: > > Well, for me it would allow the current kludge that the JDBC driver uses > for PreparedStatement. Having SP would allow that class to temporarily > store the procedure, then only the data would need to be transfered to the > backend. This would improve the majority of JDBC useage enormously. > Definitely. For JDBC/ODBC camp, SP is a strong feature to have. Although for the PrepareStatement it would mean the system needs to allow "temporary" stored procedure in the database, and is PG's security mechanism set up for that? Regards, yin-so chen
On 12 Oct 99, at 19:34, Yin-So Chen wrote: [SP discussion snipped] > > Come on, everybody, speak out your thought on this matter :) Alright, alright. Last I used the SP was on a Teradata box, and I must admit it's a useful functionality. It makes the code much easier to read, in addition to all the previously mentioned benefits. I just have trouble seeing a convenience get its day in the sun among the developers. $0.2c Rudy > > Regards, > > yin-so chen > > ************ > >
Virtues of SP: 1) speed - no reparsing of queries, no multiple queries transmitted between application and db (if SP not available, operation is a sequence of sql operations). 2) Business Logic is enforced uniformly, not re-implemented in every app that touches a table. Drawbacks of SP: 1) Secondary BL mechanism - Referential Integrity is generally regarded as better. Its declarative, so is easier to use in the query optimizer. SPs are procedural, and are harder to get right (I recall a recent posting where a post-trigger couldnt find the row cuz the column values changed from that expected in the where-clause, which were specified before the transaction started) 1) Sub-Optimal location for Business Logic RI cant easily describe all the business rules that must be honored. Procedures are often needed. Procedures are best described using a real programming language. Business Logic kinda goes with Business Applications; Apps are the context and cause for BL, and probably the most natural place to define it, particularly since the App tends to be more OO than RDBMSs.. Without SP, BL *must* be in the App, this is our current situation. In order for SP to present an easy migration path, it should also derive from the same expression of the BL. 2 concrete contexts from Perl world. DBI->prepare_cached($sql-cmd): method implies that it is stored for speed. This is directly accessible to programmer via the App language. DBIx::Table requires a complete description of the db-table structure. This info supports the automatic generation of complex DML from simpler descriptions. The info could be used to generate the 'create table' statement in sql (with caveats re table ownership, alteration, population) With a richer table description, it *seems* possible to derive RI and SP constraints. Its probably hard to do well, but even temporary tables could work. Other points. BL given in Apps means that multiple languages must provide enough info for SP derivation to be done. This might be prohibitive. Derivation of SP (centralized control) based upon multiple Apps (BL is 'specified' in each) creates a problem of inconsistent specifications. This means that the App should be able to guarantee consistency between 2 SP specifications given by 2 Apps. Presumably this would be done in a supporting library. Hopefully this thread will catalog some of the canonical uses of SP, implemented without SP, so that those geniuses who could actually implement SP in Postgres will have a comprehensive set of Canonical Uses that can inform the design of SP.
Jim Cromie wrote: [snip Virtues of SP - agreed :) And I have more opinions on the drawbacks of SP, as expected... ] > > Drawbacks of SP: > > 1) Secondary BL mechanism - Referential Integrity is generally regarded as > better. Its declarative, so is easier to use in the query optimizer. Triggers can certainly be _procedural_. > > 1) Sub-Optimal location for Business Logic > > Business Logic kinda goes with Business Applications; Apps are the context > and cause for BL, and probably the most natural place to define it, > particularly since the App tends to be more OO than RDBMSs.. Having BL in the BA level means there must be a BA for things to work. Migration path isn't critical for businesses; production databases are seldomly moved. So this is more of an issue for the application vendors than the database administrators (they want to sell the product, of course :P). > > 2 concrete contexts from Perl world. > > DBI->prepare_cached($sql-cmd): method implies that it is stored for speed. This statement only works _as_advertised_ w/ databases that have SP. Note DBD::Pg states that Postgresql does not have a prepare concept (it's there for compatibility, w/ the complete query sent every time). That's why SP rocks :) Regards, yin-so chen
Thanks for all who have replied! Those are certainly a lot of good points and discussions! From the messages, I think I've gotten the following: 1. SP is a good feature to have, although it's not necessarily the _be_all_end_all_ solution. 2. It's one of the features people would like to see. 3. It's been such a _dead_issue_ that people talk about SP in terms what work-arounds are there. 4. Since it's such a good feature, why don't you (I) roll up your sleeves and do it? For #4, besides the fact that I am not a C programmer, programming a database is certainly not like eating a piece of cake, otherwise we would have hundreds of open source database to choose from :) And this is what I want to talk about now - how do we go from ranting about SP to get it into PG? My first step was trying to make enough noise in the general list so the developers would realize that after all these years there is still demands for SP. There was noise, but obviously it's not a lot of people speaking up yet. Given the rule of thumb that only one in ten people are vocal, we can safely guess the demand is there though. The next step is trying to figure out what's the best way of getting SP into developer's agenda. I would like to ask for all of your help again, to brainstorm for the best way(s) to make it happen. Think of what it would take to convince the developers that this is important, besides we say that it's important. Certainly, PG's feature sets aren't developed solely because they are important. Half are done probably because developers think it's kewl. But in order for it to happen, we need to know what's going to make them feel the need is there. I am going to tentatively approach the developers on the hacker list to see what they think... hopefully I don't get the boot just yet :) Please, speak out your thought on this matter! Anything at all, even if you are joking, or if you think it's a complete waste of time :) We can do it. Regards, yin-so chen
Just to add one more voice, I would dearly like to see stored procedures, but have no C expertise to help out :-< M. ---------------------------------------------------------------------- ---------------- Moray.McConnachie@computing-services.oxford.ac.uk
Hi everyone,
say I have a table test
create table test (a int, b text);
and I want to ensure that 'b' is unique for a particular 'a' ('a' not being
unique).
Right now I can do something like
begin transaction
select count(*) from test where b =$value;
if count = 0 then insert into test (a,b) values ($a, $value);
But there is still a chance of a duplicate being inserted.
Is using a multicolumn unique index the best way to avoid such problems?
Any other suggestions?
Somehow I prefer to avoid getting exceptions from databases.
Is there a "select for insert" command?
e.g. while in transactions if a select for insert has the same criteria,
it's not executed until the other prior matching transactions are
completed. How yucky is that? <grin>
Thanks,
Link.
Lincoln Yeoh wrote:
> Hi everyone,
>
> say I have a table test
> create table test (a int, b text);
>
> and I want to ensure that 'b' is unique for a particular 'a' ('a' not being
> unique).
>
> Right now I can do something like
> begin transaction
> select count(*) from test where b =$value;
> if count = 0 then insert into test (a,b) values ($a, $value);
>
> But there is still a chance of a duplicate being inserted.
use unique constraint on column a as in:
CREATE TABLE test (a char unique, b int);
José