Re: BLOB / CLOB support in PostgreSQL

On 9/29/20 2:39 PM, Dave Cramer wrote:
>
>
> On Tue, 29 Sep 2020 at 14:33, Andrew Dunstan
> <andrew(dot)dunstan(at)2ndquadrant(dot)com
> <mailto: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'
>
>

I take your point, but my remark was more in response to the apparent
suggestion that what I submitted was not according to spec.

There are two Clob methods I didn't implement, and one Blob method - the
set*Stream methods, I think they should be implementable, but they will
make the implementation somewhat more complex.

Anyway, at this stage let's take the discussion back to the github forums.

cheers

andrew

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