Re: "stored procedures"

On Fri, Apr 22, 2011 at 11:46 PM, David Christensen <david(at)endpoint(dot)com> wrote:
>
> On Apr 22, 2011, at 3:50 PM, Tom Lane wrote:
>
>> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>>> On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>>> It would probably be more reasonable and feasible to have a setup where
>>>> you can end a transaction in plpgsql but a new one would start right
>>>> away.
>>
>>> ya, that's an idea.
>>
>> Yeah, that's a good thought.  Then we'd have a very well-defined
>> collection of state that had to be preserved through such an operation,
>> ie, the variable values and control state of the SP.  It also gets rid
>> of the feeling that you ought not be in a transaction when you enter
>> the SP.
>>
>> There's still the problem of whether you can invoke operations such as
>> VACUUM from such an SP.  I think we'd want to insist that they terminate
>> the current xact, which is perhaps not too cool.
>
>
> Dumb question, but wouldn't this kind of approach open up a window where (say) datatypes, operators, catalogs, etc, could disappear/change out from under you, being that you're now in a different transaction/snapshot; presuming there is a concurrent transaction from a different backend modifying the objects in question?

That's a good question. This is already a problem for functions -- an
object you are dependent upon in the function body can disappear at
any time. If you grabbed the lock first you're ok, but otherwise
you're not and the caller will receive an error. Starting with 8.3
there is plan cache machinery that invalidates plans used inside
plpgsql which should prevent the worst problems. If you're cavalier
about deleting objects that are used in a lot of functions you can get
really burned from a performance standpoint, but that's no different
than dealing with functions today.

Procedures unlike functions however can no longer rely that catalogs
remain static visibility wise through execution for functions.
pl_comp.c is full of catalog lookups and that means that some
assumptions that are made during compilation that are no longer valid
for procedures. A missing table isn't such a big deal, but maybe it's
possible to make intermediate changes while a procedure is execution
that can cause an expression to parse differently, or not at all (for
example, replacing a scalar function with setof)? This could be a
minefield of problems or possibly not -- I really just don't know all
the details and perhaps some experimentation is in order.

One thing that's tempting is to force recompilation upon certain
things happening so you can catch this stuff proactively, but plpgsql
function compilation is very slow and this approach is probably very
complex. Ideally we can just bail from the procedure if external
events cause things to go awry.

merlin