Re: BLOB / CLOB support in PostgreSQL

On Tue, 29 Sep 2020 at 14:33, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>
wrote:

>
> 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.
>
>
I haven't really looked at MySQL or MSSQL but do they implement the full
CLOB API ?
We would need to implement the full API.

BTW, just because it adheres to the spec doesn't seem to hold water in the
PostgreSQL project. Just sayin'

Dave