0

I have been reading BLOBs (type lo in my PostgreSQL database) in my entities so far with no trouble:

@Entity
@Getter @Setter
public class MyEntity {
  @Id
  private Long id;

  @Lob
  private Blob file;
}

And the following override in the dialect:

@Override
public boolean equivalentTypes(int type1, int type2) {
    // The 'lo' type is an alias for 'oid', which is typed as DISTINCT
    return super.equivalentTypes(type1, type2) ||
            (type1 == Types.BLOB && type2 == Types.DISTINCT) ||
            (type1 == Types.DISTINCT && type2 == Types.BLOB);
}

Now I want to start creating new ones, but I need to account for the large object ACLs. After any new BLOB is crated, I need to execute a query such as:

GRANT SELECT ON LARGE OBJECT 12345 TO reader_role;

The obvious way I can think to do it is with a post-save hook on every entity that has a @Lob field, but how can you execute SQL in the same transaction using that?

Is there a better way to do it at the type-level? An existing hook or an interface to override? I suspect Hibernate (and JPA in general) has no concept of blobs existing independently of an entity, because that only happens AFAIK in PostgreSQL.

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
  • Why the large objects to begin with? Do you need the "streaming" capabilities" or the fact that they can be larger than 1GB? If not, using `bytea` is typically a better choice. –  Mar 09 '22 at 14:55
  • @a_horse_with_no_name both. And after several years it's a bit late to convert the 1.2TB of large objects into `bytea` fields. – OrangeDog Mar 09 '22 at 15:00
  • Have you tried the postPersist and postUpdate JPA lifecycle methods? These events are for the entity, not the blob, but allow you to execute what you might need after the entity insert/update statements have executed while still in the same transactional context. – Chris Mar 09 '22 at 19:59
  • @Chris I mentioned that in the question. How do you execute SQL in that? Only the entity is available. – OrangeDog Mar 10 '22 at 09:06
  • 1
    the spec states: "Lifecycle callbacks can invoke JNDI, JDBC, JMS, and enterprise beans.", and you can define listener classes that should support injection in containers, or can use the local context to find the resources currently in use. The spec does state "In general, the lifecycle method of a portable application should not invoke EntityManager or Query operations, access other entity instances, or modify relationships within the same persistence context." but doesn't disallow it. I've seen this in other providers, but can't find a working example to share. – Chris Mar 10 '22 at 16:40

0 Answers0