SQL-Invoked Procedures for 8.1

Hi all,

Following is a proposal to implement what SQL2003 calls 'SQL-Invoked
Procedures' and what most people refer to as stored procedures. Fujitsu
will be funding Neil Conway and I to work on this feature.

Procedures
----------

Procedures are nearly identical to functions. From my reading of
SQL2003 (see sections 4.27 and 11.50) procedures are different from
functions in three ways:

1) Unlike functions, procedures cannot return values.

2) Procedures support parameter modes which specify whether an argument is
IN, OUT or IN OUT

3) Procedures can be run in the same savepoint level as the caller when
OLD SAVEPOINT LEVEL is specified at creation time. According to SQL2003,
functions must be run on a new savepoint level. From my understanding, we
do not do this currently.

Work will focus on 1 and 2 until we have the concept of savepoint levels
with functions. Its possible that we will implement this too if there is
demand.

This makes the difference between procedures and functions quite
superficial: procedures are functions which return void and have parameter
modes. As such, I think we can largely wrap around the existing function
creation code using this rule. That is, CREATE PROCEDURE could and I think
should be syntactic sugar.

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?

Grammar modifications
---------------------

The grammar for creating a procedure, which is a subset of the SQL 2003
grammar, is as follows:

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ [ IN | OUT | IN OUT ] [ name ] type, ... ] )
<routine characteristics>
<routine body>

<routine characteristics> are LANGUAGE, null-call and security clauses,
all of which are already implemented. <routine body> is the body, same as
with the existing functions implementation.

DROP PROCEDURE and CALL will also need to be added.

System catalog changes
-----------------------

There will be cases when we need to identify whether a routine is a
function or a procedure. This could be done two ways. We could say that
any proc in pg_proc which returns void is a procedure or we could store this
in some 'protype' column. Thoughts?

To handle parameter modes, we will also need to add an array of "char"s so
that we can determine the mode of an element in proargnames[].

Invocation
----------

Since procedures do not return a value they are invoked differently than
functions are (at least according to SQL2003). This is because if they were
used in a SELECT, UPDATE, DELETE or INSERT clause, their effect would be
ambiguous at best. ie,

SELECT * FROM foo WHERE bar();

Doesn't make much sense.

In SQL2003, procedures are invoked using:

CALL foo();

One of our original goals was to allow easier invocation of stored
procedures from PL/PgSQL, by allowing "foo()" rather than "CALL foo()"
or "PERFORM foo()". Neil submitted some preliminary patches
implementing this; following discussion with Tom, it was agreed to
make CALL optional at the SQL level, which provides the same effect:

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00412.php

In this sense, invoking a procedure follows the same rules as invoking a
function which returns void.

Procedure arguments
-------------------

>From what I can tell, there are only 3 ways to pass data to a
procedure:

1) As a literal value:

CALL foo('Then one day....');
CALL bar(1, 2, 3);
CALL baz(current_timestamp)

Note that this only works for parameters which are of mode IN (see
more below).

2) As a variable

Other databases, and SQL2003, support a few different implementations what
could be called variables. In SQL2003, there are a few types of
'variables':

i) Host parameters
ii) Column references to transition variables (OLD and NEW in the case of
triggers and rules)
iii) Dynamic parameters (ie, ? in the case of prepare)
iv) Embedded variables

(i) and (iv) are basically bindings to the client side variables. They
provide a mechanism for interface designers to allow users to associate
variables with a procedure, execute that procedure, and have the new value
of the variables available to the language once the execution call
returns. JDBC 3.0 focuses fairly heavily on this kind of thing, for
example.

I think we only need implement one of these and I discuss it below under
'Host Variables'.

3) A transition variable

These are the NEW and OLD variables in trigger and rule specifications.

If we are calling a procedure, we will need to make sure that every
argument is one of these.

Only (2) and (3) can be passed as parameters which are marked OUT or
INOUT. SQL2003 makes the following distinction between parameter modes:

IN - A 'read only' argument. It must be initialised.
OUT - An uninitialised argument which, from memory, SQL2003 says *must* be
initialised by the function itself.
INOUT - A combination of the two above. That is, an initialised argument
which can be written to.

Host Variables
--------------

Host variables are required to make INOUT and OUT parameters useful,
so this is an important part of the implementation of procedures.

The parser will need to be modified to recognise the host parameter syntax,
which is ':<varname>'. We could restrict this to something like CALL or
expand it so that normal queries could use variables too. I cannot
think of any reason to restrict use but I'm open to what people think.

We'll need a host variable node just like we have Const, etc, so that
the variable can be propagated through the system. The variable should be
substituted within the executor. This ensures that EXPLAIN wont break.
Then again, if we don't allow host parameters in normal queries, its not
a problem.

If we do, I think we could get away with piggy backing on the PREPARE/EXECUTE
code with the possibility of allowing the planner to take a look at the current
value of a given host variable so that it can generate a better plan.
Then again, using variables in standard queries may just be a very bad
idea.

As for the creation and modification of host parameters, we have two options:
an SQL language construct, which MySQL uses for example, or a protocol level
mechanism which Oracle appears to use.

As far as I can understand from SQL2003, host parameters are predefine there
at procedure creation time. I don't really get this and I haven't seen it in
another database. If someone could explain it and thinks it would serve us
better than that covered below, please let me know :-).

If we go down the grammar route, I'd imagine that we do something like:

SET VARIABLE <varname> = <val>
SHOW VARIABLE <varname>

I've used SET and SHOW because they're intuitive. Obviously these are used
by GUC and I'm not proposing we use GUC for host parameters as we want a
session-local implementation. It would be useful if we could also add some
type information to this so make procedure candidate selection easier. Ideas?

The other option is that we do it at the protocol level and modify libpq
(and psql) to add support. This would allow us something like:

template1=# \vc x int -- create a variable 'x' of type int
template1=# \vs x 2 -- set it to '2'
template1=# CALL proc_with_inout_param(:x);
CALL
template1=# \vp x -- print 'x'
120

That might need some massaging but you get the idea. The commands to psql
translate to lower level protocol level commands which are: create
variable, set (ie, modify the value of) variable and describe variable. Neil
thinks we should have create and set in one step. This is great, since it
covers most cases, but I'm not sure how we have uninitialised values. Perhaps
we set to NULL?

I think interacting with variables as above is actually quite ugly as well
but that has nothing to do with implementation at the protocol level rather
my own lack of UI skills :-).

So, what does doing this at the protocol level give us? Well, it keeps it out
of the grammar and decreases overhead for interfaces. Still, those reasons
aren't great when we're look at a protocol modification. I'd love to hear
other people's ideas here: maybe I've overlooked a much more attractive
alternative.

Getting back to the host variables themselves.

Most implementations I've seen only support a very limited range of types,
such as varchar, numeric and int. I don't see any reason why we can't
support the full range of types available in the system.

The visibility of variables is restricted to the session which created
them. That is, they're just like our implementation of temporary tables.

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. For example:

template1=# \vc x int
template1=# \vs x 2
template1=# BEGIN;
BEGIN
template1=# CALL proc_which_sets_arg_to_100(:x);
CALL
template1=# ABORT;
template1=# \vp x

What should 'x' be? Following transactional semantics, it should be 2.

The only problem is, implementing the transaction semantics might be
painful. Bruce made a suggestion to me a while ago that we could just put
the variable in a temp table. But that limits the scope for types and it
also may slow performance in a long running transaction which modifies a
variable a lot. That is, its a lot of overhead.

The other option is a hash (or some dynamic structure) in which we
attach some transaction information to each variable. We will need to
process this from CommitTransaction()/AbortTransaction(). We'll also
need to be subtransaction aware. Because we don't need to manage
concurrent access to variables, the logic needed to implement
transactional semantics by hand shouldn't be too difficult.

PL/PgSQL
--------

Its in PL/PgSQL that procedures will be most intensively used, I
believe. Since variables are already supported in the language we
will need to modify that system to make OUT and INOUT parameters
work. Currently, we copy each the argument before we call a function
so that any (potential) write to it will not be reflected in the
caller. We're also protected here by the executor which doesn't allow
us to update arguments since they are marked as constants. We'll also
need to tweak this.

The other thing we will need to look out for if we're executing a
procedure is if the user attempts to RETURN a non-void value. This would be an
error, of course. Simply using RETURN to return control to the caller shouldn't
be a problem in my opinion, however. Neil has also pointed out to me that
this is also the exact behaviour required of functions returning void.

Permissions
-----------

We can use the same permissions as for functions.

----

I will be away for the next few days (until Monday). I hope to have Internet
access but I'm not sure. Neil and I have worked through this together so
he can also answer any questions too.

Gavin

Hi Gavin,

Although I have not read the SQL 2003 spec, my recollection of other database
products' stored procs differed from your description in one significant way,
namely that they could return multiple (and varied) sets of rows.

For example, a stored proc could do a SELECT over foo and then a SELECT over
bar and return the tuples of both foo and bar. (each having different column
counts, types, etc)

The JDBC interfaces would appear to illustrate this point.
(In CallableStatement)

"A CallableStatement can return one ResultSet object or multiple ResultSet
objects. Multiple ResultSet objects are handled using operations inherited
from Statement."

Regards,
Grant

Gavin Sherry wrote:
> Hi all,
>
> Following is a proposal to implement what SQL2003 calls 'SQL-Invoked
> Procedures' and what most people refer to as stored procedures. Fujitsu
> will be funding Neil Conway and I to work on this feature.
>
[lots of interesting detail]

On Thu, 23 Sep 2004, Grant Finnemore wrote:

> Hi Gavin,
>
> Although I have not read the SQL 2003 spec, my recollection of other database
> products' stored procs differed from your description in one significant way,
> namely that they could return multiple (and varied) sets of rows.
>
> For example, a stored proc could do a SELECT over foo and then a SELECT over
> bar and return the tuples of both foo and bar. (each having different column
> counts, types, etc)
>
> The JDBC interfaces would appear to illustrate this point.
> (In CallableStatement)
>
> "A CallableStatement can return one ResultSet object or multiple ResultSet
> objects. Multiple ResultSet objects are handled using operations inherited
> from Statement."

I read the JDBC 3.0 spec and didn't see this. Like with all specs, some
details are hard to find. However, from what I've seen in the spec, I
think they have functions in mind here. That being said, I can't think how
SQL2003 would allow such behaviour. If you could show us an example,
that'd be great.

Thanks,

Gavin

Quoth the JDBC spec:

public interface CallableStatement
extends PreparedStatement

The interface used to execute SQL stored procedures. The JDBC API provides a
stored procedure SQL escape syntax that allows stored procedures to be called
in a standard way for all RDBMSs. This escape syntax has one form that includes
a result parameter and one that does not. If used, the result parameter must be
registered as an OUT parameter. The other parameters can be used for input,
output or both. Parameters are referred to sequentially, by number, with the
first parameter being 1.

{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}

IN parameter values are set using the set methods inherited from
PreparedStatement. The type of all OUT parameters must be registered prior to
executing the stored procedure; their values are retrieved after execution via
the get methods provided here.

A CallableStatement can return one ResultSet object or multiple ResultSet
objects. Multiple ResultSet objects are handled using operations inherited from
Statement.

For maximum portability, a call's ResultSet objects and update counts should be
processed prior to getting the values of output parameters.

Regards,
Grant

Gavin Sherry wrote:
> On Thu, 23 Sep 2004, Grant Finnemore wrote:
>
>
>>Hi Gavin,
>>
>>Although I have not read the SQL 2003 spec, my recollection of other database
>>products' stored procs differed from your description in one significant way,
>>namely that they could return multiple (and varied) sets of rows.
>>
>>For example, a stored proc could do a SELECT over foo and then a SELECT over
>>bar and return the tuples of both foo and bar. (each having different column
>>counts, types, etc)
>>
>>The JDBC interfaces would appear to illustrate this point.
>>(In CallableStatement)
>>
>>"A CallableStatement can return one ResultSet object or multiple ResultSet
>>objects. Multiple ResultSet objects are handled using operations inherited
>>from Statement."
>
>
> I read the JDBC 3.0 spec and didn't see this. Like with all specs, some
> details are hard to find. However, from what I've seen in the spec, I
> think they have functions in mind here. That being said, I can't think how
> SQL2003 would allow such behaviour. If you could show us an example,
> that'd be great.
>
> Thanks,
>
> Gavin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

On Thu, 23 Sep 2004, Grant Finnemore wrote:

> Quoth the JDBC spec:
>
> public interface CallableStatement
> extends PreparedStatement
>
> The interface used to execute SQL stored procedures. The JDBC API provides a
> stored procedure SQL escape syntax that allows stored procedures to be called
> in a standard way for all RDBMSs. This escape syntax has one form that includes
> a result parameter and one that does not. If used, the result parameter must be
> registered as an OUT parameter. The other parameters can be used for input,
> output or both. Parameters are referred to sequentially, by number, with the
> first parameter being 1.
>
> {?= call <procedure-name>[<arg1>,<arg2>, ...]}
> {call <procedure-name>[<arg1>,<arg2>, ...]}
>

I didn't see this in my copy of the spec, which is version 3.0 FR (final
release). Still, I think we're fine. As I said before, what I think the
spec had in mind was allowing functions to be called from the
callablestatement stuff and have their output put in the first OUT
variable.

This is... reasonable. Compare the stuff Neil's been working on with
"bare" function calls in PL/PgSQL and Tom (I think) saying that it might
be reasonable to just issue func(); as an SQL query: no CALL, no SELECT.

>
> IN parameter values are set using the set methods inherited from
> PreparedStatement. The type of all OUT parameters must be registered prior to
> executing the stored procedure; their values are retrieved after execution via
> the get methods provided here.
>
> A CallableStatement can return one ResultSet object or multiple ResultSet
> objects. Multiple ResultSet objects are handled using operations inherited from
> Statement.

I don't get this multiple ResultSet stuff. All I can think of is that the
spec has this in mind:

CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");

or

CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");

or some other permutation.

I see plenty of references to multiple ResultSets but I cannot find an
example or information on how to generate one.

Gavin

Gavin Sherry wrote:

> I don't get this multiple ResultSet stuff. All I can think of is that the
> spec has this in mind:
>
> CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
>
> or
>
> CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
>
> or some other permutation.

It's not specific to CallableStatement; you can generate multiple
resultsets from a plain Statement, and CallableStatement is just
inheriting that functionality.

The common way of generating multiple resultsets is, indeed, a
multi-statement query. For example:

> Statement stmt = conn.createStatement();
> stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
>
> ResultSet rs1 = stmt.getResultSet();
> // process rs1
> rs1.close();
>
> boolean moreResults = stmt.getMoreResults();
> assert moreResults;
>
> ResultSet rs2 = stmt.getResultSet();
> // process rs2
> rs2.close();
>
> stmt.close();

AFAIK the multiple-resultset stuff is not *required* functionality in
JDBC, it's just there to support it if it does happen. The postgresql
JDBC driver didn't actually support multiple resultsets at all until
recently.

For function/procedure calls, I'd expect it to look like:

> CallableStatement cstmt = conn.prepareCall("{call foo()}; {call bar()}");

and for the driver to turn that into two separate SELECT/CALL/whatever
queries at the protocol level, and manage the multiple resultsets
itself. The current driver doesn't handle multiple call escapes in one
query at all, but that's really just a limitation of the reasonably dumb
call-escape parser it currently has.

I wouldn't worry about this case unless there's some other reason that a
*single* function/procedure call needs to return more than one set of
results.

> I see plenty of references to multiple ResultSets but I cannot find an
> example or information on how to generate one.

That's because there's no standard way to generate them :)

-O

On Thu, 23 Sep 2004, Oliver Jowett wrote:

> Gavin Sherry wrote:
>
> > I don't get this multiple ResultSet stuff. All I can think of is that the
> > spec has this in mind:
> >
> > CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
> >
> > or
> >
> > CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
> >
> > or some other permutation.
>
> It's not specific to CallableStatement; you can generate multiple
> resultsets from a plain Statement, and CallableStatement is just
> inheriting that functionality.
>
> The common way of generating multiple resultsets is, indeed, a
> multi-statement query. For example:
>
> > Statement stmt = conn.createStatement();
> > stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
> >
> > ResultSet rs1 = stmt.getResultSet();
> > // process rs1
> > rs1.close();
> >
> > boolean moreResults = stmt.getMoreResults();
> > assert moreResults;
> >
> > ResultSet rs2 = stmt.getResultSet();
> > // process rs2
> > rs2.close();
> >
> > stmt.close();
>
> AFAIK the multiple-resultset stuff is not *required* functionality in
> JDBC, it's just there to support it if it does happen. The postgresql
> JDBC driver didn't actually support multiple resultsets at all until
> recently.
>
> For function/procedure calls, I'd expect it to look like:
>
> > CallableStatement cstmt = conn.prepareCall("{call foo()}; {call bar()}");
>
> and for the driver to turn that into two separate SELECT/CALL/whatever
> queries at the protocol level, and manage the multiple resultsets
> itself. The current driver doesn't handle multiple call escapes in one
> query at all, but that's really just a limitation of the reasonably dumb
> call-escape parser it currently has.
>
> I wouldn't worry about this case unless there's some other reason that a
> *single* function/procedure call needs to return more than one set of
> results.
>
> > I see plenty of references to multiple ResultSets but I cannot find an
> > example or information on how to generate one.
>
> That's because there's no standard way to generate them :)

Okay. So, its something that can be handled in the driver. That's what I
thought.

Do you have any idea about databases returning result sets from SQL
procedures (ie, not functions).

Thanks,

Gavin

Gavin Sherry wrote:
> Do you have any idea about databases returning result sets from SQL
> procedures (ie, not functions).
>

As other's have pointed out, this is very common in the MS SQL Server
world (and I believe Sysbase also supports it). It works like:

<begin proc def>

select * from something
...
select * from somethingelse
...

<end proc def>

We get requests for this kind of functionality at least a couple of
times a month, and although it's been a few years since I mucked with
MSSQL, I found it to be very useful in a number of different circumstances.

It is only workable because stored procedures cannot participate in
normal SELECT statements. In MSSQL you would do something like:

exec sp_my_multiresultset_proc
GO
-- or --
sp_my_multiresultset_proc
GO

so the analogy to your stored procedure proposal holds:

call sp_my_multiresultset_proc();
-- or --
sp_my_multiresultset_proc();

I had always envisioned implementing this by projecting tuples directly
the way that SHOW ALL or EXPLAIN ANALYZE do. See, e.g.
ShowAllGUCConfig() in guc.c.

Joe

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> 3) Procedures can be run in the same savepoint level as the caller when
> OLD SAVEPOINT LEVEL is specified at creation time. According to SQL2003,
> functions must be run on a new savepoint level. From my understanding, we
> do not do this currently.

It's irrelevant since we don't allow functions to call SAVEPOINT/RELEASE/
ROLLBACK TO explicitly, and probably won't do so anytime soon. The only
thing we can really manage for a function is constrained use of
subtransactions a la plpgsql exceptions. This doesn't require the
savepoints to be named at all, so savepoint levels need not enter into it.

> This makes the difference between procedures and functions quite
> superficial: procedures are functions which return void and have parameter
> modes.

If you implement it that way I think it'll be very largely a waste of
effort :-(. What you're talking about seems mere syntactic sugar and
not a fundamental advance in capability.

What I'd like to see is a "procedure" capability which is somehow
outside the transaction system and can therefore invoke BEGIN, COMMIT,
SAVEPOINT, etc. I have no immediate ideas about how to do this, but
I think that's what people are really after when they ask for
server-side procedures. They want to be able, for example, to have
a procedure encapsulate an abort-and-retry loop around a serializable
transaction. (It'd be great if we could do that in a function, but
I haven't thought of a way to make it work.)

I concur with Grant Finnemore's objection as well: people expect
procedures to be able to return resultsets, ie SETOF something,
not only scalar values. Whether this is what SQL2003 says is not
really the issue -- we have to look at what's out there in competing
products.

regards, tom lane

On Thu, Sep 23, 2004 at 12:40:41PM -0400, Tom Lane wrote:

> What I'd like to see is a "procedure" capability which is somehow
> outside the transaction system and can therefore invoke BEGIN, COMMIT,
> SAVEPOINT, etc. I have no immediate ideas about how to do this, but
> I think that's what people are really after when they ask for
> server-side procedures. They want to be able, for example, to have
> a procedure encapsulate an abort-and-retry loop around a serializable
> transaction. (It'd be great if we could do that in a function, but
> I haven't thought of a way to make it work.)

I don't think we can do that in a standard function, at least not
without a lot of work. If we think of procedures as different from
functions, however, it seems doable.

What's needed for this is to isolate the transaction-initiating code
from the main query-processing loop. So for CALL statements it wouldn't
be invoked, and the procedure would be able to use its own explicit
transaction blocks and savepoints.

This part is not hard to do at all. It can be handled from the parser,
I think.

What's harder is handling the execution code. If the procedure uses
SPI, we need a mechanism to keep its SPI state, outside the normal
transaction-bound SPI stack.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:

> What's needed for this is to isolate the transaction-initiating code
> from the main query-processing loop. So for CALL statements it wouldn't
> be invoked, and the procedure would be able to use its own explicit
> transaction blocks and savepoints.

Is that really needed? What if the procedure starts in a transaction normally
but is just allowed to commit it and start another transaction? I mean it's
not like it would be allowed to do any actual work without starting a
transaction anyways.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
>> What's needed for this is to isolate the transaction-initiating code
>> from the main query-processing loop. So for CALL statements it wouldn't
>> be invoked, and the procedure would be able to use its own explicit
>> transaction blocks and savepoints.

> Is that really needed? What if the procedure starts in a transaction normally
> but is just allowed to commit it and start another transaction?

In fact it would more or less have to start in a transaction; keep in
mind that *we cannot do any database access* outside a transaction,
and therefore we could not have looked up the procedure in the system
catalogs in the first place without starting a transaction. We could
however commit that and let the procedure launch its own transactions
(compare to VACUUM, db-wide CLUSTER, etc) once we have read the
procedure body from the catalogs and done any pre-parsing we want to do.

Another point is that we are not really equipped to deal with errors
that occur outside a transaction --- the backend manages not to crash
but it's not really the way things ought to happen. So every action
that the procedure takes will need to be wrapped, explicitly or
implicitly, inside a transaction. This is fairly close to our semantics
for interactive SQL commands, so maybe we could essentially treat the
procedure as a mechanism for pushing commands into the SQL engine.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> In fact it would more or less have to start in a transaction; keep in
> mind that *we cannot do any database access* outside a transaction,
> and therefore we could not have looked up the procedure in the system
> catalogs in the first place without starting a transaction. We could
> however commit that and let the procedure launch its own transactions
> (compare to VACUUM, db-wide CLUSTER, etc) once we have read the
> procedure body from the catalogs and done any pre-parsing we want to do.

Well I guess I'm wondering whether there's any need to commit at all.
If you do commit then you wouldn't be able to do something like:

CREATE PROCEDURE terminate_transaction()
COMMIT;
END PROCEDURE

Admittedly I can't imagine why I would want to do this. But the reference
earlier about being able to declare procedures to be in the same savepoint
namespace as their caller makes me think this is what the spec has in mind.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> however commit that and let the procedure launch its own transactions
>> (compare to VACUUM, db-wide CLUSTER, etc) once we have read the
>> procedure body from the catalogs and done any pre-parsing we want to do.

> Well I guess I'm wondering whether there's any need to commit at all.
> If you do commit then you wouldn't be able to do something like:

> CREATE PROCEDURE terminate_transaction()
> COMMIT;
> END PROCEDURE

By commit I was thinking of CommitTransactionCommand, which isn't going
to exit a pre-existing transaction block, so I'm not sure that we are
disagreeing.

One interesting point is whether it's possible for one procedure to call
another, and if so what that means for the semantics. Is the inner
procedure allowed to commit a transaction started by the outer one?

regards, tom lane

Joe Conway wrote:
> Gavin Sherry wrote:
>
>> Do you have any idea about databases returning result sets from SQL
>> procedures (ie, not functions).
>>
>
> As other's have pointed out, this is very common in the MS SQL Server
> world (and I believe Sysbase also supports it). It works like:

And these databases also return a result status/value from the stored
procedure. IIRC this result is limited to an int value.

Maarten

Tom Lane wrote:

>One interesting point is whether it's possible for one procedure to call
>another, and if so what that means for the semantics. Is the inner
>procedure allowed to commit a transaction started by the outer one?
>
>
Usually yes a procedure can call another, and it's extremely useful to
do so. I'm not so sure about the semantics with transactions, but
personally I wouldn't like to see a procedure be able to commit the
transaction of it's caller.

Peter

Maarten Boekhold wrote:

>
> Joe Conway wrote:
>
>> Gavin Sherry wrote:
>>
>>> Do you have any idea about databases returning result sets from SQL
>>> procedures (ie, not functions).
>>>
>>
>> As other's have pointed out, this is very common in the MS SQL Server
>> world (and I believe Sysbase also supports it). It works like:
>
>
> And these databases also return a result status/value from the stored
> procedure. IIRC this result is limited to an int value.
>
> Maarten

Yes, MS SQL returns an int as far as I know (all the procs I use return
an int), but in theory it can be any type.

From my useage, the return parameter (if requested) is returned as the
first out parameter.

Ie, from JDBC, my CallableStateme is of the form: "? = Call dbo.MyProc(
?, ?, ? )" so I can simply use cs.getInt( 1 ); to get at that value. If
I don't ask for the return code, then I don't receive it.

Peter

On Fri, 2004-09-24 at 01:56, Joe Conway wrote:
> As other's have pointed out, this is very common in the MS SQL Server
> world (and I believe Sysbase also supports it).

>From looking at the docs, it appears this isn't supported by Oracle or
DB2 (correct me if I'm wrong). I can see how it would be useful, but I
don't think it needs to be part of the first shot at doing stored
procedures.

-Neil

On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote:
> I don't think we can do that in a standard function, at least not
> without a lot of work.

Can you elaborate on why this would be so difficult?

-Neil

On Fri, 2004-09-24 at 02:40, Tom Lane wrote:
> I concur with Grant Finnemore's objection as well: people expect
> procedures to be able to return resultsets, ie SETOF something,
> not only scalar values.

IMHO most products (and the standard) define stored procedures as not
returning _anything_, whether they be scalar values or not. The only
counter-example I've seen pointed out is MS SQL. Or are you talking
about non-scalar OUT params?

-Neil

Neil Conway wrote:
> On Fri, 2004-09-24 at 01:56, Joe Conway wrote:
>
>>As other's have pointed out, this is very common in the MS SQL Server
>>world (and I believe Sysbase also supports it).
>
>
>>From looking at the docs, it appears this isn't supported by Oracle or
> DB2 (correct me if I'm wrong). I can see how it would be useful, but I
> don't think it needs to be part of the first shot at doing stored
> procedures.

For Oracle you would return refcursors...

Peter Mount <peter(at)retep(dot)org(dot)uk> writes:

> Tom Lane wrote:
>
> >One interesting point is whether it's possible for one procedure to call
> >another, and if so what that means for the semantics. Is the inner
> >procedure allowed to commit a transaction started by the outer one?
>
> Usually yes a procedure can call another, and it's extremely useful to do so.
> I'm not so sure about the semantics with transactions, but personally I
> wouldn't like to see a procedure be able to commit the transaction of it's
> caller.

From the quote from the spec referred to earlier it seems the spec anticipates
that by default it wouldn't be able to. At least not able to commit refer to
savepoints from its caller. Presumably that extends to transactions.

However it did provide a way to declare a procedure that could refer to
savepoints from its caller. Conceivably that extends to the overall
transaction as well.

--
greg

Neil Conway <neilc(at)samurai(dot)com> writes:
> Or are you talking about non-scalar OUT params?

Exactly. I agree that a procedure has no "return value" per se,
but we need to be able to support OUT params that are rowsets.

regards, tom lane

Neil Conway wrote:
> On Fri, 2004-09-24 at 01:56, Joe Conway wrote:
>
>>As other's have pointed out, this is very common in the MS SQL Server
>>world (and I believe Sysbase also supports it).
>
>>From looking at the docs, it appears this isn't supported by Oracle or
> DB2 (correct me if I'm wrong). I can see how it would be useful, but I
> don't think it needs to be part of the first shot at doing stored
> procedures.

I believe you are correct for Oracle at least.

But for people porting over from MSSQL it is a *huge* deal, and given
the native windows port of Postgres with 8.0.0, I predict *many*
requests for this in upcoming months.

Joe

> I believe you are correct for Oracle at least.
>
> But for people porting over from MSSQL it is a *huge* deal, and given
> the native windows port of Postgres with 8.0.0, I predict *many*
> requests for this in upcoming months.
>
Speaking from a commercial perspective. I have had, in the last 60 days
over a dozen inquiries of how PostgreSQL 8.0 on WINDOWS compares to
MSSQL. The specific question
topics have been:

1. Reliability
2. Performance
3. High Availability
4. Features

Anything that we can do, within reason to help the migration from MSSQL
to PostgreSQL "is a good thing" (tm).

Sincerely,

Joshua D. Drake

> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

On Fri, Sep 24, 2004 at 10:03:33AM -0400, Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
> > Or are you talking about non-scalar OUT params?
>
> Exactly. I agree that a procedure has no "return value" per se,
> but we need to be able to support OUT params that are rowsets.

FWIW, Sybase, MSSQL, and DB2 return recordsets via an 'open' SELECT or
OPEN CURSOR statement. IE: you execute a SELECT or an OPEN CURSOR, but
don't fetch it into anything. Oracle takes the track of fetching into a
refcursor or cursor variable, which you return as an OUT or INOUT parameter.
The advantage to MSSQL et all is it's less work/code. The advantage to
Oracle is there's no ambiguity.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, 2004-09-24 at 19:28, Neil Conway wrote:
> On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote:
> > I don't think we can do that in a standard function, at least not
> > without a lot of work.
>
> Can you elaborate on why this would be so difficult?

I never got a reply to this question -- someone, anyone?

(BTW, Gavin and I are working through the suggestions for changes to the
stored procedure proposal -- we'll send a revised spec to the list next
week. Thanks for the feedback, everyone.)

-Neil

On Thu, 23 Sep 2004, Tom Lane wrote:

> > This makes the difference between procedures and functions quite
> > superficial: procedures are functions which return void and have parameter
> > modes.
>
> If you implement it that way I think it'll be very largely a waste of
> effort :-(. What you're talking about seems mere syntactic sugar and
> not a fundamental advance in capability.
>
> What I'd like to see is a "procedure" capability which is somehow
> outside the transaction system and can therefore invoke BEGIN, COMMIT,
> SAVEPOINT, etc. I have no immediate ideas about how to do this, but
> I think that's what people are really after when they ask for
> server-side procedures. They want to be able, for example, to have
> a procedure encapsulate an abort-and-retry loop around a serializable
> transaction. (It'd be great if we could do that in a function, but
> I haven't thought of a way to make it work.)

I made no reference to this in my previous email but I certainly agree
that SPs do not give people anything more than a function if they don't
have transaction management. I think your idea, mentioned else where,
about startup being in its own txn and then calling the SP outside of a
txn may work (with some modification to some areas of the code). There are
still other cases, like functions calling SPs and SPs calling SPs which
potentially provide some messy issues. Neil and I are going to try and
work out which cases exist and then see how we can adapt the code or SPs
to handle them.

>
> I concur with Grant Finnemore's objection as well: people expect
> procedures to be able to return resultsets, ie SETOF something,
> not only scalar values. Whether this is what SQL2003 says is not
> really the issue -- we have to look at what's out there in competing
> products.

Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
by the client? We have a variety of options: returning the results as if
it was a normal SELECT; returning some kind of delimited string and
providing an API to scroll it on the client side? There's got to be
something better than that :-).

Gavin

On Thu, 23 Sep 2004, Joe Conway wrote:

> Gavin Sherry wrote:
> > Do you have any idea about databases returning result sets from SQL
> > procedures (ie, not functions).
> >
>
> As other's have pointed out, this is very common in the MS SQL Server
> world (and I believe Sysbase also supports it). It works like:
>
> <begin proc def>
>
> select * from something
> ...
> select * from somethingelse
> ...
>
> <end proc def>
>
> We get requests for this kind of functionality at least a couple of
> times a month, and although it's been a few years since I mucked with
> MSSQL, I found it to be very useful in a number of different circumstances.

That's fairly bizarre (at least to my view of the world). Say we could
have OUT parameters which were of some SETOF style type I think that would
solve the same problem.

If we wanted to just return the data as if a SELECT had been issued there
might be some tricky issues for clients like psql of the row descriptor
changed (more rows, new types, etc). On the server side, though, it should
be as simple as reinitialising the destination receiver -- although I
haven't looked at it that closely yet.

Gavin

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>> I concur with Grant Finnemore's objection as well: people expect
>> procedures to be able to return resultsets, ie SETOF something,
>> not only scalar values. Whether this is what SQL2003 says is not
>> really the issue -- we have to look at what's out there in competing
>> products.

> Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
> by the client? We have a variety of options: returning the results as if
> it was a normal SELECT; returning some kind of delimited string and
> providing an API to scroll it on the client side? There's got to be
> something better than that :-).

For the case of a single OUT SETOF parameter, acting as though the CALL
were a SELECT would work nicely. The hard part is what to do if there
are multiple such parameters. We could possibly return them as
successive SELECT results but this would break a whole lot of pretty
fundamental things at both the protocol and client-library-API level.
(The old protocol actually could handle it, but the V3 protocol is
going to have problems.)

How do you feel about restricting SPs to have at most one SETOF result?

Plan B would be to implement each SETOF result as if it were a cursor.
Say, the system would pass back a cursor (portal) name in the same
way as a scalar OUT result would be returned, and the client would need
to do FETCH operations to pull the actual rows. I'm not sure what to
say about the lifespan of such cursors --- ordinary cursors go away at
transaction end, but if an SP is invoked outside of the transaction
system then this isn't going to do for SP results.

regards, tom lane

Gavin Sherry wrote:
> That's fairly bizarre (at least to my view of the world). Say we could
> have OUT parameters which were of some SETOF style type I think that would
> solve the same problem.

That won't satify people moving over from MSSQL/Sybase, but then again,
maybe the community at-large doesn't think it is important to satify
that group of users.

I think this part of the thread actually ties in with the discussion
regarding beginning/committing transactions within stored procedures.
Think of a stored procedure as a parameterized sql script that is run
from within a single statement, rather than as a series of statements
piped in from a file. In such a file, you might do

begin;
INSERT ...;
UPDATE ...;
commit;
SELECT ...;
CREATE TEMP TABLE foo AS SELECT ...
UPDATE ...;
SELECT ...;

in order to perform a series of actions while being able to see interim
results. In MSSQL, a stored procedure can be (and very often is) used to
do something exactly like the above (perhaps related to loading of a
data warehouse, or in an interface between two business systems). In
fact, T-SQL (the MSSQL/Sybase SQL variant) also supports simple
branching, variable assignment, and conditionals, which makes it
possible to do some fairly complex processing in stored procs. This is
the direction I always hoped Postgres would go with stored procedures.

Joe

On Fri, Sep 24, 2004 at 07:28:09PM +1000, Neil Conway wrote:
> On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote:
> > I don't think we can do that in a standard function, at least not
> > without a lot of work.
>
> Can you elaborate on why this would be so difficult?

Because you have to keep the function state somewhere. Currently, all
functions are handled in the SPI stack, which is dependent on the
transaction machinery. So you'd have to move the function state
somewhere else -- maybe keeping a special SPI stack outside transaction
management. Or maybe the procedure handler does not use SPI at all.

In any case, you need some way to handle cleaning it up if the procedure
happens to fail; it needs to be able to cope with failing transactions
that have to be handled (because some operations in the procedure can be
handled), and some others that have to abort the procedure as a whole.
This sounds like a meta-transaction (transaction of transactions).
Apparently you also need some way to use "savepoints," but since you are
not in a normal transaction you can't use the current mechanism for
those. (Not sure if you really need cross-transaction "savepoints").

_And_ you need to handle all this from the procedure handler. The
current SPI exposes a limited subset of transaction handling to function
handlers; you'd need to extend that (unless you are planning to have
non-pluggable procedure handlers).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

On Sat, 2 Oct 2004, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> >> I concur with Grant Finnemore's objection as well: people expect
> >> procedures to be able to return resultsets, ie SETOF something,
> >> not only scalar values. Whether this is what SQL2003 says is not
> >> really the issue -- we have to look at what's out there in competing
> >> products.
>
> > Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
> > by the client? We have a variety of options: returning the results as if
> > it was a normal SELECT; returning some kind of delimited string and
> > providing an API to scroll it on the client side? There's got to be
> > something better than that :-).
>
> For the case of a single OUT SETOF parameter, acting as though the CALL
> were a SELECT would work nicely. The hard part is what to do if there
> are multiple such parameters. We could possibly return them as
> successive SELECT results but this would break a whole lot of pretty
> fundamental things at both the protocol and client-library-API level.
> (The old protocol actually could handle it, but the V3 protocol is
> going to have problems.)

Yes, that's what I suspected.

>
> How do you feel about restricting SPs to have at most one SETOF result?

I think its a restriction we could do without and others seem to be
suggesting that we at least need an analogous feature so that people can
have SPs return multiple result sets.

>
> Plan B would be to implement each SETOF result as if it were a cursor.
> Say, the system would pass back a cursor (portal) name in the same
> way as a scalar OUT result would be returned, and the client would need
> to do FETCH operations to pull the actual rows. I'm not sure what to
> say about the lifespan of such cursors --- ordinary cursors go away at
> transaction end, but if an SP is invoked outside of the transaction
> system then this isn't going to do for SP results.

Why not go the whole way and just have a cursor type for these kind of
parameters? I'd imagine that this would also allow users an opaque result
set. That is, the columns of the result set could be determined at run
time.

Gavin

On Sat, 2 Oct 2004, Joe Conway wrote:

> Gavin Sherry wrote:
> > That's fairly bizarre (at least to my view of the world). Say we could
> > have OUT parameters which were of some SETOF style type I think that would
> > solve the same problem.
>
> That won't satify people moving over from MSSQL/Sybase, but then again,
> maybe the community at-large doesn't think it is important to satify
> that group of users.

As Tom said in another email, if we want to support a single query
generating multiple result sets, we're going to have to break a few
things. I'd imagine that if we supported something like a cursor type for
OUT parameters, the ODBC driver could be modified to apparently return
multiple result sets by scrolling through the cursors.

>
> I think this part of the thread actually ties in with the discussion
> regarding beginning/committing transactions within stored procedures.
> Think of a stored procedure as a parameterized sql script that is run
> from within a single statement, rather than as a series of statements
> piped in from a file. In such a file, you might do
>
> begin;
> INSERT ...;
> UPDATE ...;
> commit;
> SELECT ...;
> CREATE TEMP TABLE foo AS SELECT ...
> UPDATE ...;
> SELECT ...;
>
> in order to perform a series of actions while being able to see interim
> results. In MSSQL, a stored procedure can be (and very often is) used to
> do something exactly like the above (perhaps related to loading of a
> data warehouse, or in an interface between two business systems). In
> fact, T-SQL (the MSSQL/Sybase SQL variant) also supports simple
> branching, variable assignment, and conditionals, which makes it
> possible to do some fairly complex processing in stored procs. This is
> the direction I always hoped Postgres would go with stored procedures.

I see the same use for SPs in Postgres. The basic language features,
however, would be supported through the use of PL/PgSQL (and other
languages). Did you have something else in mind?

>
> Joe
>

Gavin

While we're discussing things that will possibly mean a different system
than the current function language, I have another request: Can we have
a means of defining procedures/functions that doesn't involve using
quotes? Having to double-quote everything is extremely annoying and
prone to errors. I realize that even if procedures/functions aren't
defined using quotes there will still be cases where things need to be
multi-quoted, but those cases are much rarer.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Mon, Oct 04, 2004 at 01:55:21PM -0500, Jim C. Nasby wrote:
> While we're discussing things that will possibly mean a different system
> than the current function language, I have another request: Can we have
> a means of defining procedures/functions that doesn't involve using
> quotes? Having to double-quote everything is extremely annoying and
> prone to errors. I realize that even if procedures/functions aren't
> defined using quotes there will still be cases where things need to be
> multi-quoted, but those cases are much rarer.

So you aren't aware of the dollar-quoting feature? You may want to take
a look at that ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Limítate a mirar... y algun día veras"

Jim C. Nasby wrote:

>While we're discussing things that will possibly mean a different system
>than the current function language, I have another request: Can we have
>a means of defining procedures/functions that doesn't involve using
>quotes? Having to double-quote everything is extremely annoying and
>prone to errors. I realize that even if procedures/functions aren't
>defined using quotes there will still be cases where things need to be
>multi-quoted, but those cases are much rarer.
>
>

Have you played with dollar quoting yet? That's in 8.0 for precisely
this reason ...

The problem with moving entirely from strings would be that we support
many languages. If all we had was plpgsql it would be a no-brainer, ISTM.

cheers

andrew

On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote:
> So you aren't aware of the dollar-quoting feature? You may want to take
> a look at that ...

Can someone point me to a url? I haven't been able to find anything
about this...
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Wed, Oct 06, 2004 at 01:34:00AM -0500, Jim C. Nasby wrote:
> On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote:
> > So you aren't aware of the dollar-quoting feature? You may want to take
> > a look at that ...
>
> Can someone point me to a url? I haven't been able to find anything
> about this...

http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-CONSTANTS

Keep in mind that this is 8.0 only ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Wed, Oct 06, 2004 at 01:34:00AM -0500, Jim C. Nasby wrote:
>> On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote:
>>> So you aren't aware of the dollar-quoting feature? You may want to take
>>> a look at that ...
>>
>> Can someone point me to a url? I haven't been able to find anything
>> about this...

> http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-CONSTANTS

Also, many of the function examples in Section V,
http://developer.postgresql.org/docs/postgres/server-programming.html
have been updated to use dollar-quote style.

regards, tom lane