Re: "stored procedures"

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? In the non-explicit transaction case, locking wouldn't work to keep these objects around due to the transaction scope of locks (unless locks are part of the transaction state carried forward across the implicit transactions). If so, could that be done in such a way that it would take precedence over a parallel backend attempting to acquire the same locks without blocking the procedure?

Regards,

David
--
David Christensen
End Point Corporation
david(at)endpoint(dot)com