Postgresql text columns are created as "text" but are declared as @Lob

[nithril]

Description:

In the flyway sql script, the postgresql text columns are created as "text", for example for stream_definitions:

create table stream_definitions (
  definition_name varchar(255) not null,
  definition text,
  primary key (definition_name)
);

The JPA entity is declaring the column as a @Lob

	@Column(name = "DEFINITION")
	@Lob
	private String dslText;

By consequence Hibernate/JDBC driver are managing the definition as a large object stored into a specific table and are storing the large object id into the column definition that is of type text. It creates a soft reference that does not survive to the garbage collector of large objects (vacuumlo).

2 possibles fixes:

• Either annotate the field with @Type(type = "org.hibernate.type.TextType")
• Create the column with the type oid

That issue is in relation with #3786

Please let me know if I can help.

Release versions: 2.7.1

Steps to reproduce:

• Create a stream
• Select on the stream_definition table, the column contains an oid
• Execute the vacuumlo.
• The stream definition is not anymore available

[sabbyanandan]

Hi, @nithril. Thanks for the thorough write-up, nicely done! When you get a chance, please propose your best suggestion as PR, and @jvalkeal can collaborate with you.