Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Josh Berkus wrote:

> One of the things which differentiates SPs on other DBs from PostgreSQL
> Functions is transactionality. In SQL Server and Oracle, SPs are not
> automatically a transaction; instead, they contain transactions within them.
> This is vitally important to DBAs who want to use SPs to automate database
> maintenance, loads, transformations, and other activities which require
> checkpointing within the course of a program.

Good point. Neil and I have been nutting out some of the issues to do with
allowing SPs to start up 'outside' of a transaction. There are some pretty
weird cases like, what if a function calls a procedure? What if that
function is called in the WHERE clause of a query?

ANSI SQL has a flag, MODIFIES SQL DATA, which allows you to differentiate
between SPs which affect the database (and therefore may do something
which needs to be cleaned up in case of error) and those which don't --
ie, they just operate on their arguments.

Still, Neil and I think that allowing people to do their own txn
management inside SPs is important enough to try and look at all the cases
and solve them. We'll detail this later in the week.

> A second point, which I brought up with you on IRC, is to eliminate
> overloading and allow named parameter calls on SPs. This is extremely
> useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's
> essential for any operation that wants to create an SP-centric middleware as
> only named parameter calls allow developers to add parameters to existing
> procedures without breaking existing calls.

We think that not supporting overloading for SPs is reasonable but I am
open to debate. FWIW, it is not supported by Oracle for example.

I'm not sure about named parameter notation (as oracle calls it) for the
arguements. It seems, at least to me, that it would encourage bad
programming but if we want to ease migration it may be worthwhile. Does
anyone know how widely the feature is used?

> The more practical consideration is, where will OUT and INOUT parameters be
> used? Do we want them returned to the SQL session or directly to the
> calling client? I would think that practicality would argue in favor of the
> latter; I can't see needing variables in SQL except for testing, and having
> them in psql will allow me that.

I like the efficiency of returning them after the CALL (after every
query?). But what if someone declares a very large text variable. Do we
need to return it every time?

Thanks for your detailed feed back.

Gavin