Re: lazy detoasting

On 04/11/18 11:27, Chapman Flack wrote:
> In most cases I can easily imagine, a function that gets an SQLXML
> object is going to read it "pretty soon" ...
> 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, ...
> 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.

In the interest of closure, how this idea looks implemented in PL/Java
is here:

https://github.com/tada/pljava/commit/1a5caf1

It uses GetOldestSnapshot() to choose the snapshot to retain, and it
passes the following test, inspired by Andrew Gierth's in this thread.
(It also fails the test if the snapshot retention isn't done, confirming
that it's needed.)

CREATE TABLE t(x xml);
BEGIN READ WRITE, ISOLATION LEVEL READ COMMITTED;
/*
* In other session: INSERT INTO t(x)
* SELECT table_to_xml('pg_operator', true, false, '');
*/
SELECT javatest.echoxmlparameter(x, 0, 5) FROM t; -- 0 => stash x
/*
* In other session: DELETE FROM t;
* VACUUM t;
*/
SELECT javatest.echoxmlparameter(null, 5, 5); -- null => unstash
COMMIT;

GetOldestSnapshot() appeared in 9.6. For older PG releases, instead
of a snapshot and toast pointer being retained to detoast lazily,
the ondisk content is eagerly fetched, then decompressed lazily.
XML compresses well, so that can still use 95% less memory while one
of these objects is being held but not read from. (The 9.5-and-earlier
support is added in a separate commit.)

-Chap