Re: BLOB / CLOB support in PostgreSQL

On 9/28/20 9:46 AM, Vladimir Sitnikov wrote:
> Let me please bump the thread.
>
> Just in case, I'm PgJDBC committer.
>
> PgJDBC receives requests to "support CLOB" from time to time, however,
> I believe it is impossible without the support from the database.
> To my best knowledge, the database does not have APIs for "streaming
> large text data".
> The only "streaming large binary data" API I know is LargeObject which
> seems to be old-fashioned.
>
> I believe Java is not the only client that wants streaming access for
> binary and text data.
>
> Here's a recent pull request to PgJDBC
> https://github.com/pgjdbc/pgjdbc/pull/1892 where Andrew suggests to
> add BLOB/CLOB support via bytea/text,
> and apparently, Andrew is surprised that the database lacks BLOB/CLOB
> support.

I didn't at all say I was surprised. I said I didn't recall seeing  a
call for what you're talking about, and indeed there has been very little.

>
> Any ideas on the way to proceed here?
> I don't think it is right to implement Clob via text, especially in
> case the database provides its own "large text with streaming"
> datatype in the future.
>
> 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.
>
>

I think your concerns are seriously misplaced. In the first place, what
I have proposed is an optional behaviour, and it does not at all
preclude other behaviour being made available in future, as well as the
current behaviour which would remain the default. Furthermore, it's
consistent with how the MySQL driver behaves, AIUI, and possibly others
as well. That consistency is a major motivator for the work I've done.
Do you suggest they should rip out their Clob interface until MySQL
supports a streaming data type?

But this is hardly the place for such arguments.

To the best of my knowledge there is no concrete proposal for the type
of data type / interface you suggest. I don't even have any very good
idea what such a thing would look like.

cheers

andrew

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