Re: BLOB / CLOB support in PostgreSQL

On 9/29/20 10:26 AM, Peter Eisentraut wrote:
> 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.
>
>

So if I read this correctly what I have proposed is completely kosher
according to the spec - it's the "fully materialized on the client"
variant, just like the MySQL and MSSQL drivers.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services