On Fri, Apr 22, 2011 at 11:46 PM, David Christensen <da...@endpoint.com> wrote: > > On Apr 22, 2011, at 3:50 PM, Tom Lane wrote: > >> Merlin Moncure <mmonc...@gmail.com> writes: >>> On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut <pete...@gmx.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
