Re: BLOB / CLOB support in PostgreSQL

On 9/28/20 4:44 PM, Vladimir Sitnikov wrote:
> Andrew>To the best of my knowledge there is no concrete proposal for
> the type
> Andrew>of data type / interface you suggest. 
>
> To the best of my knowledge, one of the concrete proposals was in the very
> first message.
>
> Let me please copy it:
>
> Vladimir> **Here goes the question**:  do you think such an implementation
> Vladimir> ("large string stored in Large Objects" could be merged into
> the core eventually)?

You and I clearly have a different idea from what constitutes a concrete
proposal. This is hardly the ghost of a proposal.

>
> ---
>
> Andrew>In the first place, what
> Andrew>I have proposed is an optional behaviour
>
> Adding optional behaviors makes support complicated, so flags should
> be avoided when
> a standard solution is possible.
>
> Even if you name the behavior "optional", people would still rely on it.
> For instance, you implement Clob.truncate(int) as in-driver-memory
> truncate operation, however,
> in practice, that API should truncate the value in the database.
>
> Just in case: the current behavior for LO-based lobs is that
> Blob.truncate(int) immediately truncates LO.
> So if the application works with the current LO mode, then they would
> get a behavior change if they flip the switch.
>
> The same for setString and other modifications.
>
> An escape hatch could be like "ok, let's throw unimplemented for clob
> modifications", however, it contradicts the spec
> which says:
>
> JDBC Spec> * All methods on the <code>Clob</code> interface must be
> fully implemented if the
> JDBC Spec> * JDBC driver supports the data type.
>
> What I say here is that the behavior you suggest spans far away from
> just "blob maps to bytea".

Again, the truncate() I implemented is 100% compatible with the MySQL
driver. I just checked the MSSQL driver and it too just appears to
truncate what's in memory. So maybe we wouldn't be in such bad company.

>
> ----
>
> Andrew>Furthermore, it's
> Andrew>consistent with how the MySQL driver behaves, AIUI,
> Andrew>and possibly others as well
>
> Oracle DB behaves differently. They have BLOB/CLOB column types, and
> Clob/Blob interfaces
> map to CLOB/BLOB.
>
> Andrew>That consistency is a major motivator for the work I've done.
>
> PostgreSQL supports large binary via LargeObject API, so the driver
> maps Blob to LO.
>
> If people want to use bytea, they can use `setBinaryStream(...)`.
> What does drive people to use Clob/Blob when the database lacks APIs
> for it?
>
>

The reason for my proposal (as I stated more than once) is that people
want to be able to use the same code across databases.

cheers

andrew.

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