v4 protocol TODO item - Lazy fetch/stream of TOASTed values?

Hi all

Per the protocol todo:

https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes

do you think it's reasonable to allow for delayed sending of big varlena
values and arrays in the protocol?

The JDBC spec already has support for this in arrays, XML, and binary
blobs, but AFAIK the backend can't support lazy fetching.

Of course anbything like this would require an open transaction, but the
JDBC spec already sets that as a requirement for SQLXML etc, specifying
that resources must be retained for at least the duration of the
transaction unless explicitly free'd by the application.

As far as I can tell PostgreSQL is in a good position to support lazy
fetch of big values. TOASTed values could be returned as an opaque
placeholder value identifying the toast table oid and ctid, then fetched
by sending a further protocol-level request to the server that could
send values as a result set or by entering COPY mode to stream them.

The transaction's xmin would already prevent the values being
overwritten - at least on SERIALIZABLE isolation.

For non-TOASTed values like big in-memory values it probably makes more
sense to just force them to be sent direct to the client like they
already are, rather than trying to stash them in server memory and
address them for returning later. Though a tuplestore could always be
used to spill to disk, I guess.

Anyway. Random idea for the day.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services