Re: BLOB / CLOB support in PostgreSQL

On 2020-09-28 15:46, Vladimir Sitnikov wrote:
> The concerns to avoid "Clob maps to text" could be:
> a) Once the behavior is implemented, it is hard to change. That is
> applications would rely on it (and it becomes a defacto standard), and
> it would be hard to move to the proper "text with streaming API" datatype.
> b) If we make «clob is text», then people might start using
> update/substring APIs (which is the primary motivation for Clob) without
> realizing there’s full value update behind the scenes. Currently, they
> can use setString/getString for text, and it is crystal clear that the
> text is updated fully on every update.

When we added TOAST, we made the explicit decision to not add a "LONG"
type but instead have the toasting mechanism transparent in all
variable-length types. And that turned out to be a very successful
decision, because it allows this system to be used by all data types,
not only one or two hardcoded ones. Therefore, I'm very strongly of the
opinion that if a streaming system of the sort you allude to were added,
it would also be added transparently into the TOAST system.

The JDBC spec says

"""
An implementation of a Blob, Clob or NClob object may either be locator
based or result in the object being fully materialized on the client.

By default, a JDBC driver should implement the Blob, Clob and NClob
interfaces using the appropriate locator type. An application does not
deal directly with the locator types that are defined in SQL.
"""

(A "locator" in SQL is basically what you might call a streaming handle.)

So yes, this encourages the implementation of locators. But it also
specifies that if you don't have locators, you can implement this using
non-large-object types.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services