Re: "stored procedures"

On Fri, Apr 22, 2011 at 11:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.)

Just to be clear (I'm really trying not to be obtuse here), does that
mean you can't touch that infrastructure at all in a procedure in this
vein, or can you set up a transaction temporarily in cases you need it
(maybe at the statement level)? If you are well and truly locked out
of the sql engine with no doorway in, then i'd have to agree, plpgsql
is out.

merlin