Re: [GENERAL] stored procedure revisited

[Rudy Gireyev]

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

Re: [GENERAL] stored procedure revisited

[Jim Cromie]

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.

Re: [GENERAL] stored procedure revisited

[amy cheng]

datamart is important for web. That is why HISTORICALLY, mySQL is
so popular. BTW, I withdraw the opinion on mySQL, IMHO, it is too limited, 
no mention its not-generous-enough license.
IF I have time, I will do it myself *sigh*.

>From: Yin-So Chen <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Subject: Re: [GENERAL] stored procedure revisited
>Date: Wed, 13 Oct 1999 15:52:28 -0700
>
>amy cheng wrote:
> >
> > C is good, and in a sense, for OSS we should encourage more C 
>"scripting"
> > and "hacking" than script scripting. (perl and PL/pgSQL actually is 
>"bad" in
> > this sense). Because IF everybody use C, the use and development will
> > inherently related and the dev. speed will
> > accelate exponentially. However, C/C++ is difficult (I use
> > both C and perl, so I know it). Also, as GOOD excuse, C/C++
> > is not safe. So, we need PL SP.
>
>Well, not everyone in this world can work in the C level (I certainly
>included myself here), and talking about languages is getting awefully
>close to advocacy :)  But just think this way though, if C is the route
>to go, then why not assembly?  When you have an answer of why not, you
>also have an answer for C as well :)  But OTOH, that's why C programmers
>have nothing to fear about all the VB programmers out there...  Because
>there are jobs only C is appropriate.  I am sure you all know this so
>ignore my mumbling :)
>
> >
> > However, I would like to see data warehouse (or more moderately and
> > accurately data mart) support also -- the point: the priority?
> >
>
>So, what is the priority?  I will argue that SP is a higher priority
>than data warehousing.  The reason?  More people would benefit from SP
>than from data warehousing.  Moreover, SP will also draw database
>administrator's mind-share for PG.  Who's going to work with the
>database?  Administrators & application developers, mostly.  And if
>there are features which most administrators or developers would
>consider lacking, it would be a reason for them to look elsewhere.
>W/out them pitching for PG, would PG compete well against commercial
>databases?  SPI is great and all, but there is a reason why a PL is also
>developed.  Since the PL is here, then SP is the next logical step :)
>
>Regards,
>
>yin-so chen
>
>
>

__
Get Your Private, Free Email at http://www.hotmail.com

Re: [GENERAL] stored procedure revisited

[Yin-So Chen]

amy cheng wrote:
> 
> C is good, and in a sense, for OSS we should encourage more C "scripting"
> and "hacking" than script scripting. (perl and PL/pgSQL actually is "bad" in
> this sense). Because IF everybody use C, the use and development will
> inherently related and the dev. speed will
> accelate exponentially. However, C/C++ is difficult (I use
> both C and perl, so I know it). Also, as GOOD excuse, C/C++
> is not safe. So, we need PL SP.

Well, not everyone in this world can work in the C level (I certainly
included myself here), and talking about languages is getting awefully
close to advocacy :)  But just think this way though, if C is the route
to go, then why not assembly?  When you have an answer of why not, you
also have an answer for C as well :)  But OTOH, that's why C programmers
have nothing to fear about all the VB programmers out there...  Because
there are jobs only C is appropriate.  I am sure you all know this so
ignore my mumbling :)

> 
> However, I would like to see data warehouse (or more moderately and
> accurately data mart) support also -- the point: the priority?
> 

So, what is the priority?  I will argue that SP is a higher priority
than data warehousing.  The reason?  More people would benefit from SP
than from data warehousing.  Moreover, SP will also draw database
administrator's mind-share for PG.  Who's going to work with the
database?  Administrators & application developers, mostly.  And if
there are features which most administrators or developers would
consider lacking, it would be a reason for them to look elsewhere. 
W/out them pitching for PG, would PG compete well against commercial
databases?  SPI is great and all, but there is a reason why a PL is also
developed.  Since the PL is here, then SP is the next logical step :)

Regards,

yin-so chen

Re: [GENERAL] stored procedure revisited

[amy cheng]

1) EOF is an app server that complys corba/tkom/ejb, or it is another
"standard"? if the latter, it should/will die! (I wish tkom die
also, seems impossble now though :-(
2) app tier is good, but if SP is forbidden, the cost is that you have to 
write or buy more for things that comes with dbms for free! Also, less 
performance. The bottom line is: for some thing that is naturally relational 
(instead of OO), it is for both dev. cost and performance that they'd better 
stay in dbms. I understand commercial dbms' SP also motivated by evil 
locking desire, but there are strong legitimate reasons also. conclusion: 
app and sp should co-exist.
3) of course, that is for single dbms, if you need coupled multiple
   ones ("distributed db"), app tier is the choice;
4) cos (& IF) all dbms' SP are similar, SP can improve portability, 
comparing with clever but irregular work-arounds. remember: change
db is a big decision, "dynamic" db change is a luxuary.
5)as for datamart, seems that mysql is a better choice. seems to me that Pg 
is optimized for transactional, mysql is and will be more optimized for 
datamart -- just opinions, I'll do more research/testing on this.

>From: Howie <[EMAIL PROTECTED]>
>Reply-To: Howie <[EMAIL PROTECTED]>
>To: amy cheng <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: Re: [GENERAL] stored procedure revisited
>Date: Wed, 13 Oct 1999 17:29:29 + (GMT)
>
>actually, one would hope that the system has its db independence in the
>application layer rather than the database layer.  for instance, using
>something like NeXT's Enterprise Objects Framework to fetch rows from the
>db and translate the rows into objects, you only deal with the objects.
>The whole datastore, at this point, becomes irrelevant since you rarely
>deal with the underlying SQL -- EOF takes care of all that for you.
>Instead, you say "hey, i want all the objects that have their personName
>ivar equal to Amy" ( "personName = 'Amy'" ).  I'm fairly positive that
>Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc )  does the same
>sort of thing.
>
>keeping inserts/selects/etc in stored procedures would still require a
>rewrite of all the stored procedures when moving to another db vendor,
>which may or may not be a large problem depending on that vendor's
>imeplementation of stored procedures and SQL in general.  granted, you
>wouldnt have to completely gut the application and rewrite the whole
>bloody thing, but since your app is already going to have some of it
>rewritten ( cant use an OCI call on postgresql ), i think it'd made more
>sense to abstract things further by putting all the logic into your
>objects, EOF or Entity Javabeans, rather than in the db.
>
>so now lets talk code reuse.  both options would give you about the same
>level of code reuse, but in two completely different ways.  stored
>procedures ( and company/DBA policy ) pretty much force the user to take
>advantage of them rather than doing raw inserts, selects, etc on the
>underlying tables.  EOF forces you to deal with the objects rather than
>sql.  either way, all of your business logic is in one location.   by
>using a higher-level language, however, you wouldnt have to deal with
>tedious pl/sql-ish programming.  one could also argue that having 20+
>different stored procedures is really no better than memorizing the
>business logic and duplicating that in the application, bypassing the
>procedures altogether.  if you have to deal with developing on one dbms
>and deploying on another dbms, EOF starts to look even more beautiful --
>since your logic is in the objects, not the db, nothing will have to be
>ported to the new dbms.  in fact, all you really need to do is change the
>EOModel; all of your code can remain in binary form.
>
>'problems' with EOF-ish approaches include having to distribute your
>framework ( think library ) along with your app, which youd have to do
>anyway seeing that your objects are in that framework/package.  stored
>procedures wouldnt have to be shared outside of the dbms ( obviously ).
>personally, i find it a LOT easier to deal with EOF objects rather than a
>potentially large PL/SQL ( or PL/pgSQL ) procedure.
>
>what'd be interesting is to compare the use of stored procedures to EOF or
>EOF-ish alternatives, using the same data & schema, ofcourse.  NeXT/Apple
>has a sample db, sample data, and examples of how one can use EOF's
>features to augment/replace stored procedures in the dbms.
>
>(java)
>public void validateForDelete() throws EOValidation.Exception {
>if( !isPaid() )
>{
>   throw new EOValidation.Exception("You can't remove an unpai

Re: [GENERAL] stored procedure revisited

[Yin-So Chen]

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

Re: [GENERAL] stored procedure revisited

[Yin-So Chen]

Howie wrote:
> 
> actually, one would hope that the system has its db independence in the
> application layer rather than the database layer.  for instance, using
> something like NeXT's Enterprise Objects Framework to fetch rows from the
> db and translate the rows into objects, you only deal with the objects.
> The whole datastore, at this point, becomes irrelevant since you rarely
> deal with the underlying SQL -- EOF takes care of all that for you.
> Instead, you say "hey, i want all the objects that have their personName
> ivar equal to Amy" ( "personName = 'Amy'" ).  I'm fairly positive that
> Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc )  does the same
> sort of thing.
> 

Well, you are approaching from an application developer's point of
view.  You want to have database independence so you can move back and
forth between all the flavors of databases out there.  ODBC was meant to
address this issue, and we all know there are still limitations. 
However, even then database independence doesn't make the discussion of
stored procedure irrelevant.  Given a better tool to do something,
wouldn't you use it?  If you have to deal with databases ranging from
Access to Oracle, are you going to make your application based on Access
capability, since it doesn't have a PL?  More than likely you are going
to design one version for Access & another for Oracle... 

>From business's POV, application layer is _not_that_ important because
production databases (especially OLTP databases) are seldomly moved. 
Even today the commercial vendors finding themselves supporting the
legacy versions.  In this sense the capability of the database itself
becomes that much more important.  For the database administrators, the
ability underneath the application layer is very important indeed. 
Certainly SP offers a lot of horsepower :)

Regards,

yin-so chen

Re: [GENERAL] stored procedure revisited

[Peter Mount]

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 [EMAIL PROTECTED]
  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

Re: [GENERAL] stored procedure revisited

[Howie]

On Wed, 13 Oct 1999, amy cheng wrote:

> 
> >fact that it doesn't do something that most, if not all, commercially
> >available db systems do can work against us,
> i.e., portability and upgradability: imagine you want to change that
> M$ system into Pg, or, I hate to say this, but somehow if your
> success is so big that you can not live with Pg, you need go to O ect.
> then, true SP will make things really easy (just systax change, you may even 
> just use our open source facility -- I'm sure there will be, since PL/pgSQL 
> are so close to other PL). In my own case, when I begin to use PL/pgSQL, I 
> put some thinking on the second aspect, I bet
> others also did that. A true SP will make it more inviting.

actually, one would hope that the system has its db independence in the
application layer rather than the database layer.  for instance, using
something like NeXT's Enterprise Objects Framework to fetch rows from the
db and translate the rows into objects, you only deal with the objects.
The whole datastore, at this point, becomes irrelevant since you rarely
deal with the underlying SQL -- EOF takes care of all that for you.
Instead, you say "hey, i want all the objects that have their personName
ivar equal to Amy" ( "personName = 'Amy'" ).  I'm fairly positive that
Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc )  does the same
sort of thing.

keeping inserts/selects/etc in stored procedures would still require a
rewrite of all the stored procedures when moving to another db vendor,
which may or may not be a large problem depending on that vendor's
imeplementation of stored procedures and SQL in general.  granted, you
wouldnt have to completely gut the application and rewrite the whole
bloody thing, but since your app is already going to have some of it
rewritten ( cant use an OCI call on postgresql ), i think it'd made more
sense to abstract things further by putting all the logic into your
objects, EOF or Entity Javabeans, rather than in the db.  

so now lets talk code reuse.  both options would give you about the same
level of code reuse, but in two completely different ways.  stored
procedures ( and company/DBA policy ) pretty much force the user to take
advantage of them rather than doing raw inserts, selects, etc on the
underlying tables.  EOF forces you to deal with the objects rather than
sql.  either way, all of your business logic is in one location.   by
using a higher-level language, however, you wouldnt have to deal with
tedious pl/sql-ish programming.  one could also argue that having 20+
different stored procedures is really no better than memorizing the
business logic and duplicating that in the application, bypassing the
procedures altogether.  if you have to deal with developing on one dbms
and deploying on another dbms, EOF starts to look even more beautiful --
since your logic is in the objects, not the db, nothing will have to be
ported to the new dbms.  in fact, all you really need to do is change the
EOModel; all of your code can remain in binary form.

'problems' with EOF-ish approaches include having to distribute your
framework ( think library ) along with your app, which youd have to do
anyway seeing that your objects are in that framework/package.  stored
procedures wouldnt have to be shared outside of the dbms ( obviously ).
personally, i find it a LOT easier to deal with EOF objects rather than a
potentially large PL/SQL ( or PL/pgSQL ) procedure.

what'd be interesting is to compare the use of stored procedures to EOF or
EOF-ish alternatives, using the same data & schema, ofcourse.  NeXT/Apple
has a sample db, sample data, and examples of how one can use EOF's
features to augment/replace stored procedures in the dbms.  

(java)
public void validateForDelete() throws EOValidation.Exception {
   if( !isPaid() ) 
   {
  throw new EOValidation.Exception("You can't remove an unpaid fee");
   }

   super.validateForDelete();
}

(objective-c)
- (NSException *)validateForDelete
{
   if( ![self isPaid] )
  return [NSException validationExceptionWithFormat:@"You can't remove an unpaid 
fee"];
   return [super validateForDelete];
}

and yes, i do realize that not everyone has the option of using
EOF/Javabeans... nobody's perfect :)

> [SNIP]
> However, I would like to see data warehouse (or more moderately and 
> accurately data mart) support also -- the point: the priority?

so either (A) work on implementing tablespaces or (B) donate some money to
postgresql, inc. 

---
Howie <[EMAIL PROTECTED]>   URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."

Re: [GENERAL] stored procedure revisited

[Herouth Maoz]

At 09:33 +0200 on 10/10/1999, Yin-So Chen wrote:


> I think I am missing something very obvious here.  Can someone please
> kind enough explain to me, is there SP for postgresql and where I can
> find more information about it?  If there isn't, is there any plan for
> implementation?  Seems there needs to be an extra table that would hold
> the parsed query tree and a rewrite of the parser to make SP work.

AFAIK, there are no stored procedures in PostgreSQL. Maybe they are in a
low priority, or the developers are just trying to avoid the problems of
doing them. I'm not a developer, but I get the impression that sets of rows
are not well-abstracted in PostgreSQL. I think this is also the reason why
there are no subselects in target lists yet (correct me if I'm wrong).

Anyway, sometimes the proper solution for things you stated (i.e. return a
set of rows without using the entire query every time) are more correctly
done with views. Other things (procedural things that don't result in sets
of rows) are handled by functions. So you have a rather small niche for
which only stored procedures are the most proper tool, and which is not
covered in PostgreSQL.

If you think this niche is important, maybe you should convince the rest of
us here (I never needed to use a stored procedure so far, and I don't
remember many people using them five years ago when I was in an Oracle
environment). Or you could prioritize it with money...

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [GENERAL] stored procedure revisited

[Yin-So Chen]

amy cheng wrote:
> 
> forgive my ignorance. why "multi-resultset, multi-level transaction" SP is
> so important? no work-around? I rememeber there were some discussion on
> multiple-return-value-function in the past. My impression is that they are
> not that crucial and usually can
> find rather simple work-arounds.
> 

SP is important for a lot of reasons.  First it allows faster network
transmission because you don't have to send the query over and over
again, second it allows for faster execution because the server doesn't
need to reparse the query every time, third it allows for conceptual
abstraction so the queries can be moved into the database layer, etc...  
"multi-resultset, multi-level transaction" is just an indication of what
other database can do with SP's.  All I want to know is if there is SP
for postgresql, or _better_than_SP_ alternatives.

Work-arounds are, exactly that, work-arounds.  They are something that
will work _for_now_, but not the best solution.  I ask the question not
because I don't know how to live without SP, but because I want to see
what the mentality is behind the whole thing - is there something
intrinsically wrong with having SP, or is there some better stuffs than
SP out there, etc.  What makes a piece of software great?  When its
developers do not settle for work-arounds.

My questions still stand.  Please can someone fill in on the status with
SP, thanks.

Regards,

yin-so chen

Re: [GENERAL] stored procedure revisited

[amy cheng]

forgive my ignorance. why "multi-resultset, multi-level transaction" SP is 
so important? no work-around? I rememeber there were some discussion on 
multiple-return-value-function in the past. My impression is that they are 
not that crucial and usually can
find rather simple work-arounds.



>From: Yin-So Chen <[EMAIL PROTECTED]>
>To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
>Subject: [GENERAL] stored procedure revisited
>Date: Sun, 10 Oct 1999 00:33:33 -0700
>
>Hi,
>
>I've gone through the mailing list archives and the documentation to
>search on this topic, and amazingly there is almost no signs of stored
>procedure discussions anywhere.  Once a while a person would ask the
>question of how to do SP in postgresql, and it will be greeted with
>silence.  Not that anyone is obligated to reply, but the low volume
>about this topic seems to mean:
>
>1. this is a very dumb question, rtfm first.
>
>2. no one knows.
>
>When reading through the documentation, it seems there isn't a way to do
>SP like in other databases.  The closest thing is the CREATE FUNCTION
>command, which can only return one value or a set of one row (of course,
>when it's an opaque function it acts just like other SP).  This is very
>far from the multi-resultset, multi-level transaction SP in other
>databases.  When I go through the ToDo list, SP is not on it either.
>
>I think I am missing something very obvious here.  Can someone please
>kind enough explain to me, is there SP for postgresql and where I can
>find more information about it?  If there isn't, is there any plan for
>implementation?  Seems there needs to be an extra table that would hold
>the parsed query tree and a rewrite of the parser to make SP work.
>
>Regards,
>
>yin-so chen
>
>
>

__
Get Your Private, Free Email at http://www.hotmail.com

[GENERAL] stored procedure revisited

[Yin-So Chen]

Hi,

I've gone through the mailing list archives and the documentation to
search on this topic, and amazingly there is almost no signs of stored
procedure discussions anywhere.  Once a while a person would ask the
question of how to do SP in postgresql, and it will be greeted with
silence.  Not that anyone is obligated to reply, but the low volume
about this topic seems to mean:

1. this is a very dumb question, rtfm first.

2. no one knows.

When reading through the documentation, it seems there isn't a way to do
SP like in other databases.  The closest thing is the CREATE FUNCTION
command, which can only return one value or a set of one row (of course,
when it's an opaque function it acts just like other SP).  This is very
far from the multi-resultset, multi-level transaction SP in other
databases.  When I go through the ToDo list, SP is not on it either.

I think I am missing something very obvious here.  Can someone please
kind enough explain to me, is there SP for postgresql and where I can
find more information about it?  If there isn't, is there any plan for
implementation?  Seems there needs to be an extra table that would hold
the parsed query tree and a rewrite of the parser to make SP work.

Regards,

yin-so chen