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.