Re: "stored procedures"

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Apr 22, 2011, at 11:10 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'd like a pony, too. Let's be perfectly clear about this: there is no
>> part of plpgsql that can run outside a transaction today, and probably
>> no part of the other PLs either, and changing that "without major
>> changes" is wishful thinking of the first order.

> Correct me if I am wrong here, but the basic issue is, I think, that an error might occur. And transactions are how we make sure that when control returns to the top level, we've released any heavyweight locks, lightweight locks, buffer pins, backend-local memory allocations, etc. that we were holding when the error occurred.

Well, yes, all that infrastructure is tied to transactions. Now if you
don't use any of it, then you don't have a problem. The real difficulty
is that plpgsql uses SQL expressions freely and there's no guarantees
about what parts of the infrastructure a random function, operator, or
datatype I/O function might use. (Examples: domain_in can invoke pretty
much arbitrary code as a consequence of domain CHECK constraints, and
will certainly do catalog accesses even without those. Almost any array
operator will do catalog accesses to get the properties of the array
element type. And so on.)

You could possibly lobotomize plpgsql down to a small number of
datatypes and operators that are known not to ever do anything more
interesting than palloc() and elog(), but IMO the usefulness would be
low and the fragility high. It'd be better to give the task to an
interpreter that was never built to depend on a SQL environment in the
first place. Thus my thought about perl etc.

regards, tom lane