Postgres large object support

[blafond]

Hibernate Reactive's LobTypeTest fails using PostgreSQL by returning a NULL for a CLOB string or byte[] column type.

We're currently using vertx-pg-client 4.4.2 & hibernate-orm 6.2.4.Final

• Documentation for 4.4.x vertx PG client does not include CLOB/BLOB support but PG 15.2 uses oid type.

Are you planning on implementing this support and converting PG's oid type? or is it a bug?

[vietj]

is that a protocol change ?

[vietj]

any pointer about this 15.2 specific behavior ?

[blafond]

From what I understand, large object types are handled differently and they've moved to using a large object ID to the stored object rather than directly in the table info.

With the upgrades, the test's book value for PG in the table is an id like 5436543.

So retrieving the actual string value would require a join, I guess.

[DavideD]

@vietj, if it helps, this is the SQL we are running:

create table LobEntity (id integer not null, version integer, string varchar(255), book oid, pic oid, primary key (id))
insert into LobEntity (book,pic,string,version,id) values ($1,$2,$3,$4,$5)
select b1_0.id,b1_0.book,b1_0.pic,b1_0.string,b1_0.version from LobEntity b1_0 where b1_0.id=$1

The insert seems to work fine. The content of the table is the following after an insert:

 id | version | string |    book    |    pic     
----+---------+--------+------------+------------
  1 |       0 |        | 4294967295 |           
  2 |       0 |        |            | 4294967295

But the result of the select is always null for the columns book and pic

[kdubb]

@vietj To answer your question about the protocol... Unless I'm missing something obvious, I don't know of any (user visible) changes to TOAST or "Large Objects".

@blafond I may be stating things already known to you, but all known versions of PostgreSQL have used oid for "Large Object" references. BLOB and CLOB do not exist as types in PostgreSQL, you need to use lo_create, etc. to create and manage large objects. These functions work on an oid for pg_largeobject.

In the driver pgjdbc-ng, due to it being a JDBC 4.2/4.3 implementation, we had to work around this lack of standard support. We did it by allowing the user to add a type to their catalog that was a alias for oid but denoted specifically it was an oid for pg_largeobject. By default the driver was configured to recognize loid but the user could change this to any type name/id. Whenever it sees loid it takes special action to provide JDBC BLOB and CLOB support (even then it was problematic because you need to use the lo_* functions within the same transaction).

I'm not exactly sure how much if the above information helps the current issue but I thought I'd lay it out to see if it helps in crafting a solution.

[tsegismont]

The insert seems to work fine. The content of the table is the following after an insert:

@DavideD @blafond I ran the LobTypeTest in Hibernate Reactive for PG and here are my observations:

• Hibernate executes a prepared query, providing a Tuple that contains a ClobProxy for the oid column
• Pg Client, after preparing the statement, notices that the column type is oid and then inserts null because the type is unsupported

The pg client doesn't support creating/reading/deleting large objects. As a workaround, users should map text or binary content to text or bytea columns.