On Wed, Aug 31, 2011 at 9:00 AM, Thom Brown <t...@linux.com> wrote: > On 9 May 2011 20:52, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian <br...@momjian.us> wrote: >>> Josh Berkus wrote: >>>> Peter, >>>> >>>> > I would like to collect some specs on this feature. So does anyone have >>>> > links to documentation of existing implementations, or their own spec >>>> > writeup? A lot of people appear to have a very clear idea of this >>>> > concept in their own head, so let's start collecting those. >>>> >>>> Delta between SPs and Functions for PostgreSQL: >>>> >>>> * SPs are executed using CALL or EXECUTE, and not SELECT. >>>> >>>> * SPs do not return a value >>>> ** optional: SPs *may* have OUT parameters. >>> >>> [ Late reply.] >>> >>> What is it about stored procedures that would require it not to return a >>> value or use CALL? I am trying to understand what part of this is >>> "procedures" (doesn't return a values, we decided there isn't much value >>> for that syntax vs. functions), and anonymous transactions. >> >> FWICT the sql standard. The only summary of standard behaviors I can >> find outside of the standard itself is here: >> http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html. >> Peter's synopsis of how the standard works is murky at best and >> competing implementations are all over the place...SQL server's >> 'CALL' feature is basically what I personally would like to see. It >> would complement our functions nicely. >> >> Procedures return values and are invoked with CALL. Functions return >> values and are in-query callable. >> >> The fact that 'CALL' is not allowed inside a query seems to make it >> pretty darn convenient to make the additional distinction of allowing >> transactional control statements there and not in functions. You >> don't *have* to allow transactional control statements and could offer >> this feature as an essentially syntax sugar enhancement, but then run >> the risk of boxing yourself out of a useful properties of this feature >> later on because of backwards compatibility issues (in particular, the >> assumption that your are in a running transaction in the procedure >> body). > > I've seen no mention of SQL/PSM. Isn't all of this covered by that?
That's the 64k$ question. My take is that 'CALL' doesn't implicitly set up a transaction state, and a proper PSM implementation would allow transaction control mid-procedure. Functions will always be called in-transaction, since there is no way I can see to execute a function except from an outer query (or the special case of DO). I think there's zero point in making CALL work without dealing with the transaction issue -- in fact it could end up being a huge mistake to do so. Pavel's PSM implementation (see: http://www.pgsql.cz/index.php/SQL/PSM_Manual) works under the constraints of pg's understanding of what functions should and should not be allowed to do. It allows creation of PSM *functions* -- that's all. IMNSHO, stored procedures should run in-process, and the execution engine needs to be modified to not automatically spin up a transaction and a snapshot when running them, but most allow a pl to do that at appropriate times. plpgsql and the other pls fwict make no assumptions that strictly invalidate their use in that fashion outside of some unfortunate ambiguity issues around 'begin', 'end', etc. If there is no current transaction, each statement should create one if it's determined that the statement is interfacing with the sql engine in such a way a transaction would be required, and immediately tear it down, exactly as if an sql script was run inside the backend. The SPI interface can probably work 'as-is', and should probably return an error if you arrive into certain functions while not in transaction. An out of process, autonomous transaction type implementation should probably not sit under stored procedures for a number of reasons -- mainly that it's going to expose too many implementation details to the user. For example, does a SP heavy app have 2*N running processes? Or do we slot them into a defined number of backends for that purpose? Yuck & yuck. I like the AT feature, and kludge it frequently via dblink, but it's a solution for a different set of problems. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
