On 04/11/2018 10:41 AM, Tom Lane wrote: > The core of the problem now is that in a READ COMMITTED transaction, we > may not be holding any snapshot at all between statements. So if you're > hanging onto a toast pointer across statements you're at risk. > > On the other hand, it's also arguable that you shouldn't be interested > in dereferencing such a pointer later than the statement in which it > was read, precisely because it no longer represents accessible data. > So maybe we need to take two steps back and talk about the semantics > of what you're doing.
The mission is to implement java.sql.SQLXML, which has long been missing from PL/Java. https://docs.oracle.com/javase/6/docs/api/index.html?java/sql/SQLXML.html The API spec includes this: "An SQLXML object is valid for the duration of the transaction in which it was created." This is the class of object your Java code can retrieve from a ResultSet row from a query with an XML column type. (It's also what can be passed to you as a function parameter, if your function's SQL declaration has a parameter type XML.) An SQLXML object represents the XML value. You get to read the object (exactly once, or not at all) for the content it represents, in your choice of a half-dozen different ways corresponding to available Java XML-processing APIs. (The SQLXML implementation knows what the underlying stored form is, so it encapsulates the knowledge of how most efficiently to get it from there to the form the program wants to work with.) In most cases I can easily imagine, a function that gets an SQLXML object is going to read it "pretty soon" ... surely some time during the function call, if it was passed as a parameter, and probably within the same row loop iteration, for one retrieved from a query. However, the spec does explicitly provide that you could, for whatever reason, sit on the thing for a while, then read it later in the same transaction. You should get the same stuff you would have if you had read it right away, whether or not stuff has changed in the database since. Eager detoasting at the time of creating the object, into a transaction-scoped memory context, would trivially have that behavior, but on the chance that XML values might be large, and a function might conceivably never read the thing at all on certain code paths, I'd rather defer detoasting until the code holding the SQLXML object actually wants to read it. -Chap