Skip site navigation (1) Skip section navigation (2)

Re: "stored procedures"

From: Darren Duncan <darren(at)darrenduncan(dot)net>
To: pgsql-hackers(at)postgresql(dot)org, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Re: "stored procedures"
Date: 2011-04-21 21:48:02
Message-ID: 4DB0A612.8010108@darrenduncan.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Peter Eisentraut wrote:
> So the topic of "real" "stored procedures" came up again.  Meaning a
> function-like object that executes outside of a regular transaction,
> with the ability to start and stop SQL transactions itself.
> 
> 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.

I've thought a lot about this too.

The general case of a stored procedure should be all powerful, and be able to 
directly invoke any code written in SQL or other languages that a DBMS client 
can directly invoke on the DBMS, as if it were a client, but that the procedure 
is stored and executed entirely in the DBMS.  But the stored procedure also has 
its own lexical variables and supports conditionals and iteration and recursion.

A stored procedure is invoked as a statement and doesn't have a "return" value; 
in contrast, a function has a return value and is invoked within a value 
expression of a statement.  A stored procedure can see and update the database, 
and can have IN/INOUT/OUT parameters.  A stored procedure can have side-effects 
out of band, such as user I/O, if Pg supports that.

The general stored procedure should be orthogonal to other concerns, in 
particular to transactions and savepoints; executing one should not should not 
implicitly start or commit or rollback a transaction or savepoint.  However, it 
should be possible to explicitly declare that procedure is a transaction, so 
that starts and ends are neatly paired regardless of how the procedure exits, 
that is a transaction lifetime is attached to its lexical scope, but this would 
be optional.

A stored procedure should be able to do data manipulation, data definition, 
explicit transaction control (except perhaps when defined to be a transaction), 
privilege control, message passing, and so on.

As for semantics, lets say that when a stored procedure is invoked, its 
definition will be pulled from the system catalog in a snapshot and be compiled, 
then run normally no matter what it does, even if the definition of the 
procedure itself is changed during its execution; in the latter case, it just 
means that once the execution finishes, subsequent calls to it would then call 
the updated version or fail.  So just compiling the procedure may need a catalog 
lock or whatever, but when it starts executing a transaction isn't required.

Any stored procedure in general should be able to invoke stored procedures, to 
any level of nesting, just like in any normal programming language.  There might 
be restrictions on what individual procedures can do depending on how they're 
declared; for example, if one is declared to have a scope-bound transaction, 
then it or ones it invokes can't have explicit transaction control statements. 
But such restrictions are an orthogonal or case-dependent matter.

(When we have a distinct stored procedure, I also believe that a stored function 
should be more restricted, such as only having IN parameters and not being able 
to see the database but by way of parameters, and that it should be 
deterministic.  But that ship has sailed and I'm not going to argue for any 
changes to functions.)

-- Darren Duncan

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2011-04-21 21:49:47
Subject: Re: EOL for 8.2 (was Re: Formatting Curmudgeons WAS: MMAP Buffers)
Previous:From: Andrew DunstanDate: 2011-04-21 21:31:52
Subject: Re: EOL for 8.2 (was Re: Formatting Curmudgeons WAS: MMAP Buffers)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group