Re: SQL-Invoked Procedures for 8.1 - Mailing list pgsql-hackers

On Fri, 2004-09-24 at 04:12, Josh Berkus wrote:
> My comments are based on having professionally written several hundred 
> thousand lines of procedural code for PostgreSQL, SQL Server, and Oracle.

I haven't used stored procedures as implemented elsewhere, so I
appreciate your comments.

> > If we go down the route of saying that procedures are a type of function,
> > we have the option of allowing users access to OUT and INOUT in functions.
> > This would make procedures simply a subset of functions. What do people
> > think?
> 
> Well, to be frank, my first thought is, why bother?   If you're just going to 
> implement some syntatic sugar on top of the current Function feature, why 
> bother at all?

As far as possible, I would like to extend the PG concept of "functions"
to offer what people expect from stored procedures, and then implement
syntax sugar so that people can use the standard's stored procedure
syntax.

I think the system is cleaner if we keep the number of distinct concepts
users need to understand to a minimum. That means not making arbitrary
distinctions between stored procedures and functions. It may turn out,
for example, that implementing the kind of transactional behavior people
want for procedures won't be possible within the existing function
infrastructure -- if that's the case, so be it: we'll need to
distinguish procedures and functions. But I'd prefer to unify the
concepts as far as possible. 

> Given the opportunity, I would far prefer to set us on a road that would allow 
> us to replicate -- and exceed -- the functionality of Oracle's PL/SQL.

That's a priority for me, as well.

> Well, see my thoughts above on differentiating SPs from Functions.    I 
> certainly don't think we should be using the same table.

Using a different system catalog strikes me as total overkill, and a
surefire way to duplicate a lot of code.

> > SET VARIABLE <varname> = <val>
> > SHOW VARIABLE <varname>
> 
> The problem with this is name collisions with the GUC -- especially as it now 
> allows add-ins to create their own GUC variables.   However intuitive SET and 
> SHOW are, they will lead to problems.

I don't see how it will: SET VARIABLE would not share SET's namespace,
so collisions would not be possible.

> > The other option is that we do it at the protocol level and modify libpq
> > (and psql) to add support. [...]
> 
> This may be a better approach. I've personally never been comfortable with 
> the use of variables outside of SPs and packages; it seems orthagonal to the 
> declaritive nature of SQL.

Whether we support protocol-level variables or SQL-level variables has
nothing to do with how those variables can be referenced in queries, so
I'm not sure what you're getting at.

> 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 think what you're asking is after a query like:

CALL foo_proc(:some_out_param);

does the client need to explicitly fetch the modified variable, or is it
returned to the client via some means automatically.

Requiring the client to issue a fetch involves an additional roundtrip
(and is an annoyance), so I'm leaning toward returning modified
variables automatically. Perhaps we should allow clients to register
interest in variables -- when the value of that variable changes, they
would receive a protocol message with its new value. I don't see a clean
way to do this without modifying the protocol, though.

(We might have clients register for interest in variables they create by
default.)

> > The only other question (that I can think of now) with respect to
> > variables is how they are affected by transactions. My gut feel is
> > that they should have transactional semantics. [...]
> 
> I agree strongly with this, especially since we'll be using Savepoints inside 
> the SPs.   Having one's data mods roll back but not the variable values would 
> be confusing and lead to a *lot* of debugging.

Agreed. BTW, I should note that I'm not taken with the idea of storing
variables in temporary tables -- I don't think it will take too much
work to implement transaction semantics for variables by hand, since
there is no need to worry about concurrency.

-Neil

(I need to mull over your points on overloading and transactions -- I'll
get back to you on that...)